Window Functions — ROW_NUMBER, RANK, LEAD and LAG

Window functions perform calculations across a set of rows related to the current row without collapsing them into groups like GROUP BY does. ROW_NUMBER() assigns a unique sequential number to each row. RANK() and DENSE_RANK() handle tied positions differently. LAG() and LEAD() access adjacent rows without a self-join. These functions are the clean solution to many “top N per group” and “compare to previous” reporting queries that are awkward with GROUP BY alone.

Window Functions #

-- ── ROW_NUMBER — unique sequential number per partition ────────────────────
SELECT
    p.Id, p.Title, p.CategoryId, p.ViewCount,
    ROW_NUMBER() OVER (
        PARTITION BY p.CategoryId   -- restart numbering per category
        ORDER BY p.ViewCount DESC   -- highest views = 1
    ) AS RankInCategory
FROM dbo.Posts p
WHERE p.IsPublished = 1;

-- Get only the TOP 3 posts per category:
SELECT Id, Title, CategoryId, ViewCount, RankInCategory
FROM (
    SELECT
        p.Id, p.Title, p.CategoryId, p.ViewCount,
        ROW_NUMBER() OVER (
            PARTITION BY p.CategoryId ORDER BY p.ViewCount DESC
        ) AS RankInCategory
    FROM dbo.Posts p WHERE p.IsPublished = 1
) ranked
WHERE RankInCategory <= 3;

-- ── RANK vs DENSE_RANK — tied positions ───────────────────────────────────
-- ViewCount: 1000, 1000, 800, 600
-- ROW_NUMBER:  1,    2,   3,   4   -- unique, no ties
-- RANK:        1,    1,   3,   4   -- ties get same rank, next rank skips
-- DENSE_RANK:  1,    1,   2,   3   -- ties get same rank, next rank consecutive

SELECT
    p.Title, p.ViewCount,
    ROW_NUMBER()  OVER (ORDER BY p.ViewCount DESC) AS RowNum,
    RANK()        OVER (ORDER BY p.ViewCount DESC) AS Rank,
    DENSE_RANK()  OVER (ORDER BY p.ViewCount DESC) AS DenseRank
FROM dbo.Posts p WHERE p.IsPublished = 1;

-- ── LAG and LEAD — compare to adjacent rows ───────────────────────────────
SELECT
    p.Id,
    p.Title,
    p.ViewCount,
    p.PublishedAt,
    LAG(p.ViewCount,  1, 0) OVER (ORDER BY p.PublishedAt) AS PrevPostViews,
    LEAD(p.ViewCount, 1, 0) OVER (ORDER BY p.PublishedAt) AS NextPostViews,
    p.ViewCount - LAG(p.ViewCount, 1, 0) OVER (ORDER BY p.PublishedAt) AS ViewsDiff
FROM dbo.Posts p
WHERE p.IsPublished = 1
ORDER BY p.PublishedAt;

-- ── Running totals — SUM OVER with frame ──────────────────────────────────
SELECT
    CAST(p.PublishedAt AS DATE) AS PublishedDate,
    COUNT(*) AS PostsOnDate,
    SUM(COUNT(*)) OVER (
        ORDER BY CAST(p.PublishedAt AS DATE)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM   dbo.Posts p
WHERE  p.IsPublished = 1
GROUP  BY CAST(p.PublishedAt AS DATE)
ORDER  BY PublishedDate;

-- ── NTILE — divide into N equal buckets ──────────────────────────────────
SELECT
    p.Title, p.ViewCount,
    NTILE(4) OVER (ORDER BY p.ViewCount DESC) AS Quartile
    -- 1=top 25%, 2=next 25%, 3=next 25%, 4=bottom 25%
FROM dbo.Posts p WHERE p.IsPublished = 1;

Common Mistakes #

Mistake 1 — Using window function in WHERE clause (syntax error) #

❌ Wrong — WHERE ROW_NUMBER() OVER(...) = 1; window functions not allowed in WHERE.

✅ Correct — wrap in CTE or subquery, then filter: WITH r AS (SELECT ..., ROW_NUMBER() OVER(...) rn ...) SELECT * FROM r WHERE rn = 1.

Mistake 2 — RANK() when unique sequential numbers needed (gaps in numbering) #

❌ Wrong — RANK() produces 1, 1, 3, 4 for tied values; downstream code expecting 1, 2, 3, 4 breaks.

✅ Correct — ROW_NUMBER() for unique sequential numbers regardless of ties.