Interview Drills · CTE + LAG · growth rate
33. Month-over-month growth
Concept: CTE + LAG · growth rateshow
Growth questions need last period's value next to this period's: aggregate per month in a CTE, then LAG(total) OVER (ORDER BY month) gives the previous month. Growth % = 100 * (this - previous) / previous. The first month has no previous value, so it's NULL.
Example (different data):
WITH w AS ( SELECT week, SUM(visits) AS visits FROM traffic GROUP BY week ) SELECT week, visits, visits - LAG(visits) OVER (ORDER BY week) AS change FROM w;
Your task
A fintech investor update needs month-over-month change in card spend.
Return month (YYYY-MM), total_spend and pct_change (percent change from the previous month, rounded to 1 decimal; NULL for the first month), oldest first.
Starting Postgres in your browser…
Checking your access…