~/learn/SQL · Analytics patterns
Growth and funnels
Month-over-month change and step-by-step conversion.
why this matters
Every business review opens with growth numbers and funnel drop-off. These two patterns cover most analytics interview rounds.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
Month-over-month growth is a monthly aggregate plus LAG: compare each month to the one before and express the difference as a percentage.
Try it: Show the running total of revenue alongside the growth rate.
A funnel counts distinct users reaching each stage, in order. Conditional aggregation gives you one row with one column per step, which makes the drop-off obvious.
Note
Funnel steps must be cumulative and ordered, otherwise a later step can show more users than an earlier one. Define each step as 'reached at least this far'.
Growth formula
MoM % = (this − prev) / prev × 100 use LAG(total) OVER (ORDER BY month) for prev
Common gotcha
If the previous month is 0 or NULL, the division blows up or returns NULL. Guard it with NULLIF(prev, 0).
key takeaways
- LAG supplies the previous period's value.
- Guard divisions with NULLIF to avoid divide-by-zero.
- Funnels count distinct users reaching each step.
check your understanding
Why wrap the denominator in NULLIF(prev, 0)?
free practice
Build confidence with a question you can solve without Pro.