WoWS Data Engineer — паттерны покупок: схлопывание подряд идущих common_pack

Условие

Дана таблица purchases:

• purchaser_id — id пользователя;
• purchase — тип покупки: 'common_pack' или 'exclusive_pack';
• purchase_dt — дата покупки.

Посчитайте число пользователей для каждого уникального паттерна покупок. Правила:

1. Каждый пользователь имеет один паттерн (sub-series не считаются).
2. Покупки в паттерне упорядочены по дате.
3. Каждая покупка exclusive_pack должна присутствовать в паттерне.
4. Серии подряд идущих common_pack схлопываются в один common_pack.

Пример для одного пользователя:

Паттерн: C–E–E–C–E (подряд два common_pack 4–5 схлопнулись в один).

Решение

Подход

Это вариация gap-and-island / run-length encoding: схлопнуть подряд идущие одинаковые значения. Классическое решение — два уровня нумерации:

1. ROW_NUMBER() OVER (ORDER BY date) — позиция в общей последовательности.
2. ROW_NUMBER() OVER (PARTITION BY purchase ORDER BY date) — позиция внутри своего типа.
3. Разность даёт «островок»: подряд идущие одинаковые покупки имеют одинаковый island_id.

После этого внутри пользователя (island_id, purchase) — это «схлопнутый шаг» паттерна. Конкатенируем покупки по дате — получаем строку-паттерн.

Реализация — SQL (PostgreSQL / SQLite-совместимо)

WITH islands AS (
  SELECT
    purchaser_id,
    purchase,
    purchase_dt,
    ROW_NUMBER() OVER (PARTITION BY purchaser_id              ORDER BY purchase_dt)  AS rn_total,
    ROW_NUMBER() OVER (PARTITION BY purchaser_id, purchase    ORDER BY purchase_dt)  AS rn_kind
  FROM purchases
),
collapsed AS (
  -- Каждый "островок" одного типа — это (purchaser_id, purchase, rn_total - rn_kind)
  SELECT
    purchaser_id,
    purchase,
    MIN(purchase_dt) AS island_start
  FROM islands
  GROUP BY purchaser_id, purchase, rn_total - rn_kind
),
patterns AS (
  -- Конкатенируем по дате
  SELECT
    purchaser_id,
    STRING_AGG(
      CASE WHEN purchase = 'common_pack'    THEN 'C'
           WHEN purchase = 'exclusive_pack' THEN 'E'
      END,
      '-' ORDER BY island_start
    ) AS pattern
  FROM collapsed
  GROUP BY purchaser_id
)
SELECT
  pattern,
  COUNT(*) AS users
FROM patterns
GROUP BY pattern
ORDER BY users DESC;

SQLite-вариант

В SQLite нет STRING_AGG, но есть GROUP_CONCAT:

... GROUP_CONCAT(
      CASE WHEN purchase = 'common_pack' THEN 'C' ELSE 'E' END,
      '-'
    ) AS pattern

В SQLite порядок в GROUP_CONCAT не гарантирован без подзапроса с предварительной сортировкой. Решение — обернуть:

SELECT purchaser_id,
       GROUP_CONCAT(letter, '-') AS pattern
FROM (
  SELECT purchaser_id,
         CASE WHEN purchase = 'common_pack' THEN 'C' ELSE 'E' END AS letter,
         island_start
  FROM collapsed
  ORDER BY purchaser_id, island_start
)
GROUP BY purchaser_id;

Реализация — Python (если SQLite ограничивает)

import pandas as pd
from itertools import groupby
 
df = pd.read_csv("purchases.csv", parse_dates=["purchase_dt"])
 
def make_pattern(group: pd.DataFrame) -> str:
    g = group.sort_values("purchase_dt")["purchase"].tolist()
    # Run-length collapse подряд идущих одинаковых
    collapsed = [k for k, _ in groupby(g)]
    return "-".join("C" if x == "common_pack" else "E" for x in collapsed)
 
patterns = (df.groupby("purchaser_id")
              .apply(make_pattern)
              .rename("pattern")
              .reset_index())
 
result = patterns["pattern"].value_counts().rename_axis("pattern").reset_index(name="users")
print(result)

Анализ / интерпретация

Получаем таблицу: pattern → число пользователей. Это сегментация по поведению покупки:

• E-E-E — «эксклюзивщики», скупающие сразу несколько эксклюзивов.
• C-E — «попробовал общий → купил эксклюзив» — конверсионный паттерн.
• C — «купил один common, не вернулся».
• E-C — «эксклюзив → потом common» — нетипичный, может говорить о сценариях бандла.

Эти паттерны помогают командам монетизации понимать последовательности покупок и оптимизировать офферы.

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

1. Подряд значит «по дате», а не «подряд в строках таблицы». Сортировка обязательна.
2. Одинаковая дата у двух покупок: tie-breaker нужен (purchase_id, purchase_dt + transaction_id). Без него ROW_NUMBER нестабилен.
3. Один exclusive подряд — он сам себе «островок», ничего не схлопывает. Логика gap-and-island работает для серий любой длины ≥ 1.
4. GROUP_CONCAT без сортировки — паттерн может оказаться разной формы для одинаковых пользователей. Принудительная сортировка через подзапрос обязательна.
5. Sub-series не считаются. Нельзя выдавать «у юзера несколько паттернов» — только один полный.
6. Очень длинные паттерны. У pro-юзеров могут быть строки на 50+ символов — нагрузка на string operations. В крайних случаях — хешировать или ограничивать.
7. Нечитаемая запись через LAG-ы. Можно решать через LAG, но gap-and-island с двумя ROW_NUMBER короче и понятнее.

Альтернативы

• LAG-based:

  ... CASE WHEN purchase = LAG(purchase) OVER (PARTITION BY purchaser_id ORDER BY dt) THEN 0 ELSE 1 END

  с последующим CUMSUM — даёт island_id. Но менее лаконично.

• PySpark / Pandas — если данные не помещаются в одну машину и нужна параллельность.

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

Классический gap-and-island:

• rn_total = ROW_NUMBER OVER (PARTITION BY user ORDER BY dt)
• rn_kind = ROW_NUMBER OVER (PARTITION BY user, kind ORDER BY dt)
• island_id = rn_total - rn_kind — одинаковое для подряд идущих одинаковых покупок.

Затем MIN(dt) по островку и STRING_AGG (или эквивалент) в конкатенированный паттерн с разделителем. Финальный GROUP BY pattern даёт распределение пользователей по паттернам.