Reporting vs analysis — the four shapes — SQL Intermediate Module 1

Foundations SQL gets you to the point where you can answer a closed question — 'how many active customers do we have?', 'what was last month's revenue?'. Intermediate SQL is where you start answering open questions: 'why is retention falling?', 'which campaign actually drove conversion?', 'what does this number look like normalised against the same period last year?'.

The four shapes

Working analysts spend almost all their time in one of four query shapes. Recognise them and you can plan a query before you write it.

Reporting query — flatten a fact table down to summary numbers (revenue by month, signups by region). Usually GROUP BY + aggregates.
Cohort query — group entities (users, customers, loans) by an entry date, then track their behaviour through time. Always a JOIN to an events table + window or pivoted aggregates.
Funnel query — for each entity, find whether/when it reached each step in a sequence. Usually CASE + MIN/MAX over events + a pivot to wide.
Time-series query — sequence values along a timestamp, compute moving averages / rolling sums / period-over-period change. Window functions over ORDER BY date.

The unlock

Once you can recognise which of the four shapes a question wants, you can plan the joins, grouping, and window frames before typing. Beginners write queries forward (FROM → SELECT). Analysts write them backwards (what's the output shape → what produces it).

Frame the question before the SELECT

A useful pre-query ritual: write the desired output as a tiny example table on paper or in a comment block. Three columns and three rows. If you can't fake the output, you don't yet understand the question. This single habit eliminates half the rewrite cycles in your first year.

sql

-- Question: monthly active customers by country, last 12 months.
-- Output shape (faked):
-- month       | country | mac
-- 2025-01-01  | Kenya   | 1,240
-- 2025-01-01  | Uganda  | 412
-- 2025-02-01  | Kenya   | 1,310
-- ...
-- That tells me: GROUP BY month + country, COUNT DISTINCT user, WHERE event_date in last 12 months.

Exercise

Take this brief: 'Marketing wants to know if the new referral programme is actually growing the user base, or just cannibalising organic signups.' (1) What's the actual question hidden inside? (2) Fake the output table you'd need to answer it. (3) Which of the four shapes is this, and why?