Lesson 12 of 12 · 15 min

CTEs and window functions

Common table expressions (WITH)

Long queries are hard to read. A CTE gives a sub-result a name, so you can build a query in clear steps:

WITH order_lines AS (
  SELECT o.customer_id, o.quantity * p.price AS amount
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
)
SELECT customer_id, SUM(amount) AS spent
FROM order_lines
GROUP BY customer_id;

order_lines behaves like a temporary table that exists only for this query. You can define several, separated by commas, and each can use the ones before it.

Window functions

GROUP BY squashes rows into one per group. A window function calculates across a set of rows but keeps every row. You write it with OVER (...):

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

Useful window functions:

Function Gives
ROW_NUMBER() 1, 2, 3... with no ties
RANK() position, with ties sharing a rank (1, 2, 2, 4)
SUM(x) OVER (ORDER BY ...) a running total
LAG(x) / LEAD(x) the previous / next row's value

PARTITION BY restarts the calculation for each group, for example a ranking within each category:

SELECT category, name, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

Your task

Rank customers by how much they have spent. Use a CTE named totals with one row per customer (customer name, total spent rounded to 2 decimals), then select customer, total and their position using RANK() OVER (ORDER BY total DESC). Order by position.

query.sql
-- Rank customers by spending, using WITH and RANK() OVER

SQLite runs right here in your browser. The first run downloads about 0.7 MB; after that it is instant.

Discussion

Stuck? Ask here. Be kind, and share hints rather than full answers.

Loading the discussion…