lesson 45 Window functions · advanced

Top N per group & ties

Find the leading records within each group and handle ties.

the idea

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.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

videos4 columns · 6 rows

idinteger · key	creator_idinteger	titletext	duration_secondsinteger
1	10	SQL in ten minutes	600
2	10	Understanding joins	900
3	11	A day outdoors	300
4	12	Window functions	720
5	12	Build a project	1200

showing 5 of 6 rows

watch_events5 columns · 26 rows

idinteger · key	viewer_idinteger	video_idinteger	watched_attext	watch_secondsinteger
1	1	1	2025-01-01 10:00:00	300
2	1	2	2025-01-01 11:00:00	450
3	2	1	2025-01-02 12:00:00	600
4	3	3	2025-01-02 13:00:00	120
5	1	4	2025-01-02 14:00:00	700

showing 5 of 26 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

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;

Watch for: Adding video_id to a DENSE_RANK ordering eliminates watch-time ties.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

Rank videos inside each creator. Top-N per group needs a partition. Aggregate watch time per video, then number videos within each creator.

step-1.sql

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.creator_id, v.id
ORDER BY v.creator_id, watch_seconds DESC;

Keep the top rows per group. ROW_NUMBER or RANK inside the creator partition selects the top videos. Ties need an explicit rule.

step-2.sql

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;