Day 9: Window Functions — Analytics Without Aggregation

October 27, 2025

This content originally appeared on DEV Community and was authored by Hardik Kanajariya

Day 9: Window Functions — Analytics Without Aggregation

Welcome to Day 9!

Today we’ll unlock the power of window functions to compute rankings, running totals, moving averages, and more — all without collapsing rows like GROUP BY does.

What You’ll Learn

OVER() and PARTITION BY basics
Ranking functions: ROW_NUMBER, RANK, DENSE_RANK
Value functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Aggregates as windows: SUM, AVG, COUNT with frames
Frame clauses: ROWS/RANGE and window frames
Practical analytics patterns

1) OVER() and PARTITION BY

-- Average salary by department, shown on each row
SELECT 
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

2) Ranking Functions

-- Rank employees by salary per department
SELECT 
  employee_id, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

When duplicates occur, RANK leaves gaps, DENSE_RANK does not; ROW_NUMBER is unique per partition.

3) Value Navigation: LAG / LEAD

-- Compare each month to previous
SELECT 
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount) AS revenue,
  LAG(SUM(total_amount))  OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_rev,
  LEAD(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS next_rev
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

4) Running Totals and Moving Averages

-- Running total revenue per customer
SELECT 
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

-- 3-month moving average of revenue
WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
       revenue,
       AVG(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS mov_avg_3m
FROM monthly
ORDER BY month;

5) FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT 
  department,
  employee_id,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary,
  LAST_VALUE(salary)  OVER (PARTITION BY department ORDER BY salary DESC 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_salary
FROM employees;

Note: LAST_VALUE needs an explicit frame to look ahead.

6) Practical Patterns

Top-N per group (filter rn <= N)
Churn/retention using LAG dates
Cohort performing metrics over time

-- Top 3 products by revenue per category
WITH product_rev AS (
  SELECT 
    category,
    product_id,
    SUM(quantity * unit_price) AS revenue
  FROM sales
  GROUP BY category, product_id
)
SELECT * FROM (
  SELECT 
    category,
    product_id,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_rev
) t
WHERE rn <= 3
ORDER BY category, revenue DESC;

Performance Tips

Add ORDER BY indexes for large window sorts
Prefer ROWS frames for deterministic behavior
Use EXPLAIN ANALYZE to understand sort and window costs

Today’s Challenge

1) Compute each customer’s running total and flag when they cross $5,000 lifetime spend
2) For each department, list top 2 salaries and the gap from the previous salary
3) Build a 6-month moving average on monthly revenue

-- Your solutions here

Summary

Window functions add analytics without collapsing rows
Ranking, navigation, and aggregate windows cover most use cases
Frames control the window — choose wisely

Coming Up Next

Day 10: Indexes and Performance Optimization — speed up your queries! #PostgreSQL #SQL #WindowFunctions #Analytics #15DaysOfPostgreSQL

This content originally appeared on DEV Community and was authored by Hardik Kanajariya