window functions

If there's one advanced topic to really get, it's this one. Window functions show up in basically every data interview, and once they click you'll use them everywhere.

the problem they solve

GROUP BY squashes rows. If you want each employee's salary and their department's average in the same row, GROUP BY can't do it, the individual rows are gone after grouping.

Window functions do the calculation across a group of rows but keep every row.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Every employee is still there, each with their department's average next to them.

the OVER() part

OVER is what makes it a window function. Inside it:

• PARTITION BY, splits rows into groups (like GROUP BY but without squashing)
• ORDER BY, the order of rows inside each group
• frame (ROWS BETWEEN...), which rows around the current one to include. Later.

Empty OVER () means the whole result is one window:

SELECT name, salary, SUM(salary) OVER () AS total_payroll FROM employees;

ranking: ROW_NUMBER, RANK, DENSE_RANK

SELECT
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

Pooja and Dev both earn 70000, see the difference at the bottom:

name	salary	row_num	rnk	dense_rnk
Meera	95000	4	4	4
Rohit	90000	5	5	5
Pooja	70000	6	6	6
Dev	70000	7	6	6
Isha	60000	8	8	7

• ROW_NUMBER, always unique, ties get split randomly
• RANK, ties share a number, then it skips (6, 6, 8)
• DENSE_RANK, ties share, no gaps (6, 6, 7)

top N per group (the classic)

"Highest paid person in each department". Rank inside each department, then keep rank 1:

WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

You can't put a window function in WHERE directly (WHERE runs before them), so the CTE is needed. Change rn = 1 to rn <= 2 for top 2. Use DENSE_RANK instead if ties should all count.

running totals

Add ORDER BY inside OVER and SUM becomes cumulative:

SELECT order_id, order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Per customer:

SELECT customer_id, order_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date;

LAG and LEAD (previous / next row)

SELECT order_id, order_date, amount,
  LAG(amount)  OVER (ORDER BY order_date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY order_date) AS next_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;

First row's LAG is NULL, nothing before it. LAG(amount, 2) goes back 2 rows, LAG(amount, 1, 0) uses 0 instead of NULL.

Great for "month over month growth" and "days since last order".

FIRST_VALUE, LAST_VALUE, NTILE

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

NTILE(4) splits rows into 4 roughly equal buckets.

frames (ROWS BETWEEN)

Moving averages, like the average of this row and the 2 before it:

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

Heads up on LAST_VALUE. The default frame when you have ORDER BY is "start up to current row", so LAST_VALUE just returns the current row, which looks broken. Fix:

LAST_VALUE(name) OVER (
  PARTITION BY department ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

where they run

FROM -> WHERE -> GROUP BY -> HAVING -> window functions -> SELECT -> ORDER BY -> LIMIT

So you can use them on top of GROUP BY results (SUM(COUNT(*)) OVER () for a grand total), but you can't filter on them without wrapping in a CTE/subquery.

stuff that trips people up

• trying to use a window function in WHERE.
• ROW_NUMBER vs RANK vs DENSE_RANK when there are ties.
• LAST_VALUE "not working" because of the default frame.
• forgetting PARTITION BY and getting a running total over everyone instead of per customer.

practice

Check queries.sql.

-- window functions
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

SELECT order_id, order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

SELECT order_id, order_date, amount,
  LAG(amount) OVER (ORDER BY order_date) AS prev_amount
FROM orders;


-- practice ---------------------------------------------------------

-- 1. each order with its amount and the customer's total spend next to it
-- 2. rank products by price within each category (most expensive = 1)
-- 3. the 2 cheapest products in each category
-- 4. for each customer's orders, the number of days since their previous order
-- 5. monthly revenue, and the % change from the previous month
-- 6. each employee's salary and the difference from the highest salary in their department
-- 7. each customer's first ever order (full row), using ROW_NUMBER


-- answers ----------------------------------------------------------

-- 1
SELECT order_id, customer_id, amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

-- 2
SELECT product_name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

-- 3
WITH r AS (
  SELECT product_name, category, price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) AS rn
  FROM products
)
SELECT * FROM r WHERE rn <= 2;

-- 4
SELECT customer_id, order_id, order_date,
  order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
FROM orders
ORDER BY customer_id, order_date;

-- 5
WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly
ORDER BY month;

-- 6
SELECT name, department, salary,
  MAX(salary) OVER (PARTITION BY department) - salary AS below_top
FROM employees;

-- 7
WITH r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM orders
)
SELECT * FROM r WHERE rn = 1;