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.