SQL
Calculate Running Totals Using SQL Common Table Expressions (CTEs)
Master SQL CTEs (Common Table Expressions) to break down complex queries. This snippet shows how to compute running totals or cumulative sums, enhancing query readability and maintainability.
WITH DailySales AS (
SELECT
CAST(sale_date AS DATE) AS sale_day,
SUM(amount) AS daily_amount
FROM
sales
GROUP BY
CAST(sale_date AS DATE)
)
SELECT
sale_day,
daily_amount,
SUM(daily_amount) OVER (ORDER BY sale_day) AS running_total
FROM
DailySales
ORDER BY
sale_day;
-- Calculates the cumulative sum of daily sales over time
How it works: This query uses a Common Table Expression (CTE) named `DailySales` to first aggregate sales data by day. Then, the main query leverages a window function (`SUM() OVER (ORDER BY sale_day)`) on the CTE's result to calculate a running total of sales. CTEs improve query readability by modularizing complex logic into distinct, named sub-queries, making them easier to understand and debug.