Introduction: Group By से आगे की दुनिया – Chapter 7 तक हमने JOIN + SUBQUERY सीखा। पर एक problem थी।
Problem: “हर Player का Kills भी चाहिए और साथ में उसका Rank भी चाहिए”
अगर GROUP BY करोगे तो player की detail गायब हो जाएगी। सिर्फ 1 row per player बचेगी।
यही problem solve करते हैं WINDOW FUNCTIONS।Window Function = Row मिटती नहीं है। साथ में Calculation भी आ जाता है।और CTE = Common Table Expression = Query को readable बनाने का तरीका। Query के अंदर Query लिखने से बचाता है।
Netflix, Amazon, Swiggy के Data Analyst रोज RANK(), LAG(), LEAD() use करते हैं। चलो सीखते हैं।
Part 1: Window Functions क्या हैं? –
Syntax का फर्क देखो:
SELECT team, AVG(kills) FROM players GROUP BY team;
Output: 10 team = 10 rows. Player की detail गई।
Window Function:
SELECT name, team, kills, AVG(kills) OVER(PARTITION BY team) as team_avg
FROM players;
Output: सारे player दिखेंगे + साथ में उनकी team का average भी।
3 Keywords याद रखो:
1.OVER() – ये बताता है ये Window Function है
2.PARTITION BY – Group किससे करना है। Group By जैसा
3.ORDER BY – Window के अंदर Sort
Part 2: सबसे जरूरी 6 Window Functions
मान लो table: sales
| date | salesman | amount |
| 2025-01-01 | Amit | 100 |
| 2025-01-02 | Amit | 200 |
| 2025-01-01 | Rahul | 150 |
1.RANK() और DENSE_RANK() – Ranking निकालना”
Top 3 Salesman कौन हैं?”
SELECT salesman, amount,
RANK() OVER(ORDER BY amount DESC) as rnk
FROM sales;
Output:
1.Rahul 200 → Rank 1
2.Amit 200 → Rank 1
3.Amit 100 → Rank 3देखो 2 लोग Rank 1 पर थे तो अगला Rank 3 हो गया। Gap आ गया।
DENSE_RANK(): Gap नहीं आएगा। 1,1,2,3
Data Science Use: Leaderboard, Top N per Category
2.ROW_NUMBER() – Unique Number देना
SELECT *, ROW_NUMBER() OVER(PARTITION BY salesman ORDER BY amount DESC) as rn
FROM sales;
हर salesman के अंदर सबसे ज्यादा sale को 1, दूसरा 2।
Use: Duplicate हटाना, Top 1 per Group निकालना
3.LAG() – पिछला Row लाना
“पिछले दिन के मुकाबले Sale कितनी बढ़ी?”
SELECT date, salesman, amount,
LAG(amount) OVER(PARTITION BY salesman ORDER BY date) as prev_day_sale
FROM sales;
Output: Amit 200 के सामने 100 आएगा।
Formula: amount – LAG(amount) = Day over Day Growth
4.LEAD() – अगला Row लाना
LEAD(amount) OVER(PARTITION BY salesman ORDER BY date)
Output: 100, 300, 450…
Finance, Stock Market में सबसे ज्यादा use।
6.AVG() OVER() – Moving Average
“पिछले 3 दिन का Average”
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Part 3: CTE – Common Table Expressions –
Subquery लिखने से Query गंदी हो जाती है। CTE उसे साफ करता है।
बिना CTE:
SELECT * FROM (SELECT team, AVG(kills) avg FROM players GROUP BY team) t WHERE avg > 50;
CTE के साथ – पढ़ने में आसान:
WITH team_avg AS (
SELECT team, AVG(kills) as avg_kills
FROM players
GROUP BY team
)
SELECT * FROM team_avg WHERE avg_kills > 50;
Syntax: WITH cte_name AS ( query ) SELECT …
CTE के 3 फायदे:
1.Readable: Step by Step Logic
2.Reuse: 1 CTE को 2 बार use कर सकते हो
3.Recursive: Tree, Hierarchy निकालने के लिएExample: Multiple CTE
Example: Multiple CTE
WITH team_stats AS (
SELECT team, AVG(kills) avg FROM players GROUP BY team
),
top_teams AS (
SELECT team FROM team_stats WHERE avg > 50
)
SELECT p.name FROM players p JOIN top_teams t ON p.team = t.team;
Part 4: Real Data Science Examples –
Example 1: E-commerce – Top 3 Products per Category
WITH ranked_products AS (
SELECT category, product, sales,
RANK() OVER(PARTITION BY category ORDER BY sales DESC) as rnk
FROM products
)
SELECT * FROM ranked_products WHERE rnk <= 3;
Logic: पहले rank दो, फिर filter करो। Group By से नहीं होगा।
Example 2: Finance – Stock Growth
SELECT date, price,
((price – LAG(price) OVER(ORDER BY date)) / LAG(price) OVER(ORDER BY date)) * 100 as pct_change
FROM stocks;
Example 3: HR – Employee Salary Percentile
SELECT name, salary,
PERCENT_RANK() OVER(ORDER BY salary) * 100 as percentile
FROM employees;
Example 4: Gaming – Streak निकालना
SELECT player, date, result,
LAG(result) OVER(PARTITION BY player ORDER BY date) as prev_result
FROM matches;
अगर prev = Win और current = Win तो 2 की streak।
Part 5: Window vs Group By vs Subquery –
| GROUP BY | WINDOW FUNCTION | |
| Rows | कम हो जाती हैं | वही रहती हैं |
| Detail | गायब | बनी रहती है |
| Use Case | Report | Analysis |
Golden Rule: Report चाहिए = Group By। Analysis चाहिए = Window Function।
Part 6: 15 Interview Questions –
1. दूसरा सबसे ज्यादा Salary वाला
2.हर Department में Top 2 Employee
3.Running Total of Sales
4.पिछले महीने के मुकाबले % Growth
5.Consecutive Login Days निकालो
6.Nth Highest Salary
7.Duplicate Row हटाओ ROW_NUMBER()
8.First और Last Order Date
9.7 Day Moving Average
10.Customer का पहला और आखिरी Product
11.Gap and Island Problem
12.Salary में 20% से ज्यादा jump
13.Sessionization Problem
14.Cohort Analysis
15.RANK() और DENSE_RANK() का फर्क
Conclusion + Key Takeaways
क्या सीखा?
1.Window Function = Row नहीं मिटती + Calculation भी मिलता है। OVER(PARTITION BY … ORDER BY …)
2.RANK, LAG, LEAD, SUM OVER – यही 4 सबसे जरूरी हैं
3.CTE = Query को साफ और Step-wise लिखो। WITH … AS
Pro Tip: Data Science में 80% query इस pattern की होती है:
WITH cte AS (…)
SELECT *, RANK() OVER(…) FROM cte
Practice: LeetCode SQL Window Function 50 और StrataScratch करो।
अगला Chapter: Chapter 9 – “SQL for Data Analysis: Case Studies”
Real कंपनी के 5 Case Study तोड़ेंगे।