SQL Data Analytics • Lesson 18
Advanced Window Functions
Go beyond ROW_NUMBER() with RANK(), DENSE_RANK(), LAG() and LEAD() for deeper analytical comparisons.
0 of 8 concepts completed
Complete the activity and related MCQ to unlock the next concept.
RANK() Handles Ties with Gaps
RANK() gives tied values the same rank and leaves gaps afterward. If two rows tie at rank 2, the next row receives rank 4 — not 3.
Interactive Data
Sales table
Six sales with deliberate ties at ₹27,000 and ₹18,000 — the ties are what make RANK, DENSE_RANK and ROW_NUMBER visibly different.
| id | salesperson | city | amount | row_number | rank | dense_rank |
|---|---|---|---|---|---|---|
| 1 | Rahul | Delhi | ₹32,000 | 1 | 1 | 1 |
| 2 | Amit | Delhi | ₹27,000 | 2 | 2 | 2 |
| 3 | Arjun | Delhi | ₹27,000 | 3 | 2 | 2 |
| 4 | Priya | Mumbai | ₹21,000 | 4 | 4 | 3 |
| 5 | Kavya | Mumbai | ₹18,000 | 5 | 5 | 4 |
| 6 | Neha | Mumbai | ₹18,000 | 6 | 5 | 4 |
LAG / LEAD view
| id | salesperson | amount | previous | next | change |
|---|---|---|---|---|---|
| 1 | Rahul | ₹32,000 | — | ₹27,000 | — |
| 2 | Amit | ₹27,000 | ₹32,000 | ₹27,000 | ₹-5,000 |
| 3 | Arjun | ₹27,000 | ₹27,000 | ₹21,000 | ₹0 |
| 4 | Priya | ₹21,000 | ₹27,000 | ₹18,000 | ₹-6,000 |
| 5 | Kavya | ₹18,000 | ₹21,000 | ₹18,000 | ₹-3,000 |
| 6 | Neha | ₹18,000 | ₹18,000 | — | ₹0 |
Learn by Seeing
Ties share a rank, gaps appear
RANK() OVER ( ORDER BY amount DESC )
Amit and Arjun both have ₹27,000 — they share rank 2, and the next row starts at 4.
RANK — ties share a rank, gaps appear afterwards
Rank 3 is skipped because Amit and Arjun share rank 2 — the next number jumps to 4.
Interactive Activity
How many salespeople share the same rank in this dataset?
Hint: Look for pairs of equal amounts: 27000 twice and 18000 twice.
SQL Analyst Mindset
Compare, rank and look sideways
Real analytics rarely stops at "how much". It asks "how does this compare to the previous row?", "who is the top performer in this group?", "how many rows share this rank?". Advanced window functions answer those questions in a single pass.
Rank
RANK vs DENSE_RANK vs ROW_NUMBER.
Group
PARTITION BY city or region.
Look back
LAG for the previous row.
Look ahead
LEAD for the next row.
Frequently Asked Questions
Advanced Window Functions — FAQs
Use RANK when gaps are acceptable (e.g. sports standings). Use DENSE_RANK when you want continuous numbers even with ties.
NULL — there is no previous row. You can supply a default via LAG(column, 1, default_value).
Yes. Without ORDER BY inside OVER(), the previous / next row is undefined.
Absolutely. PARTITION BY city with LAG(amount) gives you the previous sale within each city, not across the whole table.
Lesson Summary
What you learned
Ties share a rank; gaps appear after the tie.
Ties share a rank with no gaps.
Access the previous row's value.
Access the next row's value.