LeetCode SQL — Market Analysis I: заказы пользователей за 2019 год

Условие

Таблицы:

• Users(user_id, join_date, favorite_brand)
• Orders(order_id, order_date, item_id, buyer_id, seller_id)

Для каждого пользователя верните:

• buyer_id,
• join_date,
• orders_in_2019 — количество заказов в 2019 году, где он покупатель.

Все пользователи должны быть в выводе, даже без заказов (0).

Решение

Ключ — условие в JOIN, не в WHERE

SELECT
  u.user_id AS buyer_id,
  u.join_date,
  COUNT(o.order_id) AS orders_in_2019
FROM Users u
LEFT JOIN Orders o
  ON o.buyer_id = u.user_id
 AND o.order_date BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY u.user_id, u.join_date;

Почему AND в ON, а не в WHERE

Если перенести фильтр по дате в WHERE, LEFT JOIN превратится в INNER JOIN: строки, где o.order_date IS NULL, отфильтруются. Тогда пользователи без заказов 2019 пропадут.

-- НЕВЕРНО для задачи
SELECT u.user_id, u.join_date, COUNT(o.order_id)
FROM Users u
LEFT JOIN Orders o ON o.buyer_id = u.user_id
WHERE o.order_date BETWEEN '2019-01-01' AND '2019-12-31'  -- убивает LEFT
GROUP BY u.user_id, u.join_date;

Эквивалент через коррелированный подзапрос

SELECT
  u.user_id AS buyer_id,
  u.join_date,
  (SELECT COUNT(*) FROM Orders o
   WHERE o.buyer_id = u.user_id
     AND EXTRACT(YEAR FROM o.order_date) = 2019) AS orders_in_2019
FROM Users u;

Подводные камни

1. WHERE ломает LEFT JOIN. Самая частая ошибка по этой задаче.
2. COUNT(*) vs COUNT(o.order_id). Первое даст 1 для пользователей без заказов (строка с NULL всё равно считается). Второе — корректные 0 (COUNT игнорирует NULL).
3. Год через LIKE '2019%'. Работает, но EXTRACT(YEAR ...) или BETWEEN — чище и индексируется.

Эталонный ответ

LEFT JOIN с фильтром по дате внутри ON, COUNT(o.order_id) (не *), GROUP BY юзера.