SQL-тренажёр для аналитиков

WITH ranked AS (
  SELECT category, product_id,
    SUM(quantity) AS total,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY SUM(quantity) DESC
    ) AS rn
  FROM order_items
  JOIN products USING (product_id)
  GROUP BY category, product_id
)
SELECT * FROM ranked WHERE rn <= 3;

ROW_NUMBER с PARTITION BY — одна из самых частых конструкций на собеседованиях.

WITH first_visit AS (
  SELECT user_id,
    MIN(activity_date) AS first_date
  FROM user_activity
  GROUP BY user_id
)
SELECT
  DATE_TRUNC('week', first_date) AS cohort,
  COUNT(DISTINCT f.user_id) AS size,
  COUNT(DISTINCT ua.user_id) AS returned
FROM first_visit f
LEFT JOIN user_activity ua
  ON f.user_id = ua.user_id
  AND ua.activity_date = f.first_date + 1
GROUP BY cohort;

Когортный анализ — задача для продуктовых аналитиков уровня Senior.

SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items oi
  ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

LEFT JOIN + WHERE IS NULL — проверяет понимание типов JOIN и работу с NULL.