orders(order_id, ordered_at, net_revenue)

For every calendar month from January 2025 to December 2026, return revenue, the revenue of the same month one year earlier, and the percent growth between them.

Months with no orders show 0. The prior-year figure is read from the orders table, so January 2025 can legitimately compare against January 2024 if such orders exist. Where the prior-year revenue is 0 the growth is NULL.

Return month as 'YYYY-MM', revenue, prior_year_revenue and yoy_growth_pct rounded to two decimals, ordered by month.

A LAG(..., 12) over a grouped table only lines up when no month is missing, which is exactly what cannot be assumed here.

Tables

orders

order_id  ordered_at  net_revenue
--------  ----------  -----------
1         2024-01-15  100.00
2         2025-01-10  150.00
3         2025-02-10  80.00
4         2026-01-10  300.00
5         2026-02-10  40.00
6         2026-12-31  10.00

Expected result

month    revenue  prior_year_revenue  yoy_growth_pct
-------  -------  ------------------  --------------
2025-01  150.00   100.00              50.00
2025-02  80.00    0.00                NULL
2025-03  0.00     0.00                NULL
2025-04  0.00     0.00                NULL
2025-05  0.00     0.00                NULL
2025-06  0.00     0.00                NULL
2025-07  0.00     0.00                NULL
2025-08  0.00     0.00                NULL
... 16 more row(s)