SQL
Calculate Running Total of Sales Using CTE
Compute a cumulative running total of daily sales using a Common Table Expression (CTE) to improve query readability and manage complex aggregations.
WITH DailySales AS (
SELECT
CAST(order_date AS DATE) AS sales_day,
SUM(total_amount) AS daily_total
FROM orders
GROUP BY CAST(order_date AS DATE)
)
SELECT
sales_day,
daily_total,
SUM(daily_total) OVER (ORDER BY sales_day) AS running_total
FROM DailySales
ORDER BY sales_day;
How it works: This advanced query demonstrates how to calculate a running total of daily sales using a Common Table Expression (CTE) and a window function. The CTE `DailySales` first aggregates `total_amount` by date to get `daily_total`. Then, the main query uses `SUM(daily_total) OVER (ORDER BY sales_day)` as a window function. This function calculates the cumulative sum of `daily_total` as it progresses through the `sales_day` column, ordered chronologically. CTEs enhance readability by breaking down complex queries into logical, named steps, making them easier to understand and debug.