Stop Collapsing Your Data: Why Window Functions Are a Game Changer

If you've been learning SQL for a few weeks, you're probably comfortable with GROUP BY. It's the bread and butter of data analysis. But have you ever noticed that the moment you use it, your individual rows disappear?

Today, we're going to talk about Window Functions — the secret weapon that lets you keep your data and calculate it, too.

The Problem with GROUP BY

Imagine you have a table of student exam marks. Your boss asks for a report showing each student's ID, their mark, and the class average.

If you use GROUP BY class, SQL collapses all those individual exam rows into one single row per class. You get the average, but you lose the student IDs and the individual marks.

To get both, you'd have to write a messy subquery or a complex join.

The Solution: The OVER() Clause

Window functions (using the OVER clause) perform a calculation across a set of rows, but unlike GROUP BY, they do not collapse the rows. Every record remains visible.

1. The "Magic" Average

Instead of grouping, we use PARTITION BY. Think of this as "grouping without the shrinking."

SELECT
    student_id,
    mark,
    AVG(mark) OVER (PARTITION BY class) AS class_avg
FROM exam_results;

Result:

You see Student A, their score of 85, and the class average of 78 right next to it. No data was hidden.

2. Ranking Without Gaps

Have you ever tried to rank students using just ORDER BY? It's hard to handle ties. Window functions like RANK() and DENSE_RANK() handle this automatically.

• RANK() → If two people tie for 1st, the next person is 3rd (it leaves a gap)
• DENSE_RANK() → If two people tie for 1st, the next person is 2nd (no gaps)

SELECT
    student_id,
    mark,
    DENSE_RANK() OVER (ORDER BY mark DESC) AS position
FROM exam_results;

3. Looking Back with LAG()

One of the coolest things you can do is compare a row to the one above it. This is perfect for seeing if a student improved since their last exam.

LAG(mark) reaches back to the previous record for that specific student and pulls the value forward.

SELECT
    student_id,
    mark,
    LAG(mark) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_mark
FROM exam_results;

Summary

• Use GROUP BY when you only need the high-level summary
  
  (e.g., "What was the total revenue?")

• Use Window Functions when you need to compare individual rows against a total or a previous value
  
  (e.g., "How does this student compare to the average?")

Once you start using OVER(), you'll realize that SQL isn't just about fetching data — it's about telling a story with it.