SQL Data Analytics • Lesson 17
Window Functions — Basics
Learn how SQL calculates rankings, row numbers and partition-based analysis without collapsing your original rows.
0 of 8 concepts completed
Complete the activity and related MCQ to unlock the next concept.
The Big Idea Behind Windows
A window function calculates across related rows but keeps every original row in the result. Unlike GROUP BY, which collapses rows into groups, window functions preserve the detail you are analyzing.
Interactive Data
Sales table
Six individual sales — the raw detail that window functions operate on without collapsing.
| id | salesperson | city | amount | row_number (desc) | city_rank |
|---|---|---|---|---|---|
| 3 | Amit | Delhi | ₹32,000 | 1 | 1 |
| 5 | Arjun | Delhi | ₹27,000 | 2 | 2 |
| 1 | Rahul | Delhi | ₹25,000 | 3 | 3 |
| 6 | Kavya | Mumbai | ₹21,000 | 4 | 1 |
| 2 | Priya | Mumbai | ₹18,000 | 5 | 2 |
| 4 | Neha | Mumbai | ₹15,000 | 6 | 3 |
Learn by Seeing
GROUP BY vs window
GROUP BY → collapses rows into groups OVER() → keeps rows, adds a calculation
GROUP BY reduces rows. Window functions add analytical columns without removing any row.
GROUP BY vs window — toggle to compare
Rows returned
2 (one per city)
GROUP BY collapses rows into one per group.
Interactive Activity
What does a window function allow you to do?
Hint: Compare it to GROUP BY, which removes the detail.
SQL Analyst Mindset
Add insight without losing detail
Aggregation compresses rows. Window functions enrich them. When the business wants "the top performer in each region" or "the rank of each sale", window functions return the answer without losing the underlying transaction detail.
Define window
OVER() plus optional PARTITION BY.
Order rows
ORDER BY controls ranking.
Apply function
ROW_NUMBER, RANK, SUM, ...
Keep rows
Every detail row stays in the result.
Frequently Asked Questions
Window Functions — FAQs
GROUP BY collapses rows into one row per group. OVER() keeps every row and adds a computed column alongside it.
No. Window functions run after WHERE. Wrap the query in a CTE or subquery if you need to filter on the window result.
ROW_NUMBER gives every row a unique number (ties get different numbers). RANK gives equal values the same rank and skips the next numbers.
No. PARTITION BY groups rows for the window calculation but does not change the final row order — use a top-level ORDER BY for that.
Lesson Summary
What you learned
Defines the window a function operates across.
Assigns a unique sequential number per row.
Controls ranking direction.
Restarts the window per group, enabling per-group ranking.