Calculate Year-To-Date (YTD) Sales by Month

SELECT CAST(strftime('%Y', order_date) AS INTEGER) AS year, CAST(strftime('%m', order_date) AS INTEGER) AS month, SUM(order_total) AS monthly_sales, SUM(SUM(order_total)) OVER (PARTITION BY strftime('%Y', order_date) ORDER BY strftime('%m', order_date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_sales FROM orders WHERE strftime('%Y', order_date) = '2024' GROUP BY strftime('%Y', order_date), strftime('%m', order_date) ORDER BY year, month;

Why this works

YTD combines aggregation (monthly SUM) with a window function (running SUM of those monthlies). The nested SUM(SUM(...)) OVER is legal because the inner SUM is the group aggregate and the outer SUM is the window. PARTITION BY year resets the accumulation at each year boundary. Only the date-extraction differs across engines.

Success check

Each 2024 month appears once and the final YTD value equals total 2024 revenue.