StrataScratch (Amazon) — Marketing Campaign Success: повторные покупки после дня 1

Условие

Таблица marketing_campaign(user_id, created_at, product_id, quantity, price) — покупки.

Маркетинговая кампания запускается на следующий день после первой покупки пользователя. Найдите пользователей, которые:

• Совершили первую покупку (любого товара),
• Затем (минимум через день) купили другой продукт (отличный от того, что купили в первый день).

Те, кто купил много продуктов в первый день и ничего больше — не считаются. Верните количество таких пользователей.

Решение

WITH first_day AS (
  SELECT
    user_id,
    MIN(created_at) AS first_date,
    ARRAY_AGG(DISTINCT product_id) FILTER (
      WHERE created_at = MIN(created_at) OVER (PARTITION BY user_id)
    ) AS day1_products
  FROM marketing_campaign
  GROUP BY user_id
),
later AS (
  SELECT DISTINCT
    mc.user_id,
    mc.product_id
  FROM marketing_campaign mc
  JOIN (
    SELECT user_id, MIN(created_at) AS first_date FROM marketing_campaign GROUP BY user_id
  ) f ON f.user_id = mc.user_id
  WHERE mc.created_at > f.first_date
),
day1_products AS (
  SELECT DISTINCT
    mc.user_id,
    mc.product_id
  FROM marketing_campaign mc
  JOIN (
    SELECT user_id, MIN(created_at) AS first_date FROM marketing_campaign GROUP BY user_id
  ) f ON f.user_id = mc.user_id AND mc.created_at = f.first_date
)
SELECT COUNT(DISTINCT l.user_id) AS converted_users
FROM later l
WHERE NOT EXISTS (
  SELECT 1 FROM day1_products d
  WHERE d.user_id = l.user_id AND d.product_id = l.product_id
);

Идея

1. Для каждого user — множество продуктов, купленных в день 1.
2. Множество продуктов, купленных позже (created_at > first_date).
3. Конверсия — если есть хотя бы один продукт во втором множестве, которого нет в первом.

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

1. «После дня 1» = > first_date (строгое). Если >= — попадут все, кто что-то ещё купил в тот же день. По задаче — нет.
2. Тот же продукт. Покупка того же товара после дня 1 — не конверсия по этой логике (предполагается, что кампания «открывает» новые товары).
3. first_date как DATE, а не TIMESTAMP. Если created_at TIMESTAMP, то «в этот же день» = DATE_TRUNC('day', created_at) = first_day. Иначе час 23:59 vs 00:01 — две «соседние даты», но семантически одна.

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

Set-difference между «продуктами дня 1» и «продуктами позже». EXISTS / EXCEPT — два паттерна, оба валидные. Считаем юзеров, у которых разность непуста.