StrataScratch (Spotify) — трендовые песни: top-N за неделю vs предыдущую

Условие

Таблица spotify_streams(song_id, stream_date, listen_count). Для последней полной недели в данных найдите топ-10 песен по SUM(listen_count). Для каждой топ-10 покажите её ранг на этой неделе и ранг на предыдущей. Если песня не была в топ-100 предыдущей недели — prev_rank = NULL.

Решение

WITH weekly AS (
  SELECT
    song_id,
    DATE_TRUNC('week', stream_date)::DATE AS week,
    SUM(listen_count) AS streams
  FROM spotify_streams
  GROUP BY song_id, DATE_TRUNC('week', stream_date)
),
last_two AS (
  SELECT DISTINCT week FROM weekly
  ORDER BY week DESC
  LIMIT 2
),
ranked AS (
  SELECT
    w.*,
    DENSE_RANK() OVER (PARTITION BY w.week ORDER BY w.streams DESC) AS rnk
  FROM weekly w
  WHERE w.week IN (SELECT week FROM last_two)
),
current_week AS (
  SELECT * FROM ranked
  WHERE week = (SELECT MAX(week) FROM ranked)
    AND rnk <= 10
),
prev_week AS (
  SELECT * FROM ranked
  WHERE week = (SELECT MIN(week) FROM ranked)
    AND rnk <= 100
)
SELECT
  c.song_id,
  c.rnk AS current_rank,
  p.rnk AS prev_rank,
  c.streams
FROM current_week c
LEFT JOIN prev_week p ON p.song_id = c.song_id
ORDER BY c.rnk;

Тонкость с «последней полной неделей»

DATE_TRUNC('week', ...) даёт понедельник. Если данные обрываются в среду — текущая неделя «неполная», ранжирование некорректно. Можно отфильтровать неделей < current_week_start, либо передавать дату cutoff параметром.

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

1. DENSE_RANK vs RANK. При ties на 10-м месте RANK пропустит позиции 11, 12; DENSE_RANK выдаст 11. Для top-10 важно понимать, что «ties included» = DENSE_RANK <= 10, иначе ROW_NUMBER <= 10.
2. MIN(week) среди двух не работает, если в данных есть 3+ недель. Запрос полагается на LIMIT 2 в last_two. Поэтому current_week = MAX(week), prev_week = MIN(week).
3. Песни, которых нет в prev_week. LEFT JOIN → prev_rank = NULL. Что и просили.

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

DATE_TRUNC('week') → SUM streams → DENSE_RANK per week → top-10 текущей нед LEFT JOIN top-100 пред нед. Внимание на «последнюю полную» неделю.