Aggregate to one row per month, then use LAG over the ordered months to get the previous month's value, and compute (current minus previous) divided by previous. Guard against division by zero with NULLIF. The detail that matters is generating a complete date spine and joining to it, so a month with no activity shows as zero rather than disappearing and making the comparison skip a period.
Why interviewers ask this
It is a practical test that combines aggregation, window functions, and an awareness of missing data. The date spine point is the discriminator: candidates who have shipped real reporting know that gaps in the data silently shift the comparison, while candidates who have only done exercises produce a query that looks right on complete data.
How to structure your answer
- Aggregate to the month grain first.
- Use LAG ordered by month to fetch the prior value.
- Compute the percentage change and guard the denominator.
- Join to a generated date spine so empty months still appear.
- Mention seasonality as a caveat on interpreting the number.
Example answer
I aggregate to one row per month, then LAG the metric ordered by month, then compute the change over the previous value, wrapping the denominator in NULLIF so a zero month does not blow up the query. The bit I always add is a date spine. If March had no orders at all, that row simply does not exist in the aggregate, so April's LAG reaches back to February and reports growth over the wrong period without any error. So I generate a full list of months, left join the aggregate onto it, and coalesce the nulls to zero. I also flag the interpretation risk when I hand it over. Month over month on a business with strong seasonality is noisy, and February being shorter than January makes almost everything look like a decline. Where the audience is going to make decisions on it, I usually show year over year alongside it, or normalize by days in month, and say so on the chart.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How would you build a date spine in your warehouse?
- Why might year over year be more honest than month over month?
- How would you handle a metric that can legitimately be zero?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions