Спортмастер стажировка: AOV и частота покупок 1 года с момента ввода email

Условие

Найти средний оборот на одного клиента и среднюю частоту покупок в течение 1 года с момента указания email в личном кабинете. Учитываются только те клиенты, которые впервые указали email в сентябре 2019 г.

Таблицы:

• table_ident(id_client, ident_type, dat_inserted, ident) — события указания идентификаторов.
• table_sales(id_client, id_check, sales, channel, dat_check).

Решение

Подход

1. Когорта: клиенты, у которых первый ident_type='Email' пришёл в сентябре 2019.
2. Период анализа на клиента: [email_first_date, email_first_date + 1 year).
3. На клиента: revenue = SUM(sales), n_purchases = COUNT(*), период_дней = 365.
4. Среднее по когорте.

SQL

WITH first_email AS (
  SELECT id_client, MIN(dat_inserted) AS email_dt
  FROM table_ident
  WHERE ident_type = 'Email'
  GROUP BY id_client
  HAVING MIN(dat_inserted)::date BETWEEN DATE '2019-09-01' AND DATE '2019-09-30'
),
purchases AS (
  SELECT s.id_client,
         SUM(s.sales)         AS revenue,
         COUNT(s.id_check)    AS n_purchases
  FROM table_sales s
  JOIN first_email f ON f.id_client = s.id_client
  WHERE s.dat_check >= f.email_dt
    AND s.dat_check <  f.email_dt + INTERVAL '1 year'
  GROUP BY s.id_client
)
SELECT
  AVG(revenue)                                 AS avg_revenue_per_client,
  AVG(n_purchases::float)                      AS avg_frequency,
  AVG(revenue / NULLIF(n_purchases, 0))        AS avg_aov_per_client
FROM purchases;

Если хочется обработать 0 покупок

... LEFT JOIN purchases ... → COALESCE(revenue, 0), COALESCE(n_purchases, 0) ...

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

1. «Впервые указал email» — может быть несколько строк ident_type=Email; берём MIN(dat_inserted).
2. У клиента может быть Email в августе 2019 → выпадает из когорты, даже если в сентябре указан повторно.
3. Окно [email_dt, email_dt + 1 year) — не привязано к календарному году, для каждого клиента своё.
4. Если клиент за год ничего не купил, он не попадёт в purchases — INNER JOIN теряет нули. Чтобы средний оборот считать по всей когорте (а не только купившим) — LEFT JOIN или COALESCE.
5. Усреднение «частоты»: число покупок в год на клиента; не деление общей суммы покупок когорты на число клиентов.

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

Когорта = клиенты с первым ident_type=Email в сентябре 2019. На каждого считаем revenue и n_purchases в окне 1 год от email-даты, затем усредняем по когорте.