SQL — переходы Regular → Loyal и анти-джойн на категориях

Условие

Задача 4.1

Дана таблица Order(userid, status, order, date_of_purchase, price, quantity). Status принимает значения regular или loyal. Все заказы пользователя в regular идут до его заказов в loyal (статус не возвращается обратно). Нужно по каждому пользователю посчитать:

1. Абсолютную разницу выручки между последней покупкой в regular и первой покупкой в loyal.
2. Количество уникальных дневных покупок в каждом статусе. Формат: userid, regular_purchases, loyal_purchases.
3. Разница в днях между первой regular и второй loyal покупкой.

Задача 4.2

Даны таблицы Purchases(userid, item) и Users(userid, name). Найти уникальные имена пользователей, которые покупали книги, но НЕ покупали одежду.

Решение задачи 4.1

Идея

Все три подвопроса — варианты «top-1 / top-2 на пользователя в группе»: выбираем нужные строки оконными функциями, потом считаем разницу.

Код для всех трёх вопросов

WITH ranked AS (
  SELECT
    userid,
    status,
    "order",
    date_of_purchase,
    price,
    quantity,
    price * quantity AS revenue,
    ROW_NUMBER() OVER (
      PARTITION BY userid, status ORDER BY date_of_purchase ASC, "order" ASC
    ) AS rn_asc,
    ROW_NUMBER() OVER (
      PARTITION BY userid, status ORDER BY date_of_purchase DESC, "order" DESC
    ) AS rn_desc
  FROM "Order"
),
last_regular AS (
  SELECT userid, revenue AS last_regular_revenue, date_of_purchase AS last_regular_date
  FROM ranked
  WHERE status = 'regular' AND rn_desc = 1
),
first_loyal AS (
  SELECT userid, revenue AS first_loyal_revenue, date_of_purchase AS first_loyal_date
  FROM ranked
  WHERE status = 'loyal' AND rn_asc = 1
),
first_regular AS (
  SELECT userid, date_of_purchase AS first_regular_date
  FROM ranked WHERE status = 'regular' AND rn_asc = 1
),
second_loyal AS (
  SELECT userid, date_of_purchase AS second_loyal_date
  FROM ranked WHERE status = 'loyal' AND rn_asc = 2
),
counts AS (
  SELECT
    userid,
    COUNT(DISTINCT CASE WHEN status='regular' THEN date_of_purchase END) AS regular_purchases,
    COUNT(DISTINCT CASE WHEN status='loyal'   THEN date_of_purchase END) AS loyal_purchases
  FROM "Order"
  GROUP BY userid
)
SELECT
  COALESCE(c.userid, lr.userid, fl.userid)                AS userid,
  ABS(fl.first_loyal_revenue - lr.last_regular_revenue)   AS revenue_diff_regular_loyal,
  c.regular_purchases,
  c.loyal_purchases,
  (sl.second_loyal_date - fr.first_regular_date)          AS days_first_reg_to_2nd_loyal
FROM counts c
LEFT JOIN last_regular  lr USING (userid)
LEFT JOIN first_loyal   fl USING (userid)
LEFT JOIN first_regular fr USING (userid)
LEFT JOIN second_loyal  sl USING (userid);

Если разные подвопросы запрашиваются отдельно — каждый можно делать отдельным запросом, не объединяя в одном.

Замечания

• "Уникальные дневные покупки в статусе" — это COUNT(DISTINCT date_of_purchase), а не COUNT(*). Если в один день два заказа — это одна «дневная покупка».
• В разных диалектах разница дат пишется по-разному: PostgreSQL — (d1 - d2), BigQuery — DATE_DIFF(d1, d2, DAY), MS SQL — DATEDIFF(DAY, d2, d1).

Решение задачи 4.2

Идея

«Покупал книги, но не одежду» — это anti-join на категории:

• Множество пользователей с книгами.
• Минус (EXCEPT / NOT EXISTS) множество пользователей с одеждой.

Код

SELECT DISTINCT u.name
FROM Users u
WHERE u.userid IN (SELECT userid FROM Purchases WHERE item = 'Books')
  AND u.userid NOT IN (SELECT userid FROM Purchases WHERE item = 'Clothes');

Безопаснее (без NULL-ловушек):

SELECT DISTINCT u.name
FROM Users u
JOIN Purchases pb ON pb.userid = u.userid AND pb.item = 'Books'
WHERE NOT EXISTS (
  SELECT 1 FROM Purchases pc
  WHERE pc.userid = u.userid AND pc.item = 'Clothes'
);

Или через множества:

SELECT u.name
FROM Users u
WHERE u.userid IN (
  SELECT userid FROM Purchases WHERE item = 'Books'
  EXCEPT
  SELECT userid FROM Purchases WHERE item = 'Clothes'
);

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

По 4.1

1. Tie-break при равных датах. Если в один и тот же день несколько покупок — ROW_NUMBER() ORDER BY date неоднозначен. Добавляйте вторичный ключ (order или id).
2. Пользователи с одним статусом. Кто-то может быть только regular или только loyal. LEFT JOIN спасает; если использовать INNER JOIN — потеряете их.
3. Абсолютная разница revenue. Не забывайте ABS(). Условие явно про модуль.
4. COUNT(*) вместо COUNT(DISTINCT date). Условие про «уникальные дневные покупки». Дубликаты дат не считаются.
5. Пользователи без второй loyal-покупки. Их нет в second_loyal — соответствующее поле в финальном результате будет NULL, это корректно.
6. Разные форматы даты. В исходных данных может быть строка — каст в DATE обязателен.

По 4.2

1. NOT IN (SELECT …) с NULL. Если в подзапросе есть NULL, результат может стать пустым (трёхзначная логика). Используйте NOT EXISTS или EXCEPT.
2. Регистр и пробелы. «Books» vs «books» vs «Book ». Лучше LOWER(TRIM(item)).
3. Пользователь без одного и без другого. Если он не покупал ни книги, ни одежду — он не должен быть в выводе. Условие purchased Books AND NOT purchased Clothes.
4. DISTINCT. Если у пользователя несколько покупок книг — без DISTINCT имя задвоится.

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

4.1: оконные функции ROW_NUMBER() OVER (PARTITION BY userid, status ORDER BY date) для отбора первой/последней/второй покупки в каждом статусе, потом JOIN-ы или CTE-блоки. COUNT(DISTINCT date) для дневных покупок. ABS() для разницы выручки.

4.2: JOIN на пользователях с книгами + NOT EXISTS (или EXCEPT) на пользователях с одеждой. Не использовать NOT IN без проверки на NULL.