Project 1 — E-commerce
Orders, revenue, customers, products. Go.
That's it? No “here's today's concept”? No cold open with a single tidy question?
You've had nine chapters of tidy questions. This is what the job actually looks like — leadership wants a real report on NOVA's e-commerce business, and nobody's going to hand you the exact query to run. You decide what to look at and how to justify it.
NOVA’s leadership meeting is Friday. They want three things, in whatever shape makes sense once you’ve looked at the data:
- Who NOVA’s most valuable customers actually are.
- What’s really selling, and whether that matches intuition.
- Where revenue is heading, quarter to quarter.
No hints about which clauses to use. You have four tables: customers, orders, and products are the ones that matter here. Figure out the joins yourself.
Harry’s first instinct: sum up amount per customer across every row in orders.
Wait — do refunded and cancelled orders count toward a customer's value? Diego's only order got cancelled. If I count it, he looks like he's spent money he never actually paid.
Good catch, and it's exactly the kind of decision nobody's going to make for you in the real job. “Lifetime value” should mean money that actually landed — completed orders only.
Which rows represent money that actually stuck?
SELECT c.name, c.country, COUNT(*) AS orders, SUM(o.amount) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id
ORDER BY lifetime_value DESC
LIMIT 5;| name | country | orders | lifetime_value |
|---|---|---|---|
| Bob Turner | USA | 2 | 229.98 |
| Emma Wilson | UK | 4 | 223.46 |
| Alice Sharma | India | 3 | 218.97 |
| George Lee | USA | 1 | 199.99 |
| Julia Souza | Brazil | 3 | 177.99 |
George Lee cracks the top five off a single order — one $199.99 smartwatch. Emma got there with four smaller orders. Same rank, completely different customer.
The regional leads don’t want one global top-5 — they each want to know who their own best customer is. For every country, find the single customer with the highest lifetime value (completed orders only). Return name, country, and lifetime_value, one row per country, ordered by lifetime value descending.
Hint 1
You still need the same lifetime-value calculation per customer first — build that as a CTE before anything else.
Hint 2
“The best customer per country” is the top-N-per-group shape from Chapter 9. A window function that ranks within a partition is exactly what that shape calls for.
Hint 3
PARTITION BY country ORDER BY lifetime_value DESC inside RANK() (or ROW_NUMBER()), then filter the outer query down to rank 1.
Solution
Running…
Next question: which product category is NOVA’s best-seller? Harry starts with the obvious metric — order count.
SELECT p.category, COUNT(*) AS orders
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY orders DESC;| category | orders |
|---|---|
| Audio | 5 |
| Computing | 4 |
| Accessories | 4 |
| Wearables | 3 |
| Home | 2 |
Audio wins on orders. Harry’s about to write that down when he stops himself — orders isn’t the question leadership actually asked. They asked what’s selling, and in a business context that almost always means money, not row count.
SELECT p.category, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;| category | orders | revenue |
|---|---|---|
| Wearables | 3 | 429.97 |
| Audio | 5 | 339.95 |
| Computing | 4 | 276.99 |
| Accessories | 4 | 183.94 |
| Home | 2 | 69 |
Same data, opposite ranking. Wearables has the fewest orders of any category that sells at all, but a single $199.99 smartwatch carries it to the top on revenue. Whichever number you lead with changes the story — that’s worth flagging in the report, not just picking one and moving on.
Try it: What if you group by category without joining to orders' status at all — include every order, not just completed ones?
Run your query to see results here.
Wearables still tops the list, but its revenue jumps — it’s now quietly counting a $199.99 order that was actually pending, money NOVA hasn’t collected yet. Every aggregate query has an implicit question baked into its WHERE clause: which rows am I willing to call real? Forgetting to filter isn’t neutral — it’s a decision, just an accidental one.
Leadership’s actual follow-up question: “what share of our revenue is each category?” For completed orders, return category, orders, revenue, and pct_of_total — that category’s revenue as a percentage of all completed revenue, rounded to one decimal place. Sort by revenue, highest first.
Hint 1
You need one number that represents total revenue across every category, so every row can divide into it — that's a job for a subquery.
Hint 2
A subquery in the SELECT list runs once per outer row, but a constant like a grand total only needs to be computed once. Either way, get it into an expression you can divide by.
Hint 3
SUM(o.amount) / (SELECT SUM(amount) FROM orders WHERE status = 'completed') * 100, rounded — the subquery isn't correlated to the outer query, so every group divides by the same grand total.
Solution
Running…
Last piece: is NOVA’s revenue actually growing? First, the healthiest sign of a business — how many orders actually go through versus stall, get cancelled, or come back as refunds.
Table: customers
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| country | TEXT |
| TEXT | |
| phone | TEXT |
| signup_date | DATE |
id is primary key. phone is nullable.
Table: products
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | DECIMAL(10,2) |
id is primary key.
Table: orders
| Column Name | Type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| amount | DECIMAL(10,2) |
| status | TEXT |
| placed_at | DATE |
id is primary key. customer_id is foreign key -> customers.id. product_id is foreign key -> products.id. amount is quantity * unit price at time of order. status is completed, pending, refunded, or cancelled.
Running…
82% of orders complete cleanly. Not bad — worth noting in the report, then moving on to the trend itself. NOVA doesn’t have years of history, so quarters are the right resolution: enough orders per bucket to mean something, not so few that one big order swings the whole number.
WITH quarterly AS (
SELECT
CASE
WHEN placed_at < '2023-01-01' THEN '2022 Q4'
WHEN placed_at < '2023-04-01' THEN '2023 Q1'
WHEN placed_at < '2023-07-01' THEN '2023 Q2'
ELSE '2023 Q3'
END AS quarter,
amount
FROM orders
WHERE status = 'completed'
)
SELECT quarter,
SUM(amount) AS revenue,
SUM(SUM(amount)) OVER (ORDER BY quarter) AS running_total
FROM quarterly
GROUP BY quarter
ORDER BY quarter;| quarter | revenue | running_total |
|---|---|---|
| 2022 Q4 | 199.99 | 199.99 |
| 2023 Q1 | 439.44 | 639.43 |
| 2023 Q2 | 258.45 | 897.88 |
| 2023 Q3 | 401.97 | 1299.85 |
That’s a window aggregate stacked on top of a GROUP BY — SUM(SUM(amount)) OVER (ORDER BY quarter). The inner SUM collapses each quarter to one row; the outer one runs across those already-collapsed rows without collapsing them further. And notice Q2 dips before Q3 recovers — hold onto that. It’s going to matter later.
Add one more column to the quarterly table: each quarter’s share of the running total so far — how much of the revenue booked up to and including that quarter came from that quarter alone. Return quarter, revenue, running_total, and pct_of_running_total.
Hint 1
You already have both numbers you need — revenue and running_total — sitting in the same result. This is a division, not a new join.
Hint 2
You can reuse the running-total expression as-is and just divide the quarter's own revenue by it in the same SELECT.
Hint 3
revenue / running_total * 100, rounded — both sides are already window/aggregate expressions from the CTE, so no new grouping or joins are needed.
Solution
Running…
Nobody told Harry which clause to reach for this time — he had to decide what “value” and “selling” even meant before writing a single query. That’s most of what real analytics work actually is: translating a vague ask into a precise one, then writing SQL that answers the precise version honestly.
A project isn’t one query — it’s a sequence of decisions (which statuses count, which metric answers the actual question, which grain tells the truth without drowning in noise) each backed by a query that proves it. Get the decisions right and the SQL is usually the easy part.