Skip to content

Window Functions and CTEs

Medium ~40 min

What you'll build

A set of analytical SQL queries using window functions for ranking, running totals, and lag/lead comparisons, plus CTEs for readable multi-step queries.

Goals

  • Use ROW_NUMBER, RANK, and DENSE_RANK
  • Compute running totals with window frames
  • Compare rows with LAG and LEAD
  • Build readable queries with CTEs

Prerequisites

  • GROUP BY and aggregates
  • JOIN basics
  • ORDER BY

Steps

  1. 1

    Set up sales data

    Create a sales table with monthly data for ranking and trend analysis.

    Code
    CREATE TABLE sales (
      id       SERIAL PRIMARY KEY,
      region   TEXT NOT NULL,
      month    TEXT NOT NULL,
      amount   INTEGER NOT NULL
    );
    
    INSERT INTO sales (region, month, amount) VALUES
      ('North', '2024-01', 10000), ('North', '2024-02', 12000), ('North', '2024-03', 9000),
      ('South', '2024-01', 8000),  ('South', '2024-02', 15000), ('South', '2024-03', 11000),
      ('East',  '2024-01', 7000),  ('East',  '2024-02', 9000),  ('East',  '2024-03', 13000);

    Expected result

    Nine rows of sales data across three regions and three months.

  2. 2

    Rank with ROW_NUMBER and RANK

    Use ROW_NUMBER() and RANK() with PARTITION BY to rank months within each region by sales amount.

    Code
    SELECT region, month, amount,
      ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
      RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
    FROM sales
    ORDER BY region, rn;

    Expected result

    Each region's months are numbered 1-3 by descending amount; ties share rank.

  3. 3

    Running total with window frame

    Compute a cumulative running total of sales per region over time using a window frame clause.

    Code
    SELECT region, month, amount,
      SUM(amount) OVER (
        PARTITION BY region
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS running_total
    FROM sales
    ORDER BY region, month;

    Expected result

    running_total accumulates month by month within each region.

  4. 4

    Month-over-month with LAG

    Use LAG() to compare each month's sales to the previous month, computing the percentage change.

    Code
    SELECT region, month, amount,
      LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month,
      ROUND(
        (amount - LAG(amount) OVER (PARTITION BY region ORDER BY month)) * 100.0
        / LAG(amount) OVER (PARTITION BY region ORDER BY month),
        1
      ) AS pct_change
    FROM sales
    ORDER BY region, month;

    Expected result

    Each row shows the previous month's amount and the percent change.

  5. 5

    CTE for top performer per region

    Use a CTE (WITH clause) to first rank rows, then filter to the top performer per region. CTEs make complex queries readable.

    Code
    WITH ranked AS (
      SELECT region, month, amount,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
      FROM sales
    )
    SELECT region, month AS best_month, amount AS best_amount
    FROM ranked
    WHERE rn = 1
    ORDER BY region;

    Expected result

    Returns the single best month for each region.

Try it yourself

What you learned

You mastered window functions and CTEs — the tools for analytical SQL. Window functions compute across rows without collapsing them, and CTEs make complex queries readable and maintainable.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic