Ranking: ROW_NUMBER, RANK, DENSE_RANK

Window functions keep every row

You just loaded a fact_sales table and product wants a "top 3 products per category" mart. Your first instinct, GROUP BY category ORDER BY revenue DESC LIMIT 3, gives you the top 3 for one category, not per category. The moment you need "top N within each group" or "the latest row per key," you've hit the wall that window functions were invented to knock down.

A window function runs a calculation over a "window" of rows defined relative to the current row, and, crucially, keeps every input row. GROUP BY category returns one row per category; ROW_NUMBER() OVER (PARTITION BY category …) returns every product row, each tagged with its rank inside its category. You keep the detail and get the ranking.

The three ranking functions differ only in how they treat ties, and this exact distinction is a classic interview question.

A worked example

Seed a tiny product_revenue table and rank within each category with all three functions. You can run this exact example yourself. The input table, the query, and its live output render at the bottom of this reading.

For the audio category (two products tied at 500), ranking by revenue DESC produces the classic contrast. ROW_NUMBER adds , product as a tiebreaker so its unique numbering is deterministic; RANK and DENSE_RANK order by revenue alone, so the tied rows stay peers and share a rank:

Read the tie row carefully. This is the exam question:

• ROW_NUMBER -> 1, 2, 3, 4. Always unique. With a tiebreaker it is deterministic; without one it breaks ties arbitrarily. Use it when you must pick exactly one row ("the latest record per customer").
• RANK -> 1, 1, 3, 4. Ties share a rank, then it skips (no rank 2). Use it for "Olympic" standings where a shared gold means no silver.
• DENSE_RANK -> 1, 1, 2, 3. Ties share a rank, then it does not skip. Use it for "distinct tiers" ("what is the 2nd-highest distinct revenue?").

PARTITION BY: slice, do not collapse

PARTITION BY is what makes a window "per group," and it is the clause learners most often underuse. Put it next to GROUP BY on the same six-row product_revenue table and the difference is the whole reason window functions exist:

GROUP BY folds each group down to one summary row. PARTITION BY slices the rows into groups, ranks inside each slice, and hands every original row back:

PARTITION BY category slices the rows, then ranks inside each slice:

  audio
    Earbuds      500   ->  rn 1     the counter starts at 1
    Headphones   500   ->  rn 2
    Speaker      300   ->  rn 3
    Cable        100   ->  rn 4
  video
    Monitor      900   ->  rn 1     the counter RESTARTS at 1
    Webcam       400   ->  rn 2

PARTITION BY is optional. Omit it and the whole result set is one window, so the same data ranks 1 through 6 straight through instead of restarting per category. Here it is both ways at once, which shows the partition is the only thing that re-scopes the counter:

Webcam is number 4 globally but number 2 inside video. Only the PARTITION BY changed.

Anatomy of the OVER clause

ROW_NUMBER() OVER ( PARTITION BY category   ORDER BY revenue DESC )
                    └── reset per group ──┘  └── order within window ──┘

• PARTITION BY slices the data into independent groups; the ranking restarts at 1 for each. Omit it and the whole result set is one window.
• ORDER BY inside OVER decides what "first" means, and it is separate from the query's outer ORDER BY, which only controls display order.

The two ORDER BYs really are independent. Here the window is ordered by revenue but the rows print by product name, so the numbering looks scrambled until you read what it means:

SELECT product, revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC, product) AS rn  -- window order: revenue
FROM product_revenue
ORDER BY product;                                            -- display order: name

rn is assigned by revenue; the rows just happen to print alphabetically. Change the outer ORDER BY and rn does not move, only the row order on screen does.

Pick one row per key (the pattern you'll reuse all level)

Because ROW_NUMBER is unique, "keep the latest row per customer" is a wrapped subquery:

SELECT * FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_dump c
) ranked
WHERE rn = 1;

You cannot filter on a window function in the same WHERE (it is computed after WHERE), so you wrap it in a subquery and filter the outer query. You will formalize this as deduplication later in this level.

Common pitfalls

• Filtering a window in WHERE: WHERE ROW_NUMBER() OVER (...) = 1 is a syntax error. Window functions are evaluated after WHERE / GROUP BY / HAVING. Wrap and filter outside.
• Nondeterministic ROW_NUMBER: if your ORDER BY has ties, ROW_NUMBER picks a winner arbitrarily and the choice can change between runs. Add a tiebreaker column (e.g. ORDER BY updated_at DESC, id DESC) so the result is deterministic. Graders and idempotency checks depend on this.
• Readability: name the ranked subquery (ranked, numbered) and lift the window into a CTE when the query grows.

In the warehouse: Snowflake and BigQuery let you skip the subquery with QUALIFY ROW_NUMBER() OVER (...) = 1. SQLite and Postgres have no QUALIFY, so you must wrap in a subquery/CTE. The ROW_NUMBER / RANK / DENSE_RANK semantics are identical everywhere.

One boundary, three effects

PARTITION BY does not just reset a rank counter. It resets the entire window universe, and the same boundary returns in every window feature this level:

GROUP BY and PARTITION BY are not rivals either; they compose. The apply exercise below aggregates first (a GROUP BY per product) and then ranks over that result (a PARTITION BY), a two-step you will reuse constantly.

Two shortcuts worth knowing

• NTILE(n) splits a partition into n as-equal-as-possible buckets, ordered by the window's ORDER BY. NTILE(4) OVER (PARTITION BY category ORDER BY revenue DESC, product) cuts each category into revenue quartiles, bucket 1 being the top quarter. When the rows do not divide evenly the earlier buckets take the extra row. This is the tool for "top 25% of customers by spend."
• A named WINDOW clause removes repetition when several functions share one spec. Define it once, reference it by name:

SELECT category, product, revenue,
  ROW_NUMBER() OVER w AS rn,
  NTILE(4)     OVER w AS quartile
FROM product_revenue
WINDOW w AS (PARTITION BY category ORDER BY revenue DESC, product);

Recap: ROW_NUMBER = unique 1,2,3 (pick one); RANK = 1,1,3 (ties skip); DENSE_RANK = 1,1,2 (ties don't skip). All keep every row, all reset per PARTITION BY.

Execution mode: you write a multi-statement script. It runs against a fresh in-memory SQLite DB, then hidden assertion queries check the ranks, tie handling, and row counts. Lead your load with DELETE FROM <target>; so a re-run doesn't double the rows.