LeetCode SQL — Hopper Queries II: число сотрудников по датам (interval join)

Условие

Таблица Drivers(driver_id, join_date). Нужно для каждого месяца 2020 года посчитать, сколько водителей уже подписались к концу месяца. Если за месяц подписавшихся не было — повторить предыдущее значение.

(Упрощённая постановка из 1645. Hopper Company Queries II.)

Решение

Шаг 1 — календарь месяцев

WITH months AS (
  SELECT generate_series(
    DATE '2020-01-01',
    DATE '2020-12-01',
    INTERVAL '1 month'
  )::DATE AS month_start
),

В MySQL календарь придётся собрать вручную или через CTE-рекурсию.

Шаг 2 — для каждого месяца считаем count(driver_id WHERE join_date <= last day)

calendar AS (
  SELECT EXTRACT(MONTH FROM month_start)::INT AS month,
         (month_start + INTERVAL '1 month - 1 day')::DATE AS month_end
  FROM months
)
SELECT c.month,
       (SELECT COUNT(*)
        FROM Drivers d
        WHERE d.join_date <= c.month_end) AS active_drivers
FROM calendar c
ORDER BY c.month;

Шаг 3 — если просят накопительный счётчик «новых в месяц»

WITH joins AS (
  SELECT DATE_TRUNC('month', join_date)::DATE AS m, COUNT(*) AS new_drivers
  FROM Drivers
  WHERE join_date < '2021-01-01'
  GROUP BY 1
)
SELECT c.month_start,
       COALESCE(SUM(new_drivers) OVER (ORDER BY c.month_start), 0) AS cum_drivers
FROM months c
LEFT JOIN joins j ON j.m = c.month_start
ORDER BY c.month_start;

SUM OVER (ORDER BY ...) даёт running total. Если в месяце нет новых — j.new_drivers = NULL, но SUM игнорирует NULL и просто переносит предыдущее значение.

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

1. Без календаря. Если просто GROUP BY по месяцу из Drivers — пропустим месяцы без новых регистраций.
2. LEFT JOIN + window sum. SUM(... ) OVER с NULL ведёт себя корректно (игнорирует), но в некоторых СУБД (Spark) ведёт себя иначе — лучше COALESCE(new_drivers, 0) сразу.
3. Границы месяца. «К концу месяца» — join_date <= last_day, не <. Граничный случай — кто-то зарегистрировался 31 января в 23:59.

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

Сгенерировать календарь месяцев → LEFT JOIN с агрегированными новыми регистрациями → SUM(... ) OVER (ORDER BY month) для накопительного итога.