Multi-step queries and calculations across rows: CTEs and window functions

WITH: a derived table with a name

The last lesson ended with a derived table nested inside FROM. Here is the same query written as a common table expression.

WITH category_stats AS (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
SELECT *
FROM category_stats
WHERE products >= 10
ORDER BY average DESC;

The result is the same, but the query reads in order: define category_stats, then use it. The name exists only for this statement; no table is created.

Several steps in a row

CTEs can be chained, and each CTE can use the ones before it. This turns a nested query into a sequence of named steps.

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
),
per_customer AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         ROUND(SUM(total), 2) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.country,
       pc.orders,
       pc.revenue
FROM per_customer pc
JOIN customers c ON c.id = pc.customer_id
ORDER BY pc.revenue DESC
LIMIT 20;

This query has three steps: narrow the orders, summarize them by customer, then attach each customer's name. As nested subqueries it would be read inside out; as CTEs, it follows the processing order.

Window functions: aggregate without collapsing

GROUP BY reduces many rows to one row per group. A window function computes across a set of rows while keeping every row.

SELECT p.name,
       p.price,
       RANK()       OVER (ORDER BY p.price DESC) AS price_rank,
       DENSE_RANK() OVER (ORDER BY p.price DESC) AS dense_price_rank,
       ROW_NUMBER() OVER (ORDER BY p.price DESC) AS row_no
FROM products p
ORDER BY p.price DESC
LIMIT 20;

OVER (ORDER BY p.price DESC) defines the rows and order used for the calculation. The three functions differ when prices tie:

• ROW_NUMBER() — 1, 2, 3, 4. Always distinct, ties broken arbitrarily.
• RANK() — 1, 2, 2, 4. Ties share a rank and the next value skips.
• DENSE_RANK() — 1, 2, 2, 3. Ties share a rank and nothing is skipped.

Choose the function based on the result you need. For exactly ten rows, use ROW_NUMBER; for ranked positions that include ties, use a rank function.

Top N per group

Without window functions, this would require a correlated subquery that counts more expensive products in each category. PARTITION BY restarts the numbering for every category.

WITH ranked AS (
  SELECT cat.name AS category,
         p.name AS product,
         p.price,
         ROW_NUMBER() OVER (PARTITION BY cat.id ORDER BY p.price DESC) AS rank_in_category
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Running totals

With ORDER BY in the window, each row can include all preceding rows and itself in the calculation.

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

The CTE produces one row per month, and SUM(revenue) OVER (ORDER BY month) accumulates values down that list. The same pattern works for running counts, cumulative percentages, and other calculations that depend on preceding rows.

Comparing to the previous row

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

LAG(revenue) returns the previous row in the window; LEAD returns the next. This makes month-over-month change a single expression instead of a self-join. The first row has no previous month, so previous_month and the resulting change are NULL.

Support

Window functions are widely available: PostgreSQL, SQL Server, and Oracle have supported them for years; SQLite since 3.25 (2018), MySQL since 8.0 (2018), and MariaDB since 10.2. MySQL 5.7 is a common version without them.

What you can do now

You can now express many complex read queries. The remaining lessons cover changing data safely and investigating slow queries.