There's a very widespread and very false idea: that SQL is for pulling the data out and that the real analysis happens somewhere else. In practice, most of a company's analytical work —the month's metrics, the time series, the breakdown by country, the customer Pareto, the cohorts— fits entirely in SQL, runs where the data lives and doesn't need a single file moved. This lesson turns 10-03's window functions into complete reports. But before the first query there's something more important, and it's why an analyst with judgement is worth far more than a fast one: half of analysis errors aren't about SQL, they're about definitions. Does "sales" include shipping? What about the cancelled order? Is an "active customer" one who bought at some point or one who bought this year? Every one of those questions changes the figure, and the engine answers none of them.

1. The analyst's workflow
2. GreenStore's definitions
3. Fundamental metrics
4. Time analysis
5. Segmentation
6. ABC / Pareto analysis
7. Cohorts and retention
8. Presentation: pivoting with CASE and with crosstab
9. Reproducibility and where SQL fits
10. Frequent analysis errors
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

The two steps everybody skips are the second and the fourth, and they're the ones that separate a correct number from a plausible one. Defining forces you to talk to whoever asked the question —and very often you discover the question was a different one. Validating means checking the result against something you already knew: a total, a count, last year's figure. If a breakdown by country doesn't add up to the same as the overall total, the breakdown is wrong, however elegant the query.

These are the definitions the course uses. They aren't "the right ones": they're the ones we've chosen, and what matters is that they're written down.

And the three decisions behind them:

• Shipping isn't sales. It's a service passed through, not commercial margin. If you included it, revenue would be €846.20 and the average order value €42.31: figures just as "true" that answer a different question. The serious mistake isn't choosing wrong, it's mixing the two in the same report.
• The cancelled order (number 6) counts in gross revenue and not in net. Its €26.75 really were ordered and were fully refunded: a demand report should include it, an income one shouldn't. And "all statuses" means orders not yet delivered (2 paid, 1 pending, 2 shipped) count; for income collected you'd have to filter by status and the number would be different.

The first seven, in one query with FILTER (04-04):

The immediate validation: 20 orders and 113 units are the figures the course has been carrying since 01-06, and 727.95 − 26.75 = 701.20 tallies with the cancelled order's refund. If any of the three didn't come out, you'd have to stop.

The two rates say different things and you have to publish which one it is: 15 % of orders had some return, but only 11 % of the money came back, because two of the three are partial —from order 10 the cream (€34.02) of €48.27 was returned, and from order 13 the bags (€19.80) of €30.30. Only order 6, cancelled, was returned in full.

Two active products that have never been sold, with opposite business readings: the candles have stock 0 —perhaps they were never actually available— and the deodorant has 75 units waiting. The third with no sales, the spirulina capsules, doesn't show up because it's discontinued: that WHERE p.active is a definitional decision, not a detail.

(4 of 12 rows; the total is 12 new and 8 repeat.) The reading is the one any young business would expect: until November almost every order is from a new customer, and from December on they're all from returning customers. That's one good signal —there is retention— and one worrying one: acquisition has stopped. Neither is visible from total revenue alone.

The three columns every dashboard asks for, over 10-03's monthly series:

(5 of 12 rows; the full series is in 10-03.) Three things a report should say that the table alone doesn't:

• The running total closes at €727.95 and passes through €603.52 in December: those are the two canonical figures, and their matching validates the whole series.
• The 3-month moving average is what you should show on the chart, not the raw series: actual revenue swings between €31.70 and €97.20 and the moving average between €41.88 and €71.87. With small volumes, the raw series is mostly noise.
• October's +196.70 % isn't news: it's that September had a single order, and publishing that change without the base it's computed on is misleading in good faith. Rule: don't publish a percentage change if the denominator is small; publish the absolute figure and the number of orders alongside it.

The question "are we selling more than this time last year?" is the most frequent one in the world, and in GreenStore it has no answer: the series starts in March 2025 and ends in February 2026, so January and February 2026 have nothing to compare against. The right thing is to say so, not to compute a NULL and let somebody interpret it. And what you can do, with the same honesty: compare the two partial years, making it clear they aren't comparable in length.

2026's €124.43 is not "a 79 % drop": it's two months against ten. What's comparable is the monthly average —€60.35 in 2025 against €62.22 in 2026— or the same calendar months, which here don't exist.

One and the same total, cut four different ways. The pattern is always the same GROUP BY, and what matters is that all four breakdowns add up to €727.95:

Supplements doesn't appear, and that's a result too: its only product is discontinued. An honest report says so; one that shows five rows lets people believe there are five categories. The other three cuts:

Three readings no overall total gave you. Spain contributes 60 % of revenue but has the lowest average order value (€30.98 against Portugal's €52.16): many small orders against a few big ones, which completely changes the shipping strategy. Card concentrates 55 % of revenue in 11 of the 20 orders. And the two channels are level in number, with phone slightly ahead on amount (€378.83 against €349.12): the attended channel billing more per order is what would justify having sales reps, but it's a testable hypothesis, not a conclusion.

The Pareto principle —"a few items account for most of the total"— is computed with a window running total (10-03) and classified with a CASE:

(5 of the 7 class-A rows —missing are Julien Moreau, 4th with €66.90, and Javier Ortega Ruiz, 5th with €62.93. Then come Ana with €54.85, Tiago with €44.60 and Diego with €31.70 in class B, and Elena with €30.30 and Marta with €29.53 in class C.) The summary by class, for customers and for products:

GreenStore's Pareto is a gentle one: the top 6 customers account for 65.89 % and it takes 7 to reach 73.76 %; in the classic 80/20 it would take 2 or 3 out of 12. That's a business fact: the shop doesn't depend on one big customer, which lowers the risk and at the same time suggests there are no key accounts to cultivate. And a methodological warning: the 80/95 cut-off is a convention, and you have to write down which one you use —including or excluding the row that crosses the threshold— because it changes who lands in each group.

A cohort groups customers by the moment they came in and follows them over time. On GreenStore, using the month of signup_date:

The query is correct and the analysis would be nonsense. The cohorts have one, two or three customers: a single customer who doesn't buy turns the 2025-09 cohort into a "0 % conversion" that means nothing. And the older cohorts win by definition, because they've been buying longer: comparing January's 5 orders with June's 1 is comparing ten months with eight. And that's the analysis lesson, not the SQL one. A result from samples of size 1 or 2 isn't a result: it's an anecdote in table format. What you do is (a) say so in the report, (b) group into larger cohorts —by quarter instead of by month— and (c) always compare at the same age: "orders within 90 days of signing up", which puts every cohort on equal footing and is the standard way to do retention. With 15 customers not even that would save the analysis; with 15,000, it's exactly the report management will ask for.

When a breakdown stops making sense: once some group drops below a few dozen observations, the percentage you compute will swing more than the signal you're looking for. Before splitting a total into twenty pieces, look at how many rows are left in the smallest piece.

A management report almost never wants rows: it wants a matrix, with categories down the rows and years across the columns. The portable way is 06-05's, a conditional aggregate per column (SUM(CASE …), or its modern form with FILTER):

The columns add up to €603.52 and €124.43: 2025's and 2026's canonical figures. And Sustainable home comes out at €0.00 in 2026 thanks to the COALESCE, which is informative: it stopped selling.

This is where 06-05's promise gets kept. PostgreSQL ships the tablefunc extension, with a crosstab() function that pivots from a three-column query: row, column and value.

The same figures, with two differences that matter. The order is alphabetical by the row column, because crosstab requires ORDER BY 1, 2 in the source query and accepts no other. And Sustainable home comes out NULL in 2026, not 0.00, because to crosstab "there is no row" and "there's a row with a zero" are different things — and in a chart, NULL is a hole. You fix it by wrapping the outside in COALESCE(y2026, 0).

The criterion: for two, three or four columns, SUM(CASE …) wins on clarity and portability; crosstab pays off from eight or ten fixed, known columns on. And for genuinely dynamic columns —"one per month, however many there are"— neither works: SQL returns a fixed number of columns decided at planning time. That pivot belongs in the presentation layer (BI, spreadsheet, pandas.pivot_table), and that's the natural division of labour.

An analysis you can't repeat three months from now isn't an analysis, it's a screenshot:

• The queries live in the repository, in .sql files with a comment saying which question they answer and which definition they use ("sales = product, excluding shipping, all statuses"). A dashboard with the SQL hidden inside the tool is a dashboard nobody can audit.
• Views and materialized views as a semantic layer (10-01): v_sales_detail defines once what a line's amount is, and from then on nobody writes quantity * unit_price * (1 - discount) again — which is exactly where somebody would forget the discount. mv_monthly_sales does the same for the series, and saves recomputing it on every query.
• Numbers that validate themselves. If every report includes a total you already know, a botched breakdown gives itself away instantly. And the division of labour with the other tools, which is the question every analyst asks:

The course's criterion: aggregate in SQL, model and draw outside. The operating rule: whatever reduces rows, do it as close to the database as possible. Pulling 2 million rows into pandas for a groupby that returns 12 wastes network, memory and time, and it's the analytical version of 08-04's antipattern.

The second-to-last row is the most dangerous because of how reasonable it sounds: the phone channel bills €378.83 against the web's €349.12, but that doesn't prove the sales rep generates more sales — with 10 orders per channel the difference is €3 per order. The way to find out is an experiment, not a query.

• Starting with the query and not with the definition. "Give me this month's sales" has at least four correct answers. Ask before you write. And not validating against a known total: it's the cheapest check there is and it catches 90 % of JOIN errors.
• Rounding at every step. Round only when presenting: rounding an intermediate value and then summing accumulates the error. And using AVG on an already averaged column: an average of averages only matches the overall one if every group is the same size.
• Presenting a chart without the empty months. The calendar with generate_series (11-01) isn't decoration: without it the trend is a different one. And treating a percentage over 1 or 2 cases as information: with small samples, percentages mislead more than they inform.
• Tip: write the definition in a comment inside the query itself. The report and its definition travel together, and whoever inherits it will know what they're looking at.
• Tip: keep a control figure. Every recurring report should carry a row or column that gives away when something has broken — the analytical equivalent of an automated test. And if a result surprises you, suspect the SQL before the business: nine surprises out of ten are a JOIN that multiplies or a filter that was missing.

Management asks for "margin by category". (1) List three definitional decisions you have to make before writing anything. (2) Write the query using products.cost and the course's line amount. (3) Why can the margin computed this way be wrong even if the query is correct?

Compute, for each month, the revenue, the number of distinct customers who bought and the average revenue per customer, sorted by month. (1) Write it. (2) Why doesn't the sum of "distinct customers per month" come to 12? (3) Which month has the highest average revenue per customer and what caution do you need before highlighting it?

A colleague presents this conclusion: "Portugal is our best market: its average order value is 68 % higher than Spain's". (1) Is the figure correct? (2) Give three reasons why the conclusion doesn't hold. (3) What analysis would you propose instead?

Solution 1

1. (a) Margin on the actual sale price (with the discount) or on the list price? The discount comes out of the margin, so on the line amount. (b) Is the cost the current one (products.cost) or the one at the time of sale? The schema only stores the current one: you have to say so. (c) Are cancelled orders included? A margin on sales that were refunded isn't margin.

3. Because cost is the current cost, not the one at the time of sale: it's the problem unit_price does solve for the price (01-06) and the schema does not solve for the cost, so if costs have risen the historical margin will come out understated. The design fix would be to store unit_cost in order_lines; until that exists, the number is published as an estimate and you say why.

Solution 2

2. Because a customer can buy in several months and gets counted in each one: COUNT(DISTINCT customer_id) is computed within each group, and the groups aren't disjoint by customer — summing that column counts Lucía three times. The total of distinct buyers, 12, only comes from a query with no monthly GROUP BY; it's the same error as summing "daily active users" to get the monthly figure. 3. The highest is 2025-10, with €48.60 per customer (€97.20 across 2 customers), and the caution is section 7's: that's two customers, so the "record" is explained by a single large order.

Solution 3

1. The figure is correct: €52.16 against €30.98 is 68.4 % more; the arithmetic is fine. 2. (a) Sample size: Portugal is 3 orders from 2 customers. A single large order moves the average order value by tens of euros; there's no basis for a conclusion. (b) "Best market" isn't "average order value": Spain contributes €433.70, nearly three times Portugal's (€156.48), with 14 orders and 8 customers. If "best" means volume, the conclusion flips. (c) The costs are missing: shipping to Portugal costs €9.90 against the €4.95 or €0 domestic, and that shipping eats part of the advantage; the margin could be lower. And a fourth, methodological one: the higher average order value could be because international shipping pushes the customer to bundle more items per order —free delivery above a certain amount— which is an effect of the pricing scheme itself and not a property of the market.

3. Compare margin per customer and per period, not average order value: revenue minus product cost minus real shipping cost, divided by active customers, with the number of observations published next to every figure. And if the real question is "where do we invest in acquisition?", the answer needs the acquisition cost and the repeat purchase rate by country, not an average over three orders.

SQL is an analytical tool in its own right, and the craft is less about syntax than about judgement:

• The flow is question → definition → query → validation → presentation, and the steps everybody skips are the second and the fourth. Defining forces you to decide whether "sales" includes shipping (€727.95 against €846.20) and whether the cancelled order counts (€727.95 against €701.20); validating means checking against a total you already knew. GreenStore's fundamental metrics: 20 orders, 12 buyers, 113 units, an average order value of €36.40, 5.65 units per order, a 15.00 % return rate by orders and 11.07 % by amount —two different numbers answering different questions—, 2 active products with no sales and a split of 12 new orders against 8 repeat that reveals acquisition has stopped.
• Time analysis with a running total (closing at €727.95), a 3-month moving average (the one to show) and monthly change (October's +196.70 % that isn't news). And the year-on-year comparison you can't make, because saying so is part of the job. Segmentation by category, country, payment method and channel, with all four breakdowns adding up to €727.95, and the finding no total gave you: Spain bills more but with the lowest average order value. The Pareto is gentle —6 customers account for 65.89 %— with A/B/C classes of 7/3/2 customers and 10/4/3 products.
• The cohorts come out well written and poorly founded: with one or two customers per cohort, the result is an anecdote in table format. Say so, group more coarsely and compare at the same age. The pivot with SUM(CASE …) —portable, with zeros— and with tablefunc's crosstab —compact, with NULL where there's no data and with the columns declared by hand—, closing 06-05's promise. Neither does genuinely dynamic columns: that belongs to the presentation layer.
• Reproducibility: queries in the repository with their definition written down, views and materialized views as a semantic layer, and the division of labour: aggregate in SQL, model and draw outside.

All of this runs in psql or in a BI tool. But the SQL that really runs most times a day isn't written by an analyst: it's fired off by an application, hundreds of times a second, from a web process that opens connections, runs queries and closes them. In the next lesson, SQL in web development, you close the module: the connection and the pool; how a parameterized query is run from code and how the transaction is handled; ORM versus hand-written SQL with the course's criterion; the antipatterns that kill a website, starting with the N+1 that 08-04 left pending; the useful patterns —cursor pagination, a defensive LIMIT, queues with SKIP LOCKED, JSON straight from PostgreSQL—; and the checklist for when somebody says "the website is slow".