Revenue waterfall reconstruction

Write a query that reconstructs monthly revenue movement by separating new, expansion, contraction, and churn amounts into a readable waterfall.

Start with one CTE that groups monthly movements, then project net revenue change from the named movement columns.

Sample input

revenue_eventsMonthly account-level revenue movements used to rebuild the waterfall.

account_id	event_month	movement_type	amount
201	2026-01	new	1200
202	2026-01	new	800
201	2026-02	expansion	300
202	2026-02	contraction	-150
203	2026-03	churn	-500

Expected output

Expected output shapeExample monthly waterfall rows. Your final query should return all months from the sandbox.

event_month	new_revenue	expansion_revenue	contraction_revenue	churn_revenue	net_revenue_change
2026-01	2000	0	0	0	2000
2026-02	500	300	-150	0	650
2026-03	900	200	-100	-500	500

Constraints

Preserve the sign of contraction and churn movements, keep each movement type visible, and return one row per month ordered chronologically.

Expected skills

Conditional aggregation, clear CTE naming, financial sign handling, and explaining tradeoffs in revenue movement logic.

SQL

AI evaluation

Run the SQL query to inspect preview rows.