SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…