LeetCode SQL — Game Play Analysis V: Day-1 retention по install_date

Условие

Таблица Activity(player_id, device_id, event_date, games_played) — лог сессий. Первый event_date пользователя считается датой установки.

Для каждой install_date верните:

• installs — сколько игроков установили игру в этот день,
• Day1_retention — долю игроков, которые вернулись на следующий день (округление до 2 знаков).

Структура данных

Activity(player_id INT, device_id INT, event_date DATE, games_played INT)
PRIMARY KEY (player_id, event_date)

Решение

Подход

1. Найти install_date = MIN(event_date) для каждого игрока.
2. LEFT JOIN обратно с Activity по event_date = install_date + 1 day.
3. Если совпало — игрок вернулся.

WITH installs AS (
  SELECT player_id, MIN(event_date) AS install_date
  FROM Activity
  GROUP BY player_id
)
SELECT
  i.install_date,
  COUNT(*) AS installs,
  ROUND(
    SUM(CASE WHEN a.player_id IS NOT NULL THEN 1 ELSE 0 END)::DECIMAL
    / COUNT(*),
    2
  ) AS Day1_retention
FROM installs i
LEFT JOIN Activity a
  ON a.player_id = i.player_id
 AND a.event_date = i.install_date + INTERVAL '1 day'
GROUP BY i.install_date;

Альтернатива — LEAD() по датам

WITH ranked AS (
  SELECT
    player_id,
    event_date,
    ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn,
    LEAD(event_date) OVER (PARTITION BY player_id ORDER BY event_date) AS next_date
  FROM Activity
)
SELECT
  event_date AS install_date,
  COUNT(*) AS installs,
  ROUND(SUM(CASE WHEN next_date = event_date + 1 THEN 1 ELSE 0 END)::DECIMAL
        / COUNT(*), 2) AS Day1_retention
FROM ranked
WHERE rn = 1
GROUP BY event_date;

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

1. Пропустить игроков без возврата. INNER JOIN обнулит знаменатель — нужен LEFT JOIN или SUM с CASE.
2. event_date + 1 vs INTERVAL. В MySQL DATE_ADD(install_date, INTERVAL 1 DAY), в Postgres проще install_date + 1.
3. NULL в делителе. Если для дня нет установок — он просто не появится в выводе. Не делите на COUNT(DISTINCT a.player_id) (может стать 0).

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

LEFT JOIN install + Activity по event_date = install + 1. Числитель — игроки, у которых next-day матч нашёлся. Округление через ROUND(..., 2). Hard-уровень из-за нюанса с null-знаменателем и приведения к decimal.