Aggregate Window Functions

You know SUM, AVG, COUNT with GROUP BY. Now use them with OVER() to create running totals, moving averages, and percentage calculations - all while keeping your rows intact.

Running Total

The most common use case: cumulative sum over time.

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

Running Total per Category

Add PARTITION BY to restart the running total for each group:

SELECT
  category,
  date,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY category
    ORDER BY date
  ) as category_running_total
FROM sales;

Notice: Running total resets for each category!

Percentage of Total

Calculate what percentage each row contributes:

SELECT
  department,
  employee,
  salary,
  ROUND(
    100.0 * salary / SUM(salary) OVER (), 2
  ) as pct_of_company,
  ROUND(
    100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2
  ) as pct_of_dept
FROM employees;

Running Count

Track how many events have occurred:

SELECT
  date,
  new_customers,
  SUM(new_customers) OVER (ORDER BY date) as total_customers,
  COUNT(*) OVER (ORDER BY date) as day_number
FROM signups;

Running Average

Calculate the average of all values up to the current row:

SELECT
  date,
  temperature,
  ROUND(AVG(temperature) OVER (ORDER BY date), 1) as avg_temp_so_far
FROM weather_data;

The ORDER BY Effect

Combining Multiple Window Functions

SELECT
  date,
  product,
  revenue,
  -- Running total
  SUM(revenue) OVER (ORDER BY date) as running_total,
  -- Total (constant)
  SUM(revenue) OVER () as grand_total,
  -- Percentage of total
  ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) as pct_of_total,
  -- Rank
  RANK() OVER (ORDER BY revenue DESC) as revenue_rank
FROM sales;

Real-World: YTD (Year-to-Date) Calculation

SELECT
  date,
  EXTRACT(YEAR FROM date) as year,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY EXTRACT(YEAR FROM date)
    ORDER BY date
  ) as ytd_revenue
FROM daily_sales;

YTD resets at the start of each year!

Key Takeaways

Next up: Value Functions - LAG, LEAD, FIRST_VALUE, and LAST_VALUE for comparing rows.