milestone 4/4 Top N per group & ties

Find the leading records within each group and handle ties.

Video Product Analytics · Move from watch events to daily engagement, rolling trends, and signup-cohort retention.

approach

Aggregate watched seconds per video, then rank videos within creator. ROW_NUMBER gives an exact N with a tie-breaker; DENSE_RANK includes ties and may return more than N. Make the tie policy explicit.

WITH totals AS(
  SELECT
    v.creator_id,
    v.id AS video_id,
    SUM(w.watch_seconds) AS watch_seconds
  FROM videos v
  JOIN watch_events w ON w.video_id=v.id
  GROUP BY v.id,v.creator_id
), ranked AS(
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY creator_id ORDER BY watch_seconds DESC,video_id) AS rn
  FROM totals
)
SELECT creator_id,video_id,watch_seconds
FROM ranked
WHERE rn<=2;

data for this milestone