Patterns: Top-N, Dedup, Gaps & Islands
Advanced · 27 min read · ▶ live playground · ✦ checkpoint
Window functions become practical when you stop trying to use them directly in WHERE. The reusable move is compute first, filter one step later: use a CTE to annotate rows with a window value, then let the next CTE or outer query keep the rows you want.
That is 7.3's pipeline rule with window functions plugged in. One transformation per step. Name the step. Inspect it when confused. Then build the next shape.
Top-N per group
Top-N per group means "the highest or lowest N rows inside each group." The pattern is ROW_NUMBER() in a CTE, then WHERE in the next step.
WITH ranked_purchases AS (
SELECT ce.event_id, ce.branch, ce.customer_id, ce.event_ts, ce.amount,
row_number() OVER (
PARTITION BY ce.branch
ORDER BY ce.amount DESC, ce.event_ts, ce.event_id
) AS branch_rank
FROM cafe_events AS ce
WHERE ce.event_type = 'purchase'
)
SELECT rp.branch, rp.branch_rank, rp.event_id, rp.customer_id, rp.amount
FROM ranked_purchases AS rp
WHERE rp.branch_rank <= 2
ORDER BY rp.branch, rp.branch_rank, rp.event_id; branch | branch_rank | event_id | customer_id | amount
--------+-------------+----------+-------------+--------
campus | 1 | 31 | 106 | 20.00
campus | 2 | 32 | 106 | 17.25
center | 1 | 24 | 104 | 22.00
center | 2 | 9 | 101 | 18.00
harbor | 1 | 30 | 105 | 19.25
harbor | 2 | 29 | 105 | 18.50
(6 rows)The CTE's job is annotation: branch_rank writes each purchase's position inside its branch. The outer query's job is filtering: keep ranks 1 and 2. If the business rule says ties should all survive, swap ROW_NUMBER() for RANK() or DENSE_RANK() from 13.2. If the rule says exactly two rows per branch, ROW_NUMBER() is the right tool.
Dedup: keep the newest row
Deduplication means keeping one row when several rows represent the same real-world thing. In cafe_events, profile updates repeat by event_key; the newest update wins.
WITH ranked_updates AS (
SELECT ce.event_id, ce.event_key, ce.customer_id, ce.event_ts, ce.branch,
row_number() OVER (
PARTITION BY ce.event_key
ORDER BY ce.event_ts DESC, ce.event_id DESC
) AS newest_rank
FROM cafe_events AS ce
WHERE ce.event_type = 'profile_update'
)
SELECT ru.event_key, ru.event_id, ru.customer_id, ru.event_ts, ru.branch
FROM ranked_updates AS ru
WHERE ru.newest_rank = 1
ORDER BY ru.customer_id; event_key | event_id | customer_id | event_ts | branch
-------------+----------+-------------+---------------------+--------
profile:101 | 12 | 101 | 2026-05-06 07:45:00 | center
profile:102 | 18 | 102 | 2026-05-04 07:45:00 | harbor
profile:103 | 23 | 103 | 2026-05-02 10:45:00 | campus
profile:104 | 28 | 104 | 2026-05-04 12:45:00 | center
(4 rows)This is the same shape as top-N. Partition by the key that defines "same thing." Order by the row you want to keep first. Filter to 1 one step later.
Gaps and islands: consecutive visit streaks
Gaps and islands is the pattern for finding runs of consecutive values: the islands are the runs, and the gaps are the breaks between them. For daily streaks, the trick is slow but beautiful: number the days, then subtract that number from each day. Consecutive days produce the same island key.
WITH visit_days AS (
SELECT DISTINCT ce.customer_id,
CAST(ce.event_ts AS date) AS visit_day
FROM cafe_events AS ce
WHERE ce.customer_id = 101
AND ce.event_type = 'purchase'
),
numbered_days AS (
SELECT vd.visit_day,
row_number() OVER (ORDER BY vd.visit_day) AS rn
FROM visit_days AS vd
),
islands AS (
SELECT nd.visit_day,
nd.visit_day - nd.rn::int AS island_key
FROM numbered_days AS nd
)
SELECT min(i.visit_day) AS start_day,
max(i.visit_day) AS end_day,
count(*) AS streak_days
FROM islands AS i
GROUP BY i.island_key
ORDER BY start_day; start_day | end_day | streak_days
------------+------------+-------------
2026-05-01 | 2026-05-03 | 3
2026-05-05 | 2026-05-06 | 2
(2 rows)Here is the arithmetic in words. May 1 minus row number 1, May 2 minus row number 2, and May 3 minus row number 3 all land on the same hidden key. May 5 minus row number 4 lands somewhere else, so a new island starts. The final query groups by that hidden key and reports each streak.
Sessionizing events
Sessionization means grouping timestamped events into visits based on a time gap. Here the rule is fixed: a new session starts when customer 101 has more than 30 minutes between events.
WITH ordered_events AS (
SELECT ce.event_id, ce.event_ts, ce.event_type,
lag(ce.event_ts) OVER (
PARTITION BY ce.customer_id
ORDER BY ce.event_ts, ce.event_id
) AS previous_ts
FROM cafe_events AS ce
WHERE ce.customer_id = 101
AND ce.event_type <> 'profile_update'
AND CAST(ce.event_ts AS date) = DATE '2026-05-01'
),
flagged AS (
SELECT oe.event_id, oe.event_ts, oe.event_type, oe.previous_ts,
CASE
WHEN oe.previous_ts IS NULL
OR oe.event_ts - oe.previous_ts > INTERVAL '30 minutes' THEN 1
ELSE 0
END AS new_session
FROM ordered_events AS oe
),
sessionized AS (
SELECT f.event_id, f.event_ts, f.event_type, f.new_session,
sum(f.new_session) OVER (
ORDER BY f.event_ts, f.event_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_id
FROM flagged AS f
)
SELECT s.event_id, s.event_ts, s.event_type, s.new_session, s.session_id
FROM sessionized AS s
ORDER BY s.event_ts, s.event_id; event_id | event_ts | event_type | new_session | session_id
----------+---------------------+------------+-------------+------------
1 | 2026-05-01 08:00:00 | app_open | 1 | 1
2 | 2026-05-01 08:07:00 | menu_view | 0 | 1
3 | 2026-05-01 08:18:00 | purchase | 0 | 1
4 | 2026-05-01 09:05:00 | menu_view | 1 | 2
5 | 2026-05-01 09:14:00 | purchase | 0 | 2
(5 rows)Each CTE has one job. ordered_events brings in the previous timestamp. flagged turns a gap into a 1 or 0. sessionized runs a total over those flags, so each new 1 increments the session id.
When windows beat self-joins
Use a window when the question says "this row, compared with nearby rows or other rows in its group": top purchases per branch, latest update per key, previous event, running total, streak, session. You stay in the annotate-don't-collapse model and avoid inventing a second copy of the same table just to compare rows.
Use a self-join when the result is genuinely made of row pairs: staff and their managers, overlapping reservations, products often bought together. The test is the output shape. If each original row needs a margin note, reach for a window. If the answer row represents a match between two separate rows, reach for a join. For speed, measure the real query plan in the performance section; don't guess from the shape alone.
Section 13 gave you the window toolkit: annotate totals, rank rows, look at neighbors, control frames, and turn those pieces into patterns. Next section changes gears to recursion and generated series, where queries can build rows that were not already in the table.
Checkpoint
Answer all three to mark this lesson complete