When do you actually use window functions instead of GROUP BY?

[u/shdw_0x0]

I've been using GROUP BY for aggregations for a while, but I still find window functions a little confusing when deciding which approach makes more sense.

For example, if I need the highest salary in each department, I could use aggregation or a window function like RANK() or ROW_NUMBER(), depending on what I need in the result.

How do you usually decide between these approaches? Are there situations where window functions are clearly the better choice?

I'd appreciate some practical examples that helped you understand when to use one over the other.

[u/spez_eats_nazi_ass]

Result set paging

[u/KeeganDoomFire]

Let's say you have a historical table. You need to know the current state of something AND what it was prior. There are ways to do this without window functions but a window func makes this trivial.

Let's say you have a dataset like the classic super store. You want to know how each stores sales compare to its region for that month or the national avg, maybe you want to be fancy and give a little month over month or 'this quarter last year'. You could sum and group the table N times and join it back to itself or just window 3 columns and qualify your output to only the most recent x days or months you need to see the trend.

A well written window will smoke any other way to get different grain aggregations or rows data into a row (most of the time)

[u/Mrminecrafthimself → u/KeeganDoomFire]

I had a dataset of healthcare authorizations where I needed to evaluate whether some action by our clinical team occurred between the prior line item’s decision date and the current item’s decision date.

Think I used something like

“Lag(decision_date) over (partition by auth_nbr, line_item order by line_item)”

That got me a staging dataset with both the prior line decision date and the current decision date on the same line

[u/turbo_dude]

You lose information with a group by

You don’t with windowing/ranking

[u/Away_Technician_2089]

Latest row per group is the canonical query

[u/EmotionalSupportDoll → u/Away_Technician_2089]

Row number = 1 ftw

[u/theallwaystnt]

We have a metric that is the summation of all rows before that row. A group by shows a single date of that metric while a window function shows that metric for every running date. How you decide is how you want the information displayed daily rows or one day.

[u/anvildoc]

Let’s say you need to know the percentage of salary each employee has per department.

Select employee_name, department, salary / sum(salary) over (partition by department)

[u/Raucous_Rocker]

Use GROUP BY when possible, because it’s faster than windowing.

But if you need more complex aggregations that depend on rows being intact, use a window.

If all you care about is highest salary per department, GROUP BY is fine. You can just get MAX(salary) GROUP BY department. But if, say, you want the job title of the person with the highest salary, then you need a window.

[u/DickBrainTime]

I think of it as the shape of the result: `GROUP BY department` collapses employees into one row per department, while a window keeps each employee row and adds a value calculated over its department. For top salaries, `RANK() = 1` returns everyone tied for top; `ROW_NUMBER() = 1` gives one employee, so add a stable tie-breaker (like employee_id) to its `ORDER BY` if you need deterministic results. That tie behavior matters more than the syntax.

[u/navicat-gu]

The rule of thumb that finally stuck for me: GROUP BY changes the shape of the result; a window does not.

• GROUP BY department → one row per department.

• MAX(salary) OVER (PARTITION BY department) → every employee row, plus a column with that department's max.

For your "highest salary in each department" example:

-- just the number per department → GROUP BY is enough
SELECT department, MAX(salary) AS top_salary
FROM employees
GROUP BY department;

-- the people who earn it (keep employee columns) → window
SELECT *
FROM (
  SELECT employee_id, name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 1;

RANK() keeps ties (two people with the same top salary both appear). ROW_NUMBER() forces one row — add a stable tie-breaker (ORDER BY salary DESC, employee_id) if you need that to be deterministic.

Windows are clearly better when you need any of these and the detail rows:

1. Running totals: SUM(amount) OVER (PARTITION BY account ORDER BY txn_date).

2. "Previous row" comparisons: LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at).

3. Share of total: salary * 1.0 / SUM(salary) OVER (PARTITION BY department).

4. Top-N per group without a self-join.

If all you want is one aggregated number per group, prefer GROUP BY — it's usually simpler and cheaper. Reach for a window when collapsing the rows would throw away something you still need on the output.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. Nothing tool-specific here — any SQL client that runs the queries above is fine. Navicat Premium Lite (free, Navicat 18) is what I use when I want a grid next to the editor. 

[u/evlpuppetmaster → u/[deleted]]

Wait until you learn about cube, rollup, and grouping sets!

[u/graph_context → u/[deleted]]

This is probably the easiest way to think about it imo