Chapter 8 · 6 levels

Window functions

Rank, compare and accumulate without collapsing rows: ROW_NUMBER, RANK, DENSE_RANK, running totals, LAG for growth, moving averages and cohort retention.

Start

Window functions are the line between “knows SQL” and “is ready for the analyst interview”. They compute across a set of related rows, like GROUP BY, but keep every row instead of collapsing them. That makes questions that used to need self-joins and correlated subqueries short and readable: top 3 per category, running totals, change versus last month, a 7-day moving average, each customer’s nth order.

This chapter covers the OVER (PARTITION BY ... ORDER BY ...) clause, the three ranking functions and how they treat ties, cumulative sums, LAG and LEAD, window frames, and finally a monthly cohort retention table, the kind of take-home task analyst candidates are given.

Levels

  1. Top 3 products in every category

    from Jessica Turner, Merchandising Manager

    window functions, row number, partition by, top n per group, cte

    Code

  2. ROW_NUMBER vs RANK vs DENSE_RANK

    from Ashley Rivera, Web Content Lead

    row number, rank, dense rank, ties, window functions

    Code

  3. Year-to-date revenue

    from Brian Mitchell, Finance Analyst

    running total, sum over, window frame, cte

    Code

  4. Month-over-month growth

    from Megan Collins, Head of Sales

    lag, lead, mom growth, window functions, cte

    Code

  5. 7-day moving average of orders

    from Kevin Brooks, Operations Manager

    moving average, window frame, rows between, avg over

    Code

  6. Boss: cohort retention

    from Catherine Hale, Chief Executive Officer

    window functions, row number, lag, sum over, cohort retention, cte

    Boss · 3 steps