Foundation project

Project: a customer value report

Lesson 12 of 20 · 20 min · Module 3: Window functions in depth

Build the report a sales team asks for first: who are the best customers, how often do they buy, and since when. It combines joins, conditional filtering, aggregates, dates, and ranking.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Break a report into named stages
Aggregate per order, then per customer
Rank results with DENSE_RANK

The idea

Count only orders that are paid or shipped. Calculate per-order totals once, aggregate them per customer, and rank customers with DENSE_RANK() so equal spending shares a rank.

SELECT name, price_cents,
       DENSE_RANK() OVER (ORDER BY price_cents DESC) AS price_rank
FROM products
LIMIT 4;

01One CTE per question

order_totals answers "what was each order worth?" and per_customer answers "what did each customer do?". Each step is small enough to test on its own.

02MIN and MAX work on dates

ISO dates compare correctly as text, so MIN(ordered_at) is the first order and MAX(ordered_at) the latest.

03DENSE_RANK for fair ties

DENSE_RANK gives equal values the same rank and continues with the next number (1, 2, 2, 3). RANK would skip to 4, and ROW_NUMBER would invent an order between equal customers.

SELECT v, DENSE_RANK() OVER (ORDER BY v DESC) FROM (SELECT 5 v UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 1);

Output

Your project brief

For every customer with at least one paid or shipped order, return rank (by total spent, highest first, with DENSE_RANK), name, country, orders, spent_cents, first_order and last_order (dates of their first and latest such order). Sort by rank, then name.

What the checks look for

Ranks the six paying customers by what they spent
Leaves the source tables unchanged

Try it in the editor →

Take this with you

Big reports are small, named steps combined.

Go a little further

Add an average order value column and a column that labels customers as "VIP" when they spent more than 10 000 cents.