Coding Interview Preparation on Telegram: “📊 SQL SATURDAY #5

📊 SQL SATURDAY #5 - Window Functions (The Interview Differentiator)

If GROUP BY collapses rows, window functions let you calculate aggregates WITHOUT collapsing them - you keep every row, plus a calculated value alongside it. This is one of the clearest signals of SQL seniority in interviews.


sales
+----+------------+--------+
| id | department | amount |
+----+------------+--------+
| 1  | Eng        | 100    |
| 2  | Eng        | 200    |
| 3  | Sales      | 150    |
| 4  | Sales      | 300    |

Question: Show each sale alongside the total for its department, without collapsing rows.

sql
SELECT id, department, amount,
       SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM sales;


+----+------------+--------+------------+
| id | department | amount | dept_total |
+----+------------+--------+------------+
| 1  | Eng        | 100    | 300        |
| 2  | Eng        | 200    | 300        |
| 3  | Sales      | 150    | 450        |
| 4  | Sales      | 300    | 450        |

PARTITION BY is like GROUP BY, but it doesn't collapse the rows - every row keeps its own identity while gaining group-level context.

Another classic interview favorite - ranking within groups:

sql
SELECT id, department, amount,
       RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dept_rank
FROM sales;

⚠️ Interview trap: RANK() vs DENSE_RANK() vs ROW_NUMBER() - know the difference cold:
🔹 ROW_NUMBER() - always unique, 1,2,3,4, even with ties
🔹 RANK() - ties share a rank, but the NEXT rank skips (1,1,3,4)
🔹 DENSE_RANK() - ties share a rank, next rank does NOT skip (1,1,2,3)

Interviewers love asking you to explain this exact difference, then predict output on a tied dataset.

Which of the three ranking functions do you use most in your actual job? 👇