ALL IMPORTANT PATTERNS for Running Total and Moving Average

 

These two topics are VERY HIGH-FREQUENCY interview questions.
Below are ALL IMPORTANT PATTERNS for Running Total and Moving Average (Last 3 records) — with multiple SQL styles, edge cases, and interview tips.

---

🔹 1. RUNNING TOTAL (CUMULATIVE SUM)

📌 Sample Table

sales
-----------------------
sale_date | amount
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 150
2024-01-04 | 300

---

✅ Pattern 1: Window Function (BEST & EXPECTED)

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

🔹 Output

sale_date	amount	running_total
01-Jan	100	100
02-Jan	200	300
03-Jan	150	450
04-Jan	300	750

✔ Most optimal
✔ Works in SQL Server / Databricks / Oracle / Postgres

---

✅ Pattern 2: Running Total Per Group (Partition)

SELECT
    region,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS running_total
FROM sales;

✔ Common real-world variant

---

✅ Pattern 3: Using Correlated Subquery (No Window Functions)

SELECT
    s1.sale_date,
    s1.amount,
    (
        SELECT SUM(s2.amount)
        FROM sales s2
        WHERE s2.sale_date <= s1.sale_date
    ) AS running_total
FROM sales s1;

⚠ Slower
✔ Good for older SQL versions

---

✅ Pattern 4: Using Self Join

SELECT
    s1.sale_date,
    s1.amount,
    SUM(s2.amount) AS running_total
FROM sales s1
JOIN sales s2
    ON s2.sale_date <= s1.sale_date
GROUP BY s1.sale_date, s1.amount;

---

✅ Pattern 5: Reset Running Total on Condition

👉 Example: Reset when month changes

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY MONTH(sale_date)
        ORDER BY sale_date
    ) AS running_total
FROM sales;

---

🔹 2. MOVING AVERAGE (LAST 3 RECORDS)

---

✅ Pattern 1: Window Function (BEST)

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM sales;

🔹 Output

sale_date	amount	moving_avg_3
01-Jan	100	100
02-Jan	200	150
03-Jan	150	150
04-Jan	300	216.67

✔ Interview favorite
✔ Handles edge rows automatically

---

✅ Pattern 2: Moving Avg Per Group

SELECT
    region,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM sales;

---

✅ Pattern 3: Using Correlated Subquery

SELECT
    s1.sale_date,
    s1.amount,
    (
        SELECT AVG(s2.amount)
        FROM sales s2
        WHERE s2.sale_date BETWEEN
              DATEADD(day, -2, s1.sale_date)
              AND s1.sale_date
    ) AS moving_avg_3
FROM sales s1;

⚠ Uses date-based window, not row-based
❗ Might return more than 3 rows if multiple records per day

---

✅ Pattern 4: Using Self Join + Row Number

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
    FROM sales
)
SELECT
    c1.sale_date,
    c1.amount,
    AVG(c2.amount) AS moving_avg_3
FROM cte c1
JOIN cte c2
    ON c2.rn BETWEEN c1.rn - 2 AND c1.rn
GROUP BY c1.sale_date, c1.amount;

---

🔥 ADVANCED PATTERNS (SENIOR LEVEL)

✅ Running Total Ignoring NULLs

SUM(COALESCE(amount,0)) OVER (...)

---

✅ Moving Avg with Exactly 3 Rows Only

CASE 
    WHEN COUNT(*) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) = 3
    THEN AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )
END AS moving_avg_3

---

✅ RANGE vs ROWS (VERY IMPORTANT)

Clause	Meaning
ROWS	Physical rows
RANGE	Value-based (dates/numbers)

---

🧠 INTERVIEW CHEAT SHEET

Requirement	Best Solution
Running total	SUM() OVER
Moving average	AVG() OVER
Group-wise	PARTITION BY
Old SQL	Self Join
Performance	Window Functions

---

🎯 Interview Power Line

“I prefer ROWS-based window frames for moving averages to guarantee an exact number of records.”