Window Function Use Cases

You now know all three families. This topic collects the patterns that come up again and again in real projects and interviews — each one is a small recipe you can reuse.

Definition

A window function pattern is a standard way of combining a window function (usually in a subquery or CTE) with an outer filter or calculation to solve a common analytical problem — such as keeping the top N rows per group, detecting duplicate rows or comparing a period with the previous one.

Almost every pattern follows the same two steps:

1. Inner query: add a window column (rank, previous value, running total…).
2. Outer query: filter or calculate on that column (WHERE rn = 1, WHERE rn > 1, this - previous…).

Pattern 1 — Top-N per group

"Each player's 2 best innings" → ROW_NUMBER partitioned by player.

SELECT *
FROM (
    SELECT id, player, runs,
        ROW_NUMBER() OVER (PARTITION BY player ORDER BY runs DESC) AS rn
    FROM innings
    WHERE runs IS NOT NULL
) t
WHERE rn <= 2;

Pattern 2 — Top-N including ties

"The top 4 scores of the season" — but 73 is shared by two innings. ROW_NUMBER would cut one 73 off arbitrarily. DENSE_RANK keeps both:

SELECT *
FROM (
    SELECT id, player, runs,
        DENSE_RANK() OVER (ORDER BY runs DESC) AS dr
    FROM innings
) t
WHERE dr <= 4;

5 rows for "top 4" — both 73s are included. Use RANK instead if "top 4" should mean "at most 4 positions, with gaps".

Pattern 3 — Bottom-N

Just flip the sort: ROW_NUMBER() OVER (ORDER BY runs ASC, id) and keep rn <= 3 → the three lowest scores: 0 (Rohit), 26 (Dhoni), 45 (Rohit, id 4).

Pattern 4 — Find and remove duplicates

Imagine the same innings was loaded twice (ids 13 and 14 are copies). Rows are duplicates when player + match_date repeat. Number each copy with ROW_NUMBER, partitioned by the columns that define a duplicate:

-- extra rows (the example below adds them with setup)
INSERT INTO innings VALUES
    (13, 'Virat Kohli', 'RCB', '2025-03-22', 82),
    (14, 'Jos Buttler', 'RR',  '2025-04-08', 107);

SELECT *
FROM (
    SELECT id, player, match_date, runs,
        ROW_NUMBER() OVER (PARTITION BY player, match_date ORDER BY id) AS rn
    FROM innings
) t
WHERE rn > 1;

rn = 1 is the original (lowest id) we keep; rn > 1 are the extra copies. To delete them:

DELETE FROM innings
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
            ROW_NUMBER() OVER (PARTITION BY player, match_date ORDER BY id) AS rn
        FROM innings
    ) t
    WHERE rn > 1
);
-- 12 rows remain

SQL Server shortcut: SQL Server can delete directly through a CTE: WITH d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY player, match_date ORDER BY id) AS rn FROM innings) DELETE FROM d WHERE rn > 1; (CTEs are covered in the next chapter.)

A plain GROUP BY player, match_date HAVING COUNT(*) > 1 tells you which combinations are duplicated, but not which row ids to delete — ROW_NUMBER does.

Pattern 5 — Month-over-month change

Aggregate per month with GROUP BY, then LAG the monthly total:

SELECT
    MONTH(match_date)                                      AS match_month,
    SUM(runs)                                              AS month_runs,
    LAG(SUM(runs)) OVER (ORDER BY MONTH(match_date))       AS prev_month_runs,
    SUM(runs) - LAG(SUM(runs)) OVER (ORDER BY MONTH(match_date)) AS change
FROM innings
GROUP BY MONTH(match_date);

April produced 77 fewer runs than March. Add player to GROUP BY and PARTITION BY player to the LAG for month-over-month per player.

Pattern 6 — Time gap between events

SELECT id, player, match_date,
    DATEDIFF(day,
        LAG(match_date) OVER (PARTITION BY player ORDER BY match_date),
        match_date) AS days_gap
FROM innings;

Every player's matches are exactly 7 days apart (the first match of each player has NULL — there is no previous match). The same pattern measures "days between orders" per customer.

Pattern 7 — Pagination

SELECT *
FROM (
    SELECT id, player, runs, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM innings
) t
WHERE rn BETWEEN 5 AND 8;   -- "page 2" with 4 rows per page

Returns ids 5–8. (SQL Server 2012+ also has ORDER BY id OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY.)

More patterns at a glance

Cheat sheet

Common mistakes

• ❌ Deleting duplicates with ROW_NUMBER partitioned by id → every row gets rn = 1 ✅ partition by the columns that define "the same record".
• ❌ "Top 3" with ROW_NUMBER when ties matter → ✅ RANK/DENSE_RANK.
• ❌ Filtering the window column in the same query's WHERE → ✅ subquery or CTE.

Interview tip

Q: Write a query to find the second-highest score per player. A: SELECT * FROM (SELECT player, runs, DENSE_RANK() OVER (PARTITION BY player ORDER BY runs DESC) AS dr FROM innings) t WHERE dr = 2; — DENSE_RANK makes sure a tie for first place does not hide the real second-highest value.