Month-over-month revenue change with LAG, labelled increase / decrease / no change
Table monthly_revenue(month, revenue) has one row per month. For each month return its revenue, the change versus the previous month, and a label — increase, decrease, or no change. The first month has no prior month to compare against.
-- month, revenue, change vs previous month, and an increase/decrease/no-change labelWrite the query.
LAG(revenue) OVER (ORDER BY month) pulls the prior month onto the current row; subtract for the change. A CASE on its sign labels each month increase, decrease, or no change. The first month's LAG is NULL.
- ✗Using
LEAD(next row) when you needLAG(previous row) - ✗Labelling the first month instead of leaving its trend null
- ✗Adding
PARTITION BY month, which isolates each month soLAGis always null
- →How would you compute the percent change rather than the absolute change?
- →What happens to the first month's row and how should you present it?
LAG(revenue) OVER (ORDER BY month) pulls in the previous month's revenue, and a CASE on the sign of the difference gives the label:
SELECT month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
CASE
WHEN LAG(revenue) OVER (ORDER BY month) IS NULL THEN 'n/a'
WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'increase'
WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'decrease'
ELSE 'no change'
END AS trend
FROM monthly_revenue
ORDER BY month;LEAD would look forward (the next month), not back. The first month's LAG is NULL — label it n/a, not increase. PARTITION BY month would be a mistake: it isolates each month, so LAG always returns NULL.
