Уникальные клиенты по месяцам

Задача

Маркетинг оценивает активную аудиторию по месяцам: «Сколько разных клиентов платили нам каждый месяц? Один клиент, заплативший пять раз за месяц, — это один активный клиент, а не пять».

Верни по каждому месяцу: сам месяц и число уникальных клиентов, совершивших хотя бы один платёж.

Схема данных

Учебная база проката dvdrental, одна таблица:

• payment(payment_id, customer_id, amount, payment_date, ...) — платежи; payment_date — момент платежа (timestamp), customer_id — кто платил. Один клиент за месяц может встречаться много раз.

Решение

SELECT to_char(date_trunc('month', payment_date), 'YYYY-MM') AS month,
       count(DISTINCT customer_id) AS active_customers
FROM payment
GROUP BY month
ORDER BY month;

Разбор

Задача держится на двух приёмах: сгруппировать платежи по месяцу и посчитать в каждом месяце именно разных клиентов.

date_trunc('month', payment_date) «обрубает» дату до начала месяца: и 2007-04-03, и 2007-04-28 попадают в один и тот же месяц — 2007-04. Все платежи одного месяца получают одинаковый ключ, и GROUP BY month складывает их в одну группу. Без date_trunc пришлось бы вытаскивать год и месяц по отдельности через EXTRACT и группировать по паре полей — работает, но date_trunc короче и сразу даёт нормальную дату для группировки и сортировки. Снаружи оборачиваем результат в to_char(..., 'YYYY-MM') — так на выходе читаемая строка месяца, а не полная дата с обнулённым временем.

Сердце задачи — COUNT(DISTINCT customer_id). Обычный COUNT(customer_id) посчитал бы все платежи месяца, и клиент с пятью платежами накрутил бы пятёрку. DISTINCT внутри COUNT сначала отбрасывает дубли customer_id, а потом считает — так получается «сколько разных людей», а не «сколько строк». Это ровно та метрика, которую спрашивает маркетинг: активная аудитория, а не число транзакций. Механика дедупликации та же, что в вопросе про поиск и удаление дублей.

Ещё одна тонкость: COUNT(DISTINCT customer_id) игнорирует NULL — если бы в платеже не был проставлен клиент, такая строка в счёт не пошла бы. Для активной аудитории это правильно: аноним — не уникальный клиент.

GROUP BY month здесь ссылается на алиас из SELECT — PostgreSQL это разрешает в GROUP BY и ORDER BY (но не в WHERE). Можно было повторить и всё выражение date_trunc(...) — результат тот же.

Ожидаемый результат

month | active_customers
--- | ---
2007-02 | 518
2007-03 | 599
2007-04 | 599
2007-05 | 158