SQL Shortcut: Calculate Running Total Using ONE Window Function

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: What is a running total in SQL?
A1: A cumulative sum that adds values sequentially based on order.

Q2: Why use window functions instead of subqueries?
A2: Because they are faster, more readable, and scalable.

Q3: What does OVER() do?
A3: It defines a window for the function without collapsing rows.

Q4: Difference between GROUP BY and window functions?
A4: GROUP BY aggregates rows; window functions keep row-level data.

Q5: Can running totals be calculated per group?
A5: Yes, using PARTITION BY.

#SQL #DataAnalytics #WindowFunctions #DataScience #Analytics

Видео SQL Shortcut: Calculate Running Total Using ONE Window Function канала CodeVisium