SQL Shortcut: Calculate Running Total Using ONE Window Function
About this lesson
If you work with dashboards or reports, you’ve definitely seen this: Sales increasing day by day Revenue accumulating over time That’s called a running total. Most people write complicated queries for this. Let’s simplify it. 🧩 LONG WAY (Using Correlated Subquery – Slow & Verbose): SELECT s1.date, s1.sales, ( SELECT SUM(s2.sales) FROM sales s2 WHERE s2.date v= s1.date ) AS running_total FROM sales s1 ORDER BY s1.date; This calculates the total for each row separately. It works, but it becomes slow on large datasets. ⚡ SQL SHORTCUT (Window Function): SELECT date, sales, SUM(sales) OVER ( ORDER BY date ) AS running_total FROM sales; One clean query. Efficient. Scalable. And easy to read. Running totals (cumulative sums) are used in almost every analytical workflow. They help track how values grow over time and are essential for: Sales dashboards Financial reporting KPI tracking Time-series analysis Business growth metrics 🔍 Example Table sales ------------------- date | sales 2024-01-01 | 100 2024-01-02 | 200 2024-01-03 | 150 🧪 Expected Output date | sales | running_total 2024-01-01 | 100 | 100 2024-01-02 | 200 | 300 2024-01-03 | 150 | 450 🚀 How Window Function Works SUM(sales) OVER (ORDER BY date) SUM() → cumulative calculation ORDER BY → defines sequence OVER() → keeps row-level data Unlike GROUP BY, it doesn’t collapse rows. 🧩 Partition Example (Running Total per Category) SELECT category, date, sales, SUM(sales) OVER ( PARTITION BY category ORDER BY date ) AS running_total FROM sales; 🧩 Monthly Running Total SUM(sales) OVER ( PARTITION BY YEAR(date), MONTH(date) ORDER BY date ) ⚠️ Common Mistakes Forgetting ORDER BY Using GROUP BY instead Not partitioning correctly Mixing date formats 🔧 Supported Databases MySQL 8+ PostgreSQL SQL Server Oracle Snowflake BigQuery 🧩 5 INTERVIEW QUESTIONS + ANSWERS Q1:
DeepCamp AI