TECH **SQL for Data Analysis: Rolling/Moving Averages & Running Totals with Window Functions**

SQL for Data Analysis: Rolling/Moving Averages & Running Totals with Window Functions

A hyper-practical, zero-fluff guide for real projects and certifications (PL-300, Google Data Analytics, etc.)

1. What This Is & Why It Matters

You’re analyzing time-series data—sales, stock prices, server metrics, or user activity—and you need to smooth out noise or track cumulative trends. Rolling (moving) averages and running totals are your go-to tools.

• Why it matters in production:
• Dashboards break without them. Stakeholders expect "30-day moving averages" or "year-to-date totals" in reports. If you don’t know window functions, you’ll resort to slow, error-prone self-joins or Excel hacks.
• Performance pitfalls. Without window functions, you might write nested loops or temporary tables that grind your database to a halt on large datasets.
• Real-world scenario: You inherit a legacy SQL query that calculates monthly sales growth by joining the same table 12 times. It runs in 45 seconds. With window functions, it runs in 0.2 seconds and scales to 10 years of data.

Superpower: Window functions let you compute aggregates without collapsing rows, keeping your original dataset intact while adding derived columns.

2. Core Concepts & Components

• Window Function
  A function that operates on a "window" of rows relative to the current row, without grouping (unlike GROUP BY).
  Production insight: If you see OVER(), you’re using a window function. These are 10–100x faster than self-joins for time-series analysis.

• OVER() Clause
  Defines the window of rows to consider. Syntax: OVER([PARTITION BY ...] [ORDER BY ...] [ROWS BETWEEN ...]).
  Production insight: Forgetting ORDER BY in time-series windows is a top cause of silent bugs—your averages will be wrong but look plausible.

• ROWS BETWEEN
  Specifies the frame of rows around the current row (e.g., ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING).
  Production insight: Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—this trips up 80% of beginners.

• UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING
  Includes all rows from the start/end of the partition.
  Production insight: Use UNBOUNDED PRECEDING for running totals; UNBOUNDED FOLLOWING is rare but useful for "reverse" running totals.

• PARTITION BY
  Splits the window into groups (like GROUP BY, but doesn’t collapse rows).
  Production insight: Over-partitioning (e.g., PARTITION BY user_id, date, hour) can kill performance—only partition by what you need.

• ORDER BY in OVER()
  Determines the sequence of rows in the window.
  Production insight: If your data isn’t pre-sorted, ORDER BY in the window function doesn’t guarantee output order—add an outer ORDER BY if needed.

• AVG() OVER()
  Calculates a rolling average.
  Production insight: For daily data, AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives a 7-day moving average.

• SUM() OVER()
  Calculates a running total.
  Production insight: SUM(sales) OVER (ORDER BY date) gives a cumulative sum—critical for "year-to-date" metrics.

• FIRST_VALUE() / LAST_VALUE()
  Grabs the first/last value in the window.
  Production insight: LAST_VALUE() is tricky—it defaults to RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, which often returns the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to fix it.

• LAG() / LEAD()
  Accesses previous/following rows (e.g., LAG(sales, 1) = yesterday’s sales).
  Production insight: LAG() is faster than self-joins for comparing consecutive rows.

3. Step-by-Step Hands-On: Calculate Rolling Averages & Running Totals

Prerequisites

• A SQL environment (PostgreSQL, BigQuery, Snowflake, or SQL Server).
• A table with time-series data. We’ll use this example:

CREATE TABLE daily_sales (
    date DATE,
    product_id INT,
    revenue DECIMAL(10,2)
);

-- Sample data
INSERT INTO daily_sales VALUES
    ('2023-01-01', 1, 100), ('2023-01-02', 1, 150), ('2023-01-03', 1, 200),
    ('2023-01-01', 2, 50),  ('2023-01-02', 2, 75),  ('2023-01-03', 2, 100);

Task 1: 3-Day Rolling Average per Product

Goal: Add a column showing the average revenue over the last 3 days for each product.

SELECT
    date,
    product_id,
    revenue,
    AVG(revenue) OVER (
        PARTITION BY product_id
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_3d
FROM daily_sales
ORDER BY product_id, date;

Expected Output:

date       | product_id | revenue | rolling_avg_3d
-----------+------------+---------+----------------
2023-01-01 | 1          | 100.00  | 100.00         -- Only 1 row in window
2023-01-02 | 1          | 150.00  | 125.00         -- (100 + 150) / 2
2023-01-03 | 1          | 200.00  | 150.00         -- (100 + 150 + 200) / 3
2023-01-01 | 2          | 50.00   | 50.00
2023-01-02 | 2          | 75.00   | 62.50
2023-01-03 | 2          | 100.00  | 75.00

Why it works:
- PARTITION BY product_id resets the window for each product.
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current row + 2 prior rows.
- AVG() calculates the mean over this window.

Task 2: Running Total (Cumulative Sum) per Product

Goal: Add a column showing the cumulative revenue for each product.

SELECT
    date,
    product_id,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY product_id
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales
ORDER BY product_id, date;

Expected Output:

date       | product_id | revenue | running_total
-----------+------------+---------+--------------
2023-01-01 | 1          | 100.00  | 100.00
2023-01-02 | 1          | 150.00  | 250.00        -- 100 + 150
2023-01-03 | 1          | 200.00  | 450.00        -- 100 + 150 + 200
2023-01-01 | 2          | 50.00   | 50.00
2023-01-02 | 2          | 75.00   | 125.00
2023-01-03 | 2          | 100.00  | 225.00

Why it works:
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the start of the partition to the current row.
- SUM() adds up the values in this window.

Task 3: Compare Current Day to Previous Day (Using LAG)

Goal: Add a column showing the revenue difference vs. the prior day.

SELECT
    date,
    product_id,
    revenue,
    revenue - LAG(revenue, 1) OVER (
        PARTITION BY product_id
        ORDER BY date
    ) AS day_over_day_change
FROM daily_sales
ORDER BY product_id, date;

Expected Output:

date       | product_id | revenue | day_over_day_change
-----------+------------+---------+---------------------
2023-01-01 | 1          | 100.00  | NULL                -- No prior day
2023-01-02 | 1          | 150.00  | 50.00               -- 150 - 100
2023-01-03 | 1          | 200.00  | 50.00               -- 200 - 150
2023-01-01 | 2          | 50.00   | NULL
2023-01-02 | 2          | 75.00   | 25.00
2023-01-03 | 2          | 100.00  | 25.00

Why it works:
- LAG(revenue, 1) fetches the revenue from the previous row in the window.
- The first row in each partition has no prior row, so it returns NULL.

4. ? Production-Ready Best Practices

Performance

• Index your ORDER BY columns. Window functions with ORDER BY perform full scans if the column isn’t indexed.
  sql CREATE INDEX idx_daily_sales_product_date ON daily_sales(product_id, date);
• Avoid RANGE BETWEEN for time-series data. ROWS BETWEEN is faster and more predictable.
• Limit window size. A 365-day rolling average is expensive—consider downsampling (e.g., weekly averages).

Readability

• Alias your windows. Reuse the same window definition with WINDOW clause: sql SELECT
date,
revenue,
AVG(revenue) OVER w AS rolling_avg,
SUM(revenue) OVER w AS running_total FROM daily_sales WINDOW w AS (PARTITION BY product_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
• Comment complex windows. Explain why you chose a specific frame (e.g., -- 7-day MA for smoothing weekly seasonality).

Maintainability

• Test edge cases. What happens on the first/last day of the dataset? Does LAG() return NULL as expected?
• Document assumptions. If your rolling average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, note that it’s a 7-day average (not 6).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Frame specification:

2. "Which frame gives a 7-day moving average?"

   
   
   • ❌ ROWS BETWEEN 6 PRECEDING AND 1 FOLLOWING (9-day window)
   • ✅ ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (7-day window)

3. Running totals:

4. "How do you calculate a year-to-date total?"

   
   
   • ✅ SUM(revenue) OVER (PARTITION BY year ORDER BY date)
   • ❌ SUM(revenue) OVER (PARTITION BY date ORDER BY year) (wrong logic)

5. LAG vs LEAD:

6. "How do you compare today’s sales to yesterday’s?"

   
   
   • ✅ revenue - LAG(revenue, 1) OVER (ORDER BY date)
   • ❌ revenue - LEAD(revenue, 1) OVER (ORDER BY date) (compares to tomorrow)

7. Edge cases:

8. "What does AVG(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) return on the first row?"
   • ✅ The value of the first row (only 1 row in window).
   • ❌ NULL (common misconception).

Key Trap Distinctions

• ROWS vs RANGE:
• ROWS = physical rows (e.g., ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING = 3 rows).

• RANGE = logical values (e.g., RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING includes all rows with values ±1 from current row). Avoid RANGE for time-series data.

• Default window frames:

• SUM() OVER (ORDER BY date) = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• AVG() OVER (ORDER BY date) = same as above.
• LAST_VALUE() OVER (ORDER BY date) = RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (usually not what you want).

7. ? Hands-On Challenge

Challenge:
You have a table server_metrics with columns timestamp, server_id, and cpu_usage. Write a query to: 1. Calculate a 5-minute rolling average of CPU usage per server.
2. Flag any row where the current CPU usage is > 2x the 5-minute average.

Solution:

SELECT
    timestamp,
    server_id,
    cpu_usage,
    AVG(cpu_usage) OVER (
        PARTITION BY server_id
        ORDER BY timestamp
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_5min,
    CASE WHEN cpu_usage > 2 * AVG(cpu_usage) OVER (
        PARTITION BY server_id
        ORDER BY timestamp
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) THEN 'ALERT' ELSE 'OK' END AS status
FROM server_metrics
ORDER BY server_id, timestamp;

Why it works:
- ROWS BETWEEN 4 PRECEDING AND CURRENT ROW = 5-minute window (assuming 1 row per minute).
- The CASE statement compares the current cpu_usage to 2x the rolling average.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions Docs – Official guide with deep dives.
2. BigQuery Window Functions – Google’s syntax and examples.
3. Book: SQL for Data Analysis by O’Reilly – Chapter 5 covers window functions in depth.
4. Practice: StrataScratch – Real interview questions using window functions.