A complete sales analytics project is not one clever query. It is a set of deliverables, where each one answers a single question, each one is built on a schema designed for that question, and each one is verified on its own before it is combined. This capstone assembles the whole course into the pack a sales director would be handed.
The previous lesson in this module covered "What Differs Between MySQL, PostgreSQL and SQL Server?". This lesson is the last one in the SQL Projects for Practice module.
What goes into a complete sales analytics project?
The brief is this. A furniture company wants a monthly review pack that covers the four regions, the target against the actual, who carried it, the product mix, the trend, the customer segment, and who needs the attention. Seven things to answer, and each one wants a different shape of the same data.
The schema the project rests on
There are six tables, and each one holds a single kind of fact.
The one that shapes every query is the sales table, which holds one row for each sale. Here is the shape the engine actually holds for it.
PRAGMA table_info(sales);cid name type notnull dflt_value pk
--- ----------- ------- ------- ---------- --
0 sale_id INTEGER 0 NULL 1
1 rep_id INTEGER 1 NULL 0
2 customer_id INTEGER 1 NULL 0
3 product_id INTEGER 1 NULL 0
4 sale_date TEXT 1 NULL 0
5 quantity INTEGER 1 NULL 0
6 unit_price REAL 1 NULL 0
7 discount REAL 1 0 0The three reference columns point at the reps, the customers, and the product lines. The discount column is the only one with a default of its own.
The other five tables are regions, reps, customers, product_lines, and a region_targets table that is keyed by region and month.
Three decisions carry the project. The revenue is computed and not stored. The month key is derived and not stored. And the targets sit in their own table. All three keep the data honest, because nothing that is stored can go out of date.
Deliverable one: the executive summary
The actual against the target, per region and per month, with the variance ranked worst-first.
WITH monthly AS (
SELECT r.region_id, rg.region_name, strftime('%Y-%m', s.sale_date) AS month_key,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY r.region_id, rg.region_name, month_key
),
targets AS (
SELECT region_id, month_key, target_revenue FROM region_targets
),
variance AS (
SELECT m.region_id, m.region_name, m.month_key, m.revenue, t.target_revenue,
m.revenue - t.target_revenue AS gap
FROM monthly m JOIN targets t
ON t.region_id = m.region_id AND t.month_key = m.month_key
)
SELECT region_name, month_key, ROUND(revenue, 2) AS revenue, target_revenue,
ROUND(gap, 2) AS variance,
ROUND(100.0 * revenue / target_revenue, 1) AS pct_of_target,
RANK() OVER (ORDER BY gap DESC) AS best_variance
FROM variance
ORDER BY gap DESC;region_name month_key revenue target_revenue variance pct_of_target best_variance
----------- --------- ------- -------------- -------- ------------- ------------
North 2025-05 2279.0 1600.0 679.0 142.4 1
West 2025-04 2942.25 2600.0 342.25 113.2 2
North 2025-03 3053.9 2800.0 253.9 109.1 3
South 2025-04 1701.0 1500.0 201.0 113.4 4
East 2025-05 1083.0 950.0 133.0 114.0 5
East 2025-04 1416.0 1300.0 116.0 108.9 6
West 2025-03 1262.0 1200.0 62.0 105.2 7
South 2025-03 1549.5 1500.0 49.5 103.3 8
East 2025-03 456.0 500.0 -44.0 91.2 9
South 2025-05 1428.6 1500.0 -71.4 95.2 10
West 2025-05 840.0 950.0 -110.0 88.4 11
North 2025-04 859.5 1000.0 -140.5 86.0 12The monthly CTE defines the grain once, the targets CTE isolates the comparison data, and the variance is the only place where the subtraction happens. One place means one place to check.
Deliverable two: who carried the region
A share of the region, plus a running total, so a reader can see how quickly it was covered.
WITH rep_revenue AS (
SELECT rg.region_id, rg.region_name, r.rep_id, r.rep_name,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
)
SELECT region_name, rep_name, ROUND(revenue, 2) AS revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region_id), 1) AS pct_of_region,
ROUND(SUM(revenue) OVER (PARTITION BY region_id ORDER BY revenue DESC), 2) AS running_region_total,
RANK() OVER (PARTITION BY region_id ORDER BY revenue DESC) AS rnk
FROM rep_revenue
ORDER BY region_name, rnk;region_name rep_name revenue pct_of_region running_region_total rnk
----------- ------------- ------- ------------- --------------------- ---
East Greta Vogel 2955.0 100.0 2955.0 1
North Bruno Kessler 3273.5 52.9 3273.5 1
North Ada Whitfield 2918.9 47.1 6192.4 2
South Carla Nunes 2503.5 53.5 2503.5 1
South Dev Patel 2175.6 46.5 4679.1 2
West Elif Demir 2627.25 52.1 2627.25 1
West Farid Haddad 2417.0 47.9 5044.25 2There are three windows over one grouped result: a share of the partition total, a cumulative total down the ranked list, and the rank itself.
The running total answers what the other columns cannot. In North, one rep reached 52.9 percent on their own, and a share column alone would not show how fast the rest of the region was covered.
Deliverable three: the product mix
A pivot, built with conditional aggregation.
SELECT rg.region_name,
ROUND(SUM(CASE WHEN p.category = 'furniture' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS furniture,
ROUND(SUM(CASE WHEN p.category = 'storage' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS storage,
ROUND(SUM(CASE WHEN p.category = 'accessories' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS accessories,
ROUND(SUM(CASE WHEN p.category NOT IN ('furniture','storage','accessories') THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS other
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
JOIN product_lines p ON p.product_id = s.product_id
GROUP BY rg.region_id, rg.region_name
ORDER BY rg.region_name;region_name furniture storage accessories other
----------- --------- ------- ----------- -----
East 1923.0 456.0 0.0 576.0
North 1125.0 1065.0 2228.4 1774.0
South 1126.5 1605.0 1038.6 909.0
West 1382.25 2040.0 360.0 1262.0East sells no accessories at all. It shows as 0.0 and not a blank, and that is the difference between "we sold none" and "no data". A blank cell in a report invites a question you cannot answer.
The 2228.40 that North took in accessories is the largest cell in the pack, and the long format hides it completely.
Deliverable four: the trend
The revenue per month per region, with a running total and a two-month moving average.
WITH monthly AS (
SELECT rg.region_name, strftime('%Y-%m', s.sale_date) AS month_key,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_name, month_key
)
SELECT region_name, month_key, ROUND(revenue, 2) AS revenue,
ROUND(SUM(revenue) OVER (PARTITION BY region_name ORDER BY month_key), 2) AS running_total,
ROUND(AVG(revenue) OVER (PARTITION BY region_name ORDER BY month_key
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS moving_avg
FROM monthly
ORDER BY region_name, month_key;region_name month_key revenue running_total moving_avg
----------- --------- ------- ----------- ----------
East 2025-03 456.0 456.0 456.0
East 2025-04 1416.0 1872.0 936.0
East 2025-05 1083.0 2955.0 1249.5
North 2025-03 3053.9 3053.9 3053.9
North 2025-04 859.5 3913.4 1956.7
North 2025-05 2279.0 6192.4 1569.25
South 2025-03 1549.5 1549.5 1549.5
South 2025-04 1701.0 3250.5 1625.25
South 2025-05 1428.6 4679.1 1564.8
West 2025-03 1262.0 1262.0 1262.0
West 2025-04 2942.25 4204.25 2102.13
West 2025-05 840.0 5044.25 1891.13North's moving average falls from 1956.70 to 1569.25, even though May revenue of 2279.00 beats April's 859.50. That is because the average still carries March's exceptional 3053.90. Reading one month on its own would have told you the opposite story.
Deliverable five: who needs the attention
The reps who are below their own region's average.
This one needs a separate query level, because a window function cannot be filtered in the statement that computes it.
WITH rep_revenue AS (
SELECT rg.region_id, rg.region_name, r.rep_id, r.rep_name,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
),
region_avg AS (
SELECT region_id, AVG(revenue) AS avg_revenue FROM rep_revenue GROUP BY region_id
)
SELECT rr.region_name, rr.rep_name, ROUND(rr.revenue, 2) AS revenue,
ROUND(ra.avg_revenue, 2) AS region_average,
ROUND(rr.revenue - ra.avg_revenue, 2) AS gap_to_average
FROM rep_revenue rr
JOIN region_avg ra ON ra.region_id = rr.region_id
WHERE rr.revenue < ra.avg_revenue
ORDER BY gap_to_average;region_name rep_name revenue region_average gap_to_average
----------- ------------- ------- --------------- ---------------
North Ada Whitfield 2918.9 3096.2 -177.3
South Dev Patel 2175.6 2339.55 -163.95
West Farid Haddad 2417.0 2522.13 -105.13There is one rep per region, and each one of them is below their own region average.
Written as a window function at the same query level, this would be wrong. The average would be computed over the already-filtered rows, so the comparison would be made against the wrong number. The fix is to compute the window one level up.
Deliverable six: the customer segment
The last cut asks a different question. It is not who sold, but to whom.
SELECT c.segment, COUNT(DISTINCT c.customer_id) AS customers,
COUNT(s.sale_id) AS orders,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue,
ROUND(AVG(s.quantity * s.unit_price * (1 - s.discount)), 2) AS avg_order_value
FROM sales s JOIN customers c ON c.customer_id = s.customer_id
GROUP BY c.segment ORDER BY revenue DESC;segment customers orders revenue avg_order_value
------------- --------- ------ ------- ---------------
business 3 12 6619.75 551.65
consumer 3 11 6570.0 597.27
institutional 2 9 5681.0 631.22Business and consumer are level on revenue, but the averages tell different stories. The institutional customers buy the least often, and they buy the most when they do.
That is a conclusion a revenue column on its own would hide.
What made this project work?
- One schema, designed for the questions. Every deliverable is a different shape of the same normalised data.
- One grain per query, stated explicitly. Region and month for the summary, rep and region for the leaderboard, and category for the mix.
- Named steps in named CTEs. Every intermediate result can be run alone, so a wrong number is traceable to one cause.
- Each technique where it belongs. The windows for the comparisons, and the aggregates for the totals.
- Derived fields, never the stored ones. The revenue and the variance cannot go stale.
- Verification at every step. The row counts are checked and the totals are reconciled.
Each deliverable answers one question, and that question is written down before the SQL is.
The pack is not six queries. It is six answers.
Quick reference
- You design the schema for the questions and not for the report.
- You state the grain of every query, and you aggregate at exactly that grain.
- You compute the windows in a separate level from any filter that depends on them.
- You use the CTEs to name each step, so a wrong number has one place to be wrong.
- You verify each deliverable before you assemble the pack.
Capstone drill: you extend the pack with a seventh deliverable, which is the top three products per region, using a window function over the grouped product revenue. Then you state in one sentence which ranking function you would use, and why the answer must not contain gaps.