Window Functions Are the SQL Superpower Nobody Taught You

For a long time, I avoided window functions. They looked complicated. PARTITION BY, OVER(), weird syntax that didn't look like the rest of SQL. So I did what a lot of people do — solved the same problems with clunky self-joins and subqueries, and just accepted that some queries were going to be long and slow.

Then I actually learned window functions, and it felt like discovering a whole feature had been sitting in the toolbox the entire time, unused.

The short version: OVER() lets you calculate across a group of rows without collapsing them into one. Same rows in, same rows out — just smarter.

Here's what they do, why they matter, and a few uses that come up constantly in real work.

The core idea, in plain words

A normal GROUP BY collapses rows. Ten rows go in, one summary row comes out per group. That's useful, but sometimes you want the opposite — keep every row, and just attach some extra calculated info to each one, pulled from the other rows around it.

That's a window function. Same number of rows in, same number out. Nothing collapses.

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

Every employee keeps their own row. But now each row also shows the average salary for their department, calculated across the whole group. No GROUP BY needed, no subquery, no join.

---

Running totals without the pain

Want a running total of sales, day by day? Without window functions, this usually turns into a self-join or a correlated subquery. Slow. Annoying to read six months later when you have to touch it again.

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

ORDER BY inside the OVER() tells the database to add up everything from the start of the data through the current row. One line. No subquery.

sale_date	amount	running_total
Jan 1	100	100
Jan 2	50	150
Jan 3	75	225

---

Top N per group, the way it should be done

This is the classic problem: find the top 3 highest-paid employees in each department. Doing this without window functions usually means some ugly combination of subqueries and LIMIT, applied per group, which SQL doesn't naturally support.

With RANK(), it's clean:

SELECT *
FROM (
  SELECT
    employee,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees
) ranked
WHERE rank <= 3;

PARTITION BY splits the data into departments. ORDER BY salary DESC ranks people within each department. Then you just filter down to rank <= 3. That's the whole trick.

Cheat sheet: which ranking function do you actually want?

• ROW_NUMBER() — always gives unique numbers, even for ties (1, 2, 3, 4…)
• RANK() — ties get the same number, but leaves a gap after (1, 1, 3, 4…)
• DENSE_RANK() — ties get the same number, no gap (1, 1, 2, 3…)

Pick based on whether you care about the gap. Most of the time, DENSE_RANK() is what people actually want and RANK() is what they reach for out of habit.

I rewrote a reporting query at a past job that used two nested subqueries and a LIMIT inside a correlated join just to pull the top 5 customers per region. Forty-some lines. The RANK() version above did the same thing in nine.

---

Comparing a row to the one before it

LAG() grabs a value from a previous row. LEAD() grabs one from a row ahead. Trends, gaps, row-to-row comparisons — this is the pair you reach for.

SELECT
  user_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY user_id ORDER BY order_date
  ) AS days_since_last_order
FROM orders;

This tells you how many days passed between each user's orders — no self-join required. Swap LAG for LEAD and you get the days until the next order instead. Same idea, opposite direction.

The mistake almost everyone makes at first

Forgetting ORDER BY inside OVER() when you actually need it. Without it, functions like SUM() add up the entire partition for every row, not a running total.

-- wrong: every row shows the department's full total, not a running total
SUM(amount) OVER (PARTITION BY department)

-- right: running total within each department, row by row
SUM(amount) OVER (PARTITION BY department ORDER BY sale_date)

Same function, one missing clause, completely different result. This one gets people every time.

---

Why this is worth learning properly

Window functions replace a whole category of clunky, slow queries with something readable and fast. Running totals, rankings, row-to-row comparisons, moving averages — all of it gets simpler once OVER() stops looking scary. It's one of those SQL features that feels intimidating for about twenty minutes, and then becomes something you reach for every single week.

Further reading:

PostgreSQL: Documentation: 18: 3.5. Window Functions

postgresql.org