Lesson 13 � Advanced

ROW-BY-ROW
POWER BANAO.

Window functions row-by-row calculation karte hain bina group kiye � ranking, running total, moving average sab window se hota hai. Data analysis mein powerful tool hai jo tumhe super analyst bana dega.

WHY: Window functions kyun zaroori hain?

Socho tumhe har student ka rank nikalna hai marks ke hisaab se, ya har month ka running total chahiye. GROUP BY se tum rows group kar dete ho lekin original rows lost ho jaati hain. Window functions tumhe har row ke saath calculation result deta hai � bina kuch group kiye, bina rows lose kiye. Yeh SQL ka sabse powerful feature hai data analysis ke liye.

HOW: Window function ka syntax samjho

Window function ka formula hai � FUNCTION() OVER (PARTITION BY ... ORDER BY ...). OVER clause defining karta hai ki window kaise banega. PARTITION BY se groups bante hain, ORDER BY se sorting hoti hai.

1. ROW_NUMBER

Har row ko ek unique number do � sabse basic window function:

-- Students ko marks ke hisaab se rank karo
SELECT name, course, marks,
 ROW_NUMBER() OVER (ORDER BY marks DESC) as rank_num
FROM students;

-- Har course mein alag se rank karo
SELECT name, course, marks,
 ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC) as course_rank
FROM students;

2. RANK aur DENSE_RANK

Rank assign karo ties ke saath � RANK mein gap hota hai, DENSE_RANK mein nahi:

-- RANK: ties mein same rank, phir gap
SELECT name, course, marks,
 RANK() OVER (PARTITION BY course ORDER BY marks DESC) as course_rank
FROM students;

-- DENSE_RANK: ties mein same rank, koi gap nahi
SELECT name, marks,
 DENSE_RANK() OVER (ORDER BY marks DESC) as dense_rnk
FROM students;

-- Example: RANK vs DENSE_RANK
-- Marks: 100, 95, 95, 90
-- RANK: 1, 2, 2, 4 (3 skip hua)
-- DENSE_RANK: 1, 2, 2, 3 (koi skip nahi)
-- ROW_NUMBER: 1, 2, 3, 4 (sab unique)

3. LEAD aur LAG

Previous ya next row ka value access karo � sequential analysis ke liye:

-- Previous aur next row ka marks dikhao
SELECT name, marks,
 LAG(marks, 1) OVER (ORDER BY marks) as prev_marks,
 LEAD(marks, 1) OVER (ORDER BY marks) as next_marks
FROM students;

-- Month-over-month sales comparison
SELECT month, sales,
 LAG(sales, 1) OVER (ORDER BY month) as prev_month_sales,
 sales - LAG(sales, 1) OVER (ORDER BY month) as growth
FROM monthly_sales;

4. Running Total

Sum chalte chalo rows ke saath � cumulative calculation:

-- Running total: har row tak ka sum
SELECT name, marks,
 SUM(marks) OVER (ORDER BY marks) as running_total
FROM students;

-- Course-wise running total
SELECT name, course, marks,
 SUM(marks) OVER (PARTITION BY course ORDER BY marks) as course_running_total
FROM students;

5. Moving Average

Average nikalo sliding window se � trends dikhane ke liye:

-- 3-row moving average
SELECT name, marks,
 AVG(marks) OVER (ORDER BY marks ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM students;

-- Rolling 7-day average (sales example)
SELECT date, amount,
 AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as rolling_7day_avg
FROM daily_sales;

Try it: Window functions practice karo

Neeche ka editor SQL jaisa hai. Yahan apni window function query likho aur "Run Query" dabao. Screen par sample results dikhenge � yeh browser-based simulation hai, real database nahi.

Common window function mistakes

• OVER clause bhoolna: Window function hamesha OVER() ke saath aata hai. Sirf ROW_NUMBER() likhoge toh error aayega � ROW_NUMBER() OVER(ORDER BY marks) sahi hai.
• PARTITION BY aur ORDER BY ka confusion: PARTITION BY groups banata hai (jaise course-wise), ORDER BY sorting karta hai. Dono alag kaam karte hain � ek saath use kar sakte ho.
• Window frame na samajhna: Jab ORDER BY hota hai toh default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW hota hai. Moving average ke liye explicit ROWS BETWEEN likho.
• ROW_NUMBER vs RANK ka confusion: ROW_NUMBER hamesha unique deta hai (1,2,3,4). RANK ties mein same deta hai aur gap hota hai (1,2,2,4). DENSE_RANK mein gap nahi hota (1,2,2,3).
• Aggregate function ke saath GROUP BY: Window function mein GROUP BY lagane ki zaroorat nahi � OVER clause hi window define karta hai. Agar GROUP BY lagaya toh wo alag query ban jayegi.