Advanced
Case 43 of 50
Case 043: The Customer Scorecard
customersorderspayments
The manager asks Alex to build a single report leadership will look at every week.
"Could you put together one row per customer — order count, total spend, and their average order value, all in one place?"
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Build one report showing each customer's order count, total spending, and average order value.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH order_counts AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
),
spend AS (
SELECT orders.customer_id, SUM(payments.amount) AS total_spent
FROM orders
JOIN payments ON payments.order_id = orders.id
GROUP BY orders.customer_id
)
SELECT
customers.name,
order_counts.order_count,
spend.total_spent,
ROUND(spend.total_spent * 1.0 / order_counts.order_count, 1) AS avg_order_value
FROM customers
JOIN order_counts ON order_counts.customer_id = customers.id
JOIN spend ON spend.customer_id = customers.id
ORDER BY spend.total_spent DESC;
What that query returns
Case Debrief
Multi-CTE report
- Multiple independent CTEs, each answering one sub-question, joined together at the end — this is how most real dashboard queries are structured.