TECH **SQL for Data Analysis: Aggregate Window Functions (SUM, AVG, COUNT OVER PARTITION BY) – Zero-Fluff Study Guide**

SQL for Data Analysis: Aggregate Window Functions (SUM, AVG, COUNT OVER PARTITION BY) – Zero-Fluff Study Guide

1. What This Is & Why It Matters

What it is:
Aggregate window functions (SUM(), AVG(), COUNT() with OVER(PARTITION BY)) let you compute aggregations (totals, averages, counts) without collapsing rows—unlike GROUP BY, which squashes your dataset into fewer rows. Instead, they add a computed column to every row, keeping the original granularity.

Why it matters in production:
- You need running totals, moving averages, or per-group metrics (e.g., "Show me daily sales and the monthly total up to that day").
- You’re building dashboards where users want to see both individual records and aggregated trends (e.g., "Show me each customer’s order and their lifetime spend").
- You’re debugging data quality (e.g., "Flag customers where their average order value is 3x the group average").
- You’re optimizing queries—window functions often outperform self-joins or subqueries.

Real-world scenario:
You’re analyzing e-commerce data. Your boss wants a report showing: - Each order (order_id, customer_id, order_date, amount) - The customer’s lifetime spend (sum of all their orders) - The average order value for their country
- A flag if this order is above their personal average

Without window functions, you’d need multiple subqueries or self-joins, making the query slow and hard to maintain. With window functions, it’s one clean query.

2. Core Concepts & Components

• OVER() clause
• Defines the "window" of rows to aggregate over.

• Production insight: If you omit PARTITION BY, the window is the entire result set—useful for grand totals, but often not what you want.

• PARTITION BY

• Splits the data into groups (like GROUP BY), but keeps all rows.

• Production insight: Over-partitioning (e.g., PARTITION BY customer_id, order_id) can kill performance—only partition on what you need.

• SUM() OVER()

• Computes a running total or group sum.

• Production insight: Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running totals (e.g., "cumulative sales").

• AVG() OVER()

• Computes a moving average or group average.

• Production insight: For time-series data, use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day moving average.

• COUNT() OVER()

• Counts rows in a window (e.g., "How many orders has this customer placed?").

• Production insight: COUNT(*) counts all rows, COUNT(column) ignores NULLs—be explicit.

• ORDER BY inside OVER()

• Defines the order of rows in the window (critical for running totals).

• Production insight: If you forget ORDER BY, the result is non-deterministic (you’ll get the same sum, but applied to random rows).

• ROWS BETWEEN

• Controls the frame of the window (e.g., "last 3 rows").

• Production insight: Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—often not what you want for time-series data.

• RANGE BETWEEN

• Similar to ROWS, but works on values (e.g., "all rows where date is within 7 days").
• Production insight: Less common than ROWS, but useful for date-based windows.

3. Step-by-Step Hands-On: Building a Customer Order Report

Prerequisites

• A SQL environment (PostgreSQL, BigQuery, Snowflake, or SQL Server).
• A table orders with columns: sql order_id (INT), customer_id (INT), order_date (DATE), amount (DECIMAL), country (VARCHAR)
• Sample data (run this first): ```sql CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  country VARCHAR(50) );

INSERT INTO orders VALUES (1, 101, '2023-01-01', 50.00, 'USA'), (2, 101, '2023-01-15', 75.00, 'USA'), (3, 102, '2023-01-05', 30.00, 'UK'), (4, 102, '2023-01-20', 40.00, 'UK'), (5, 103, '2023-01-10', 100.00, 'USA'), (6, 101, '2023-02-01', 60.00, 'USA'); ```

Task: Write a query that shows:

1. Each order.
2. The customer’s lifetime spend (sum of all their orders).
3. The average order value for their country.
4. A flag (1 or 0) if this order is above their personal average.

Step 1: Add the customer’s lifetime spend

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  country,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_spend
FROM orders;

Expected output:

order_id | customer_id | order_date | amount | country | customer_lifetime_spend
---------+-------------+------------+--------+---------+------------------------
1        | 101         | 2023-01-01 | 50.00  | USA     | 185.00
2        | 101         | 2023-01-15 | 75.00  | USA     | 185.00
6        | 101         | 2023-02-01 | 60.00  | USA     | 185.00
3        | 102         | 2023-01-05 | 30.00  | UK      | 70.00
4        | 102         | 2023-01-20 | 40.00  | UK      | 70.00
5        | 103         | 2023-01-10 | 100.00 | USA     | 100.00

Step 2: Add the country’s average order value

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  country,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_spend,
  AVG(amount) OVER (PARTITION BY country) AS country_avg_order_value
FROM orders;

Expected output (new column):

country_avg_order_value
-----------------------
71.25  -- USA (50+75+60+100)/4
35.00  -- UK (30+40)/2
71.25  -- USA
35.00  -- UK
71.25  -- USA

Step 3: Add a flag for orders above the customer’s average

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  country,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_spend,
  AVG(amount) OVER (PARTITION BY country) AS country_avg_order_value,
  CASE
    WHEN amount > AVG(amount) OVER (PARTITION BY customer_id)
    THEN 1
    ELSE 0
  END AS is_above_personal_avg
FROM orders;

Expected output (new column):

is_above_personal_avg
---------------------
0  -- 50 < 61.67 (101's avg)
1  -- 75 > 61.67
0  -- 60 < 61.67
0  -- 30 < 35 (102's avg)
1  -- 40 > 35
0  -- 100 = 100 (103's avg)

Step 4: (Bonus) Add a running total of sales per customer

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  country,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_spend,
  AVG(amount) OVER (PARTITION BY country) AS country_avg_order_value,
  CASE
    WHEN amount > AVG(amount) OVER (PARTITION BY customer_id)
    THEN 1
    ELSE 0
  END AS is_above_personal_avg
FROM orders;

Expected output (new column):

running_total
-------------
50.00  -- First order for 101
125.00 -- 50 + 75
185.00 -- 125 + 60
30.00  -- First order for 102
70.00  -- 30 + 40
100.00 -- Only order for 103

4. ? Production-Ready Best Practices

Performance

• Avoid over-partitioning: PARTITION BY customer_id, order_id is useless (each group has 1 row).
• Use ROWS BETWEEN for time-series data: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day moving average.
• Index PARTITION BY and ORDER BY columns: Speeds up window function execution.
• Test with EXPLAIN ANALYZE: Window functions can be expensive—check the query plan.

Readability

• Alias window functions clearly: SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_spend > SUM(amount) OVER (PARTITION BY customer_id) AS total.
• Line-break OVER() clauses: Makes complex windows easier to debug.
  sql SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total

Debugging

• Check for NULL handling: COUNT(*) vs COUNT(column) behave differently.
• Verify ORDER BY in running totals: Without it, results are non-deterministic.
• Compare with GROUP BY: If you’re not sure, write a GROUP BY version first to validate the logic.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Running total:
2. "Write a query to show daily sales and a running total for the month."

3. Answer: SUM(sales) OVER (ORDER BY date)

4. Group averages:

5. "Show each employee’s salary and the average salary for their department."

6. Answer: AVG(salary) OVER (PARTITION BY department)

7. Moving average:

8. "Calculate a 7-day moving average of stock prices."

9. Answer: AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

10. Flagging outliers:

11. "Flag customers whose order value is 2x the country average."
12. Answer: CASE WHEN amount > 2 * AVG(amount) OVER (PARTITION BY country) THEN 1 ELSE 0 END

Key ⚠️ Trap Distinctions

• GROUP BY vs PARTITION BY:
• GROUP BY collapses rows; PARTITION BY keeps them.
• Exam trap: "Which collapses rows?" → GROUP BY.
• ROWS vs RANGE:
• ROWS = physical rows; RANGE = logical values (e.g., dates).
• Exam trap: "Which is better for time-series?" → ROWS.
• COUNT(*) vs COUNT(column):
• COUNT(*) = all rows; COUNT(column) = non-NULL values.
• Exam trap: "Which counts NULLs?" → Neither.

7. ? Hands-On Challenge

Challenge:
You have a table sales with columns date (DATE), product_id (INT), revenue (DECIMAL). Write a query that: 1. Shows each sale.
2. Adds a column with the 3-day moving average revenue for that product.
3. Flags (1 or 0) if the sale is above the product’s 3-day average.

Solution:

SELECT
  date,
  product_id,
  revenue,
  AVG(revenue) OVER (
    PARTITION BY product_id
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day,
  CASE
    WHEN revenue > AVG(revenue) OVER (
      PARTITION BY product_id
      ORDER BY date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) THEN 1
    ELSE 0
  END AS is_above_moving_avg
FROM sales;

Why it works:
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = last 3 rows (including current).
- PARTITION BY product_id ensures the average is per product.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions Docs – Official guide with examples.
2. BigQuery Window Functions – Google’s docs with real-world use cases.
3. SQLZoo Window Functions Tutorial – Interactive exercises.
4. "SQL for Data Analysis" (O’Reilly) – Chapter 6 covers window functions in depth.