The GROUP BY Default Is Costing You Rows: When Window Functions Are the Only Sane Choice

Someone on r/Database asked the question that’s been quietly haunting SQL developers for years: “When do you actually use window functions instead of GROUP BY?” The post sat at 11 upvotes, modest by Reddit standards, but the comments revealed something far more interesting than the original question. There’s a genuine knowledge gap here, and it’s not just about syntax.

The short version: GROUP BY collapses rows. Window functions don’t. That single distinction cascades into nearly every decision about how you structure analytical queries, and getting it wrong means either writing horrific self-joins or silently losing the row-level detail your report actually needs.

Let’s dig into when you should break from the aggregation default, and why the performance implications go deeper than most developers realize.

The Core Difference: Collapsing vs. Preserving

Every SQL developer learns GROUP BY early. It’s the hammer you reach for whenever someone says “aggregate.” But the moment you need group-level information alongside individual rows, GROUP BY forces you into a corner.

As one commenter on the thread put it: “Group by collapses everything into a single row per group which is great when you actually want that. Window functions are for when you need the group-level info but still want to keep all your rows intact.”

That’s the entire thesis in one sentence. The question is whether your query actually wants collapsed output or whether you’re just defaulting to it because it’s familiar.

Consider this scenario from a practical guide on SQL window functions: a hospital manager wants to rank visits by total bill amount. A normal GROUP BY aggregation can calculate totals or averages, but it collapses multiple visits into a single result. The window function approach keeps every visit visible while computing the ranking:

SELECT
    visit_id,
    total_bill_kes,
    RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits_fact;

Every row stays. The rank appears alongside the detail. No self-join required.

The Running Total Problem: GROUP BY’s Ugliest Failure Mode

If there’s one “lightbulb moment” that converts developers to window functions, it’s running totals. The comment thread on the Reddit post called it out explicitly: “Can’t do that with group by without joining back to the original table which gets ugly fast. With sum() over you just have it in one pass.”

And “ugly fast” undersells it. To compute a running total with GROUP BY, you’d need to:

1. Aggregate the data into date buckets
2. Self-join back to compute cumulative sums
3. Pray the join logic handles edge cases correctly

The window function version is a single pass:

SELECT
    sale_month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY sale_month
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS running_total
FROM monthly_sales;

This isn’t just cleaner, it’s fundamentally different in how the database executes it. The window function scans once, computes the cumulative sum in order, and returns. The self-join approach requires the database to materialize intermediate results and join them back, which is exactly the kind of query that causes performance reviews to turn hostile.

When Window Functions Win: A Decision Framework

The practical question isn’t “which is better”, it’s “what does my query actually need?” Here’s a decision framework based on what the research consistently shows:

The window functions skill documentation puts it even more directly: use windows when “a query needs rankings, running totals, period-over-period changes, latest row per key, streaks, or when someone asks to replace a self-join or correlated subquery with a window.”

The “Top N Per Group” Case That Confuses Everyone

Let’s tackle the classic confusion point: finding the highest salary per department. You can do this with GROUP BY, but only if you want just the salary number. The moment you want the employee’s name alongside it, you need either a subquery, a self-join, or a window function.

The window function approach uses ROW_NUMBER() or RANK(), depending on how you want ties handled:

WITH ranked_employees AS (
    SELECT
        first_name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE salary_rank <= 3;

DENSE_RANK() assigns the same rank to equal salaries without skipping numbers afterward, which means you might get more than three employees per department if there are ties. ROW_NUMBER() guarantees exactly N rows but breaks ties arbitrarily. RANK() includes ties but leaves gaps in the numbering.

The choice matters, and this guide on SQL queries highlights exactly this distinction, it’s not just syntax trivia, it changes the semantics of your result set.

The Default Frame Trap: Where Window Functions Go Wrong

Here’s where I’ll push back on the window function evangelists: they’re powerful, but the defaults will bite you. The codexskills.dev documentation warns about this explicitly, the default frame when you use ORDER BY inside OVER() is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and that causes two serious bugs:

1. LAST_VALUE() returns the current row’s value, not the partition’s last value, because the default frame stops at the current row’s peers.
2. Running totals with RANGE treat equal-timestamp rows as peers, giving them all the same summed total instead of a row-by-row progression.

The fix is to always write your frame explicitly:

SELECT
    visit_id,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        ORDER BY total_bill_kes, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS highest_bill_in_order
FROM visits_fact;

The default frame trap is exactly the kind of silent bug that makes developers distrust window functions. The query runs, returns plausible numbers, and the numbers are wrong. This is also why this deep dive into SQL understanding beyond syntax argues that memorizing syntax patterns without understanding execution semantics leads exactly to these kinds of failures.

Performance: When Windows Actually Beat GROUP BY

The performance picture is more nuanced than most blog posts admit. Window functions aren’t automatically faster, they’re different. Here’s what the research shows:

The good news: A well-written window query “will smoke any other way to get different grain aggregations or rows data into a row”, as one commenter on the original thread put it. When you need multiple levels of aggregation alongside row detail, windows eliminate the repeated group-and-join dance.

The bad news: Each distinct PARTITION BY/ORDER BY combination needs its own sort. Multiple windows mean multiple sorts. On large tables, that’s not free.

The practical advice:

• Reuse window definitions with the WINDOW clause instead of repeating OVER() clauses
• An index on (partition_cols, order_cols) lets PostgreSQL and MySQL skip the sort entirely
• Filter rows in WHERE before windowing whenever the filter doesn’t change the window’s meaning
• In warehouses like BigQuery or Snowflake, a window over a massive unpartitioned set funnels through one worker, partition by something with high cardinality

The “Information Loss” Argument

One commenter on the Reddit thread summed up the GROUP BY tradeoff in four words: “You lose information with a group by. You don’t with windowing/ranking.”

That’s the real argument. GROUP BY is destructive, it reduces N rows to M groups, and whatever row-level detail you had is gone. If you need it back, you’re joining back to the source table, which doubles your I/O.

Window functions preserve the grain. You can always aggregate later with another query, but you can’t un-collapse rows.

The Historical Table Problem

Another commenter raised the scenario that genuinely separates serious practitioners from novices: historical tables where you need the current state and what it was prior. “There are ways to do this without window functions but a window func makes this trivial.”

The example: “You want to know how each store’s sales compare to its region for that month or the national average, maybe you want to be fancy and give a little month over month or ‘this quarter last year.'”

Without windows, you’d aggregate the table N times and join it back to itself. With windows, you add three columns and filter for the rows you need. The codexskills.dev skill documentation calls this the “period-over-period” pattern and provides the exact template:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly_revenue;

The catch: LAG() looks at the previous row, not the previous month. If months are missing, the “previous” row might be two months back. The fix requires generating a complete calendar first, a date spine, and left-joining to it. This is the kind of nuance that separates competent SQL from expert SQL.

When GROUP BY Still Wins: The Honest Assessment

I’ve been pushing window functions hard, so let me be honest about when GROUP BY remains the right call.

When the grain IS the group: If you genuinely want one row per department, per month, or per category, GROUP BY is simpler, more readable, and more predictable. Using window functions here would be using a chainsaw to cut butter.

When you need HAVING: If you’re filtering on the aggregate result itself, departments with average salary above $80K, customers with more than 10 orders, the HAVING clause is purpose-built for this. Window functions require wrapping in a CTE or subquery.

When you’re detecting duplicates: The canonical duplicate detection query is GROUP BY email HAVING COUNT(*) > 1. It’s concise, readable, and exactly what you need. Don’t overthink it.

When mixing with GROUP BY: The codexskills documentation notes something subtle: “Mixing window functions and GROUP BY: the window runs on grouped rows, so SUM(SUM(amount)) OVER (...) is valid and often what you want.” The order of operations matters, windows compute after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT.

The Readability Argument That Actually Matters

Let me address the elephant in the room: window functions scare people. The syntax is unfamiliar, the frame semantics are confusing, and the default behaviors can be surprising. Code reviews often go poorly when someone drops a window function into a query that “just worked” with GROUP BY.

But here’s the thing: the GROUP BY version that “just works” is often 60 lines of self-joins and subqueries that re-implement what a window function does in 10 lines. Which is more readable?

A deep understanding of SQL execution semantics beats syntax fluency precisely because it lets you recognize when you’re fighting the language instead of using it.

The Career Angle: SQL Skills That Actually Matter

There’s a broader conversation happening about whether SQL-only skills remain valuable in 2026, and the window functions question sits squarely in the middle of it. The analysts who automate themselves out of relevance are the ones writing the same GROUP BY queries on repeat. The ones who understand when to break from aggregation defaults are the ones building the analytical layers that make pipelines actually useful.

And the shift toward SQL in data pipelines is making this distinction more important, not less. When plain SQL becomes the default for transformation layers, knowing how to express complex analytical logic efficiently in that language is a competitive advantage.

The Bottom Line

The decision between GROUP BY and window functions isn’t about which is “better”, it’s about what your query needs to produce. If you need one row per group, GROUP BY is your answer. If you need group-level calculations alongside row-level detail, window functions are the difference between a clean query and a monstrosity of self-joins.

The SQL antipatterns conversation applies here too, the wrong aggregation tool doesn’t just hurt performance, it produces wrong answers that look right.

Here’s your practical checklist:

1. Identify your output grain before writing the query. One row per group? GROUP BY. One row per source row with computed columns? Window functions.
2. State your tiebreakers explicitly. Any ORDER BY inside OVER() needs a unique tiebreaker, or ROW_NUMBER() picks arbitrarily.
3. Write frames explicitly when using aggregates or FIRST_VALUE()/LAST_VALUE(). The default RANGE frame will betray you.
4. Check your results against a GROUP BY equivalent. The codexskills documentation recommends exactly this: “On real data, totals reconcile with a plain GROUP BY query.”
5. Look at the execution plan. If you see repeated sorts, consolidate your window definitions.

The GROUP BY default is comfortable. It’s familiar. And it’s costing you rows, performance, and sanity on queries that were never meant to be aggregated in the first place.