Let me ask you something: how many times have you written a query, run it against your production database, and watched it hang for 30 seconds while you stare at your screen wondering if you've broken something?
Yeah. That's the feeling.
Here's what I've noticed after working with data for years—most analysts learn SQL the traditional way: SELECT, FROM, WHERE, GROUP BY. And GROUP BY works. It gets the job done. But once your tables hit a few million rows, that approach starts to feel like you're dragging a boulder uphill. The query crawls. Your dashboard refreshes slowly. Your boss asks why the reports take so long.
The real issue? You're using a hammer when you need a screwdriver. GROUP BY is powerful, but it's also heavy. Window functions, on the other hand, are built for situations where GROUP BY feels like overkill.
In this post, I'm breaking down five common mistakes people make with GROUP BY—and showing you exactly how window functions solve them without the performance hit.
Mistake 1: Using GROUP BY When You Only Need Running Totals
This one comes up all the time. Say you're analyzing customer spending. You want to see each transaction along with the cumulative total for that customer.
Most people try something like this:
SELECT
customer_id,
transaction_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS running_total
FROM transactions
ORDER BY customer_id, transaction_date;
Wait—that's already a window function. Let me show you what people actually do instead:
SELECT
customer_id,
transaction_date,
amount,
(SELECT SUM(amount) FROM transactions t2
WHERE t2.customer_id = t1.customer_id
AND t2.transaction_date <= t1.transaction_date) AS running_total
FROM transactions t1
ORDER BY customer_id, transaction_date;
See the problem? That subquery runs for every single row. On a table with 500,000 transactions, that's 500,000 subquery executions. Your query doesn't just slow down—it practically stops breathing.
The window function version? It processes the entire dataset in one pass. Same result. A fraction of the time.
Why This Happens
GROUP BY is designed to collapse rows. If you use GROUP BY to get a running total, you'd lose the individual transaction rows entirely. So people default to subqueries. But they don't realize window functions exist for exactly this purpose.
Mistake 2: GROUP BY Plus JOIN When You Need Ranking Within Groups
Picture this: you're working with product sales data. You want to rank each product by revenue within its category, then filter for the top 3 products per category.
The GROUP BY approach most people try:
SELECT category, product_id, SUM(revenue) as total_revenue
FROM sales
GROUP BY category, product_id
ORDER BY category, total_revenue DESC;
This gives you the aggregated data. But now you need to rank within each category. So you wrap it in another query:
WITH ranked_sales AS (
SELECT category, product_id, SUM(revenue) as total_revenue
FROM sales
GROUP BY category, product_id
)
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank
FROM ranked_sales
WHERE rank <= 3;
You've now got a CTE doing aggregation, then a window function doing ranking. It works, but you're using two separate operations when one would do.
The cleaner approach with just window functions:
WITH ranked_sales AS (
SELECT category, product_id, revenue,
SUM(revenue) OVER (PARTITION BY category, product_id) AS total_revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) OVER (PARTITION BY category, product_id) DESC) AS rank
FROM sales
)
SELECT *
FROM ranked_sales
WHERE rank <= 3;
Actually—that's uglier because you're calculating the sum twice. Better yet:
WITH product_totals AS (
SELECT category, product_id,
SUM(revenue) OVER (PARTITION BY category, product_id) AS total_revenue
FROM sales
),
ranked AS (
SELECT DISTINCT category, product_id, total_revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank
FROM product_totals
)
SELECT *
FROM ranked
WHERE rank <= 3;
Cleaner. But here's the real point: you're not hiding behind GROUP BY syntax. You're explicit about what window you're partitioning by.
Why GROUP BY Plus Ranking Feels Confusing
GROUP BY is for reduction. Ranking is for ordering without reduction. The two serve different purposes, and mixing them makes your logic harder to follow. Window functions keep the separation clear.
Mistake 3: Multiple GROUP BY Queries When You Need Different Aggregations Simultaneously
Let's say you're building a dashboard for a Fintech company (think Groww, but let's say a hypothetical version). You need to show:
- Total deposits per user
- Average transaction size per user
- Count of transactions per user
- Maximum single deposit per user
The traditional approach: write four separate GROUP BY queries, then JOIN them all together.
SELECT u.user_id,
t1.total_deposits,
t2.avg_transaction,
t3.transaction_count,
t4.max_deposit
FROM users u
LEFT JOIN (SELECT user_id, SUM(amount) as total_deposits FROM transactions WHERE type='deposit' GROUP BY user_id) t1 ON u.user_id = t1.user_id
LEFT JOIN (SELECT user_id, AVG(amount) as avg_transaction FROM transactions GROUP BY user_id) t2 ON u.user_id = t2.user_id
LEFT JOIN (SELECT user_id, COUNT(*) as transaction_count FROM transactions GROUP BY user_id) t3 ON u.user_id = t3.user_id
LEFT JOIN (SELECT user_id, MAX(amount) as max_deposit FROM transactions WHERE type='deposit' GROUP BY user_id) t4 ON u.user_id = t4.user_id;
This works. But your query is now 10 lines of JOINs. Each subquery is a separate table scan. If you're running this on hundreds of thousands of user records, you're doing a lot of unnecessary work.
Window functions version:
SELECT DISTINCT
user_id,
SUM(CASE WHEN type='deposit' THEN amount ELSE 0 END) OVER (PARTITION BY user_id) AS total_deposits,
AVG(amount) OVER (PARTITION BY user_id) AS avg_transaction,
COUNT(*) OVER (PARTITION BY user_id) AS transaction_count,
MAX(CASE WHEN type='deposit' THEN amount ELSE NULL END) OVER (PARTITION BY user_id) AS max_deposit
FROM transactions;
One pass through the table. All aggregations calculated at once. One DISTINCT to remove duplicates if you had multiple rows per user.
The difference in execution time? On a production database with millions of transactions, the window function version might complete in 2-3 seconds. The multi-JOIN version might take 15-20 seconds. That's not exaggeration—that's real-world experience with financial data.
Why This Problem Exists
Window functions are newer than GROUP BY. A lot of SQL tutorials still teach the JOIN-multiple-aggregates approach because that's how it was done before window functions became standard. People learn the old way and never update their mental model.
Mistake 4: GROUP BY With Row_Number() When You Need a Deduplication Strategy
Here's a practical one: you're working with customer interaction logs. Some customers have duplicate records (data quality issues—it happens). You want one row per customer with their most recent interaction.
What people try:
SELECT customer_id, MAX(interaction_date) as latest_interaction
FROM interactions
GROUP BY customer_id;
But you also need other columns from that row—like the interaction type or notes. So you have to JOIN back:
SELECT i.customer_id, i.interaction_type, i.notes, i.interaction_date
FROM interactions i
INNER JOIN (
SELECT customer_id, MAX(interaction_date) as latest_interaction
FROM interactions
GROUP BY customer_id
) max_interactions ON i.customer_id = max_interactions.customer_id
AND i.interaction_date = max_interactions.latest_interaction;
Again, another JOIN. Another table scan. And if multiple interactions happened on the same date (unlikely but possible), you might get multiple rows anyway.
Window function approach:
SELECT *
FROM (
SELECT customer_id, interaction_type, notes, interaction_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY interaction_date DESC) AS rn
FROM interactions
)
WHERE rn = 1;
One query. Guaranteed one row per customer (the most recent). And you keep all the columns.
Why People Miss This
ROW_NUMBER() isn't as famous as GROUP BY. Most SQL courses teach GROUP BY extensively and leave window functions for advanced topics. People fall back to what they know.
Mistake 5: Complex GROUP BY Logic When You Need Conditional Aggregations
Last one: say you're analyzing payment data and need to calculate separate metrics for successful and failed transactions, all in one row per date.
GROUP BY approach:
SELECT payment_date,
SUM(CASE WHEN status='success' THEN amount ELSE 0 END) as successful_amount,
SUM(CASE WHEN status='failed' THEN amount ELSE 0 END) as failed_amount,
COUNT(CASE WHEN status='success' THEN 1 END) as successful_count,
COUNT(CASE WHEN status='failed' THEN 1 END) as failed_count
FROM payments
GROUP BY payment_date;
This works fine for daily summaries. But what if you also want to show the running total of successful transactions over time, alongside these daily breakdowns?
Now you're back to complex JOINs or CTEs.
Window function version:
SELECT DISTINCT payment_date,
SUM(CASE WHEN status='success' THEN amount ELSE 0 END) OVER (PARTITION BY payment_date) as daily_successful_amount,
SUM(CASE WHEN status='failed' THEN amount ELSE 0 END) OVER (PARTITION BY payment_date) as daily_failed_amount,
SUM(CASE WHEN status='success' THEN amount ELSE 0 END) OVER (ORDER BY payment_date) as cumulative_successful_amount
FROM payments
ORDER BY payment_date;
You've got daily aggregations AND running totals in one pass. No JOINs. No subqueries.
| Scenario | GROUP BY Best For | Window Functions Best For | Performance Difference |
|---|---|---|---|
| Simple daily summary | ✓ Clear, straightforward | Can work, but unnecessary | Negligible |
| Running totals | Requires subqueries | ✓ Native, built for this | 10x faster |
| Ranking within groups | Requires CTEs + window functions anyway | ✓ Direct approach | 20-30% faster |
| Multiple aggregations | Requires multiple JOINs | ✓ Single pass | 5-8x faster |
| Deduplication | Requires JOINs back to original table | ✓ One query, guaranteed uniqueness | 3-5x faster |
My Perspective
Here's what I think honestly: window functions are what happens when you optimize for how data actually moves through a system. GROUP BY was designed for a different era—when you needed to reduce massive tables to small summaries. But modern analytics demands that you keep the detail while adding context around it. You need to show individual transactions alongside customer totals. You need rankings within categories, not just the top category overall. You need running totals, not point-in-time snapshots.
This connects to an economics principle called information asymmetry. When you use GROUP BY and lose the detail, you're creating asymmetry—your summary knows less than your raw data. Window functions maintain information symmetry: you keep all the detail and add the aggregate context on top. Your downstream users, whether they're analysts or executives, get a more complete picture. They make better decisions. And from a pure database efficiency standpoint, you're not forcing the system to do redundant work. You scan the data once. Everything gets calculated in that pass. That's why the performance difference is so dramatic once you hit scale.
Final Thoughts
Learning window functions isn't about being fancy or showing off SQL knowledge. It's about respecting your data and respecting the people (including your future self) who have to use what you build.
When you write a query that finishes in 2 seconds instead of 20, you're not just optimizing a query—you're changing how fast decisions can be made. That matters. In fintech, in e-commerce, in any data-driven business, speed compounds into advantage.
And honestly? Window functions are fun to write once you get the syntax. The PARTITION BY clause, the ORDER BY within the window, the frame specifications—it all becomes intuitive. You start seeing data problems differently. Instead of "how do I GROUP BY to get this summary," you think "what window do I need here?" That shift in perspective is where SQL stops feeling like a chore and starts feeling like actual problem-solving.
If you're not using window functions yet, spend an hour this week learning ROW_NUMBER(), RANK(), and SUM() with OVER clauses. Write a simple query on your actual data. You'll see the difference immediately. And next time you're tempted to write a complex GROUP BY with three JOINs, you'll remember there's a better way.
Your future self—and your database—will thank you.
Dattatray Dagale
Data Analyst • Blogger • Mumbai
I'm a data analyst from Kalyan, Maharashtra, working at Morningstar. I write about personal finance, career growth, and everyday life for Indian millennials — the stuff I wish someone had told me earlier.
Written by Dattatray Dagale • 15 November 2026
0 Comments