33. Practical patterns: dedup with ROW_NUMBER, top-N per group, gaps & islands

This lesson doesn't introduce new syntax — it's three of the most common real-world problems window functions solve, using only what the last four lessons already covered. Recognizing these shapes is often more valuable than knowing the individual functions in isolation.

Pattern 1: filtering on a window function's result

Every pattern below needs this first, since (as the window-basics lesson showed) window functions can't appear in WHERE. The fix is always the same: compute the window function in a subquery or CTE, then filter in the outer query, where it's just an ordinary column by that point:

WITH ranked AS (
    SELECT title, rating, rental_rate,
           row_number() OVER (PARTITION BY rating ORDER BY rental_rate DESC) AS rn
    FROM film
)
SELECT title, rating, rental_rate
FROM ranked
WHERE rn = 1;

Pattern 2: deduplication with ROW_NUMBER

A extremely common real problem: a table has duplicate rows (or near-duplicates) for what should be a unique entity, and you need exactly one row per entity — typically "the most recent" or "the first."

-- Keep only each customer's MOST RECENT rental (one row per customer).
WITH ranked_rentals AS (
    SELECT rental_id, customer_id, rental_date,
           row_number() OVER (PARTITION BY customer_id ORDER BY rental_date DESC) AS rn
    FROM rental
)
SELECT rental_id, customer_id, rental_date
FROM ranked_rentals
WHERE rn = 1
ORDER BY customer_id
LIMIT 10;

This is also the standard technique for actually deleting true duplicate rows from a table that lacks a uniqueness constraint (a one-time data cleanup, not a query pattern to run routinely) — DELETE FROM t WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1), using ROW_NUMBER() to mark every row after the first in each duplicate group for removal. (ctid is Postgres's physical row identifier — an internals detail this course doesn't cover further, mentioned here only so the pattern is recognizable if you meet it in the wild.)

Pattern 3: top-N per group

The generalization of Pattern 2 — instead of exactly 1 row per group, keep the top N:

-- Top 3 most expensive films per rating.
WITH ranked AS (
    SELECT title, rating, rental_rate,
           row_number() OVER (PARTITION BY rating ORDER BY rental_rate DESC, title) AS rn
    FROM film
)
SELECT title, rating, rental_rate
FROM ranked
WHERE rn <= 3
ORDER BY rating, rn;

Use ROW_NUMBER() when you want exactly N rows per group regardless of ties (arbitrary tie-break). Use RANK() instead if ties should legitimately produce more than N rows for a group (e.g. "top 3" where a 4-way tie for 3rd place means you actually want all 4). This choice — which ranking function backs a top-N query — is a real design decision, not a stylistic one; picking wrong either silently drops a legitimately- tied row or returns more rows than a report expects.

Pattern 4: gaps and islands

A classic, trickier pattern: given a sequence of rows with a date (or other ordered) column, find contiguous runs ("islands") separated by gaps. The standard trick: subtract a ROW_NUMBER() (which always increments by exactly 1) from the actual ordered value — inside a contiguous run, both increase in lockstep, so the difference stays constant; at a gap, the difference jumps. That constant difference becomes a group identifier for each island.

-- Find each customer's contiguous-day rental streaks (days with at
-- least one rental, no gap day in between).
WITH daily_activity AS (
    -- one row per (customer, distinct rental day)
    SELECT DISTINCT customer_id, rental_date::date AS rental_day
    FROM rental
),
numbered AS (
    SELECT customer_id, rental_day,
           row_number() OVER (PARTITION BY customer_id ORDER BY rental_day) AS rn
    FROM daily_activity
),
islands AS (
    SELECT customer_id, rental_day,
           rental_day - (rn * interval '1 day') AS island_key  -- constant within a streak
    FROM numbered
)
SELECT customer_id, min(rental_day) AS streak_start, max(rental_day) AS streak_end,
       count(*) AS streak_length
FROM islands
GROUP BY customer_id, island_key
HAVING count(*) > 1   -- only genuine multi-day streaks
ORDER BY streak_length DESC
LIMIT 10;

This is genuinely one of the more advanced patterns in this course — worth returning to once window functions feel comfortable, rather than something to memorize on first read. The core idea to hold onto: ordered_value - row_number() is constant within a contiguous run and changes at every gap — everything else in the pattern is bookkeeping around that one trick.

Check yourself

1. Why can't you write WHERE row_number() OVER (...) = 1 directly, and what's the standard fix?
2. For a "top 3 per group" report where ties should be included even if they push the group above 3 rows, would you use ROW_NUMBER() or RANK()?
3. In the gaps-and-islands pattern, why does rental_day - row_number() (as an interval) stay constant within a contiguous streak but change at a gap?