SQL GROUP BY vs Window Functions: Choose by Output Grain

GROUP BY and window functions can both calculate totals, averages, counts, minima, and maxima. The difference is not the function name. It is the shape of the result. GROUP BY collapses detail rows into one row per group. A window function keeps the detail rows and adds group context to each one.

That single distinction resolves most interview confusion. Before choosing syntax, state what one output row should represent.

Key takeaways

• Use GROUP BY when the requested output grain is the group itself.
• Use a window function when every detail row must remain visible.
• For “rank groups by their totals,” aggregate first and apply the window second.
• A window calculation happens after WHERE and grouping, so filter window results in an outer query.

The SQL GROUP BY practice hub covers reductions such as Couriers per Depot and Distinct Languages Spoken by Each Guide. The SQL window functions hub covers row-preserving and ranking patterns such as Department Top-3 Earners and Each Reader's Three Latest Loans.

One dataset, two valid questions

Suppose sales contains one row per transaction:

“Return total sales per region” requires one row per region. Detail rows should disappear. “Return every sale with its regional total” requires one row per sale. The group total is context attached to each detail row.

GROUP BY collapses to the grouping grain

The grouped report is direct:

SELECT region,
       COUNT(*) AS sale_count,
       SUM(amount) AS regional_total
FROM sales
GROUP BY region
ORDER BY region;

The five input rows become two output rows because there are two regions. Columns such as sale_id and rep cannot appear as unaggregated selections: there is no single sale or representative that describes a regional row.

Some databases allow loose grouping configurations that choose an arbitrary value for a non-grouped column. Do not rely on that. It is logically ambiguous, non-portable, and a warning sign in review.

A window aggregate preserves detail rows

The same sum becomes row-preserving when used with OVER:

SELECT sale_id,
       rep,
       region,
       amount,
       SUM(amount) OVER (PARTITION BY region) AS regional_total
FROM sales
ORDER BY sale_id;

PARTITION BY region defines independent calculation groups, but it does not merge their rows. That makes percent-of-total and above-group-average questions natural:

amount * 1.0 / SUM(amount) OVER (PARTITION BY region)

That expression is a fragment, not a separate running fixture. Its point is that the denominator is available on each original sale.

Sometimes the right answer uses both

“Rank regions by total sales” has two logical stages. First, transactions collapse to one row per region. Second, those regional rows are ranked. A CTE makes the grain transition visible:

WITH regional_sales AS (
  SELECT region, SUM(amount) AS regional_total
  FROM sales
  GROUP BY region
)
SELECT region,
       regional_total,
       DENSE_RANK() OVER (ORDER BY regional_total DESC) AS sales_rank
FROM regional_sales
ORDER BY sales_rank, region;

Trying to force this into one mental step is where mistakes happen. The window sees the grouped rows produced by the query stage before it. It ranks regions, not individual transactions.

Filtering differs because evaluation order matters

Grouped aggregates are filtered with HAVING because the condition is evaluated after grouping:

GROUP BY region
HAVING SUM(amount) >= 120

Window values are not available to the same query block's WHERE clause. For “sales above their regional average,” compute the average in a CTE or subquery and filter outside:

WITH enriched AS (
  SELECT sales.*,
         AVG(amount) OVER (PARTITION BY region) AS regional_average
  FROM sales
)
SELECT *
FROM enriched
WHERE amount > regional_average;

These are syntax fragments illustrating filter placement. If a warehouse supports QUALIFY, it may shorten the window-filter form, but QUALIFY is not universal SQL. The outer-query pattern works across more interview environments.

Practical mistakes

• Grouping away columns the output needs. If every transaction must remain, a grouped summary cannot recover its identity later without joining back.
• Using a window when only a summary is needed. Repeated totals plus DISTINCT are a roundabout and fragile substitute for GROUP BY.
• Confusing window ORDER BY with result ordering. ORDER BY inside OVER defines calculation sequence. A final query ORDER BY defines display order.
• Filtering a window alias in WHERE. The alias does not exist at that stage. Use an outer query or supported QUALIFY.
• Ranking before aggregating. If the prompt ranks departments by total revenue, first create one department row, then rank those rows.
• Ignoring ties. ROW_NUMBER, RANK, and DENSE_RANK answer different ranking requirements.

Joins can change the choice by changing grain

A calculation may look like a simple group or window problem until a join multiplies the rows. Suppose each sale has several adjustment records. Joining adjustments before calculating regional revenue repeats the sale amount. Neither GROUP BY nor a window function repairs that modeling mistake automatically.

First establish one row per sale by aggregating adjustments or otherwise selecting the intended record. Then decide whether the final output collapses to regions or keeps sales. Grain is a property of the whole query pipeline, not only the line containing GROUP BY or OVER.

This is why a good solution often uses several named stages: prepare one row per entity, aggregate to the reporting grain, then apply a window for ranking or comparison. Each CTE should have a sentence describing one row. If that sentence changes unexpectedly after a join, stop and inspect cardinality.

Performance follows the same semantic choice

A grouped result can be much smaller than its input, while a window must carry every detail row through the calculation. Do not use windows merely because they look advanced. If the consumer needs only one row per group, reduction is both clearer and often less work. If detail rows are required, replacing a window with a grouped subquery and join may do more work and introduce cardinality risk. Correct shape comes first; the simpler correct shape is usually a good starting point for performance too.

A decision rule you can say in an interview

Use this short explanation:

“The requested output is one row per employee, so I cannot group employees away. I use a window average partitioned by department, which preserves every employee and gives each row the department benchmark. If the output were one row per department, I would use GROUP BY instead.”

That answer connects syntax to output grain and shows that the choice is deliberate.

For more repetitions, compare Best Recorded Time per Runner, which asks for a grouped result, with Latest Booking per Room, which requires row selection within groups. Then try Cumulative Campaign Budget, where ordering inside a window changes the calculation. The transferable question is always the same: which rows should still exist at the end?

For the formal behavior of window functions and their OVER clauses, see the current PostgreSQL window-functions documentation.