Running Totals & Moving Averages

Intermediate

20

35

25

Aggregate Functions as Window Functions

Window Frame Specifications

Master running totals (cumulative sums) for financial and business reporting

Calculate moving averages with precise window frame specifications

Understand different types of moving averages (simple, weighted, exponential)

Handle time-based windows and date-driven calculations

Optimize performance for large time-series datasets

Apply running calculations to real business scenarios and trend analysis

Running totals and moving averages are the backbone of financial reporting and trend analysis. Every bank statement shows running balances, every stock chart displays moving average trend lines, and every business dashboard includes cumulative metrics. Tesla uses running totals to track production milestones, Netflix uses moving averages to smooth viewership data for trend analysis, and Amazon uses both for inventory planning and sales forecasting. These calculations transform raw daily data into meaningful business insights - turning "noisy" day-to-day fluctuations into clear trends that executives can understand and act upon.

Running totals and moving averages appear in 99% of financial reports and business dashboards. They're fundamental to data analysis roles across all industries - from financial analysts calculating YTD revenue to supply chain managers tracking inventory trends. Companies like Goldman Sachs use sophisticated moving averages for algorithmic trading, while startups use simple running totals to track growth metrics. These skills are tested in 85% of data analyst interviews and are essential for anyone working with time-series data. Mastering window frames for these calculations demonstrates advanced SQL proficiency.

flowchart TB

subgraph "Sample Daily Sales Data"

A["Day 1: $100<br/>Day 2: $150<br/>Day 3: $120<br/>Day 4: $180<br/>Day 5: $140<br/>Day 6: $160<br/>Day 7: $130"]

end

subgraph "Running Total (Cumulative Sum)"

B["Day 1: $100<br/>Day 2: $250 ($100+$150)<br/>Day 3: $370 ($250+$120)<br/>Day 4: $550 ($370+$180)<br/>Day 5: $690 ($550+$140)<br/>Day 6: $850 ($690+$160)<br/>Day 7: $980 ($850+$130)"]

B1["⬆️ Accumulates all previous values<br/>📈 Shows cumulative progress<br/>🏆 Perfect for YTD, QTD metrics"]

end

subgraph "3-Day Moving Average"

C["Day 1: $100 (only 1 value)<br/>Day 2: $125 (avg of $100,$150)<br/>Day 3: $123 (avg of $100,$150,$120)<br/>Day 4: $150 (avg of $150,$120,$180)<br/>Day 5: $147 (avg of $120,$180,$140)<br/>Day 6: $160 (avg of $180,$140,$160)<br/>Day 7: $143 (avg of $140,$160,$130)"]

C1["📊 Smooths out fluctuations<br/>🔄 Rolling window calculation<br/>📉 Shows trends clearly"]

end

A --> B

A --> C

style B1 fill:#e8f5e8

style C1 fill:#e3f2fd

Think of running totals and moving averages as two different ways to tell the story of your data over time. Running totals are like your bank account balance - they keep adding up everything that happened before to show you where you stand today. Moving averages are like the weather forecast - they smooth out the daily ups and downs to show you the underlying trend. Running totals answer "How much total progress have we made?" while moving averages answer "What direction are we heading?" Both are essential for turning raw daily numbers into business insights that executives can understand and act upon.

**

**Running Totals (Cumulative Sums) - The Progress Tracker:**

Running totals accumulate values from the beginning of the dataset (or partition) up to the current row. They're perfect for tracking cumulative progress.

```sql

-- Basic running total

SELECT

order_date,

daily_revenue,