Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

4.4.9 · Coding › Databases

Window functions KYUN exist karte hain?

GROUP BY → N rows in, fewer rows out (collapse).
Window function → N rows in, N rows out (annotate).

Yeh akela difference 80% value hai. Ise memorise karo.

Window ki anatomy KYA hai?

Definition OVER clause

Ek window function koi bhi function hota hai jo OVER (...) ke saath aata hai. OVER clause window ko define karta hai — rows ka woh slice jo function "dekhta" hai.

func() OVER (
    PARTITION BY <cols>   -- rows ko independent groups mein split karo
    ORDER BY <cols>       -- har group ke andar ordering define karo
    <frame>               -- ROWS/RANGE BETWEEN ... (optional)
)

PARTITION BY = "har group ke liye calculation restart karo" (ek per-group reset ki tarah).
ORDER BY = "main rows mein se kis sequence mein chalta hoon" (ranking & lag/lead ke liye zaroori).
Agar tum PARTITION BY omit karo, toh poora result set ek partition hota hai.

Paanch core functions

Teeno rankers KAISE alag hain (derive karo!)

Ordered salaries (DESC) lo: 900, 900, 700, 500.

salary	ROW_NUMBER	RANK	DENSE_RANK
900	1	1	1
900	2	1	1
700	3	3	2
500	4	4	3

Derivation logic:

ROW_NUMBER bas increment karta hai: line mein position.
RANK = 1 + (tumse strictly aage rows ki sankhya). Do 900s hain → 700 ke 2 rows aage hain → rank 3.
DENSE_RANK = 1 + (tumse strictly aage distinct values ki sankhya). 700 ke aage 1 distinct value hai (900) → rank 2.

Woh formula hi definition hai — gap rule memorise karne ki zaroorat nahi.

$RANK (r) = 1 + {x : x ≺ r}, DENSE_RANK (r) = 1 + {distinct v : v ≺ r}$

Figure — Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

Worked Example 1 — Har department mein top earner

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

PARTITION BY dept KYUN? Hum chahte hain ki counter har department ke liye restart ho, toh rn=1 ka matlab hai "is dept mein top".
ORDER BY salary DESC KYUN? rn=1 highest salary pe land karna chahiye.
Subquery KYUN? Tum ==window function ko WHERE mein use nahi kar sakte== (yeh WHERE ke baad compute hoti hai). Toh ise inner query mein compute karo, bahar filter karo.

Worked Example 2 — RANK vs DENSE_RANK pitfall

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS r,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

"Top 3 salaries" RANK se KYUN break ho sakti hai? Agar do log 2nd ke liye tie karte hain, toh RANK deta hai 1,2,2,4 — toh WHERE r <= 3 sirf 3 rows return karta hai lekin woh value skip ho jaati hai jo 3rd-distinct hoti.
Yeh step KYUN matter karta hai: "Top 3 distinct salary levels" → DENSE_RANK use karo. "Top 3 log position ke hisaab se" → ROW_NUMBER use karo. Galat ranker choose karna #1 interview trap hai.

Worked Example 3 — LAG se day-over-day change

SELECT day, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_rev,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM sales;

LAG KYUN? Humein previous row ki value current row ke saath chahiye difference compute karne ke liye — exactly yahi window functions dete hain bina self-join ke.
0 default KYUN? Pehle din ka koi predecessor nahi hota; default ke bina yeh NULL hota, aur delta bhi NULL. 0 ek sane fallback hai.
ORDER BY day KYUN? Order ke bina "previous" meaningless hai — LAG window ki ordering follow karta hai.

Recall Feynman: 12-saal ke bacche ko explain karo

Socho bachche height ke hisaab se ek line mein khade hain. ROW_NUMBER bas heads count karna hai: 1, 2, 3… RANK medals deta hai — agar do bachche equally tall hain toh dono ko silver (2nd) milta hai, aur agla bacha 4th position pata hai kyunki do silvers diye gaye. DENSE_RANK ek friendlier judge hai: do silvers, lekin agla bacha phir bhi bronze (3rd) pata hai, koi skipping nahi. LAG puchh raha hai "mere aage khada bacha kitna tall hai?" aur LEAD puchh raha hai "mere peeche khada bacha kitna tall hai?" — aur yeh sab tab ho raha hai jab sab line mein khade hain (koi bhi GROUP BY ki tarah ek single blob mein merge nahi hota).

Common mistakes

Flashcards

Window function vs GROUP BY: key difference?

GROUP BY N rows ko fewer mein collapse karta hai; window function saari N rows rakhta hai aur har ek ko annotate karta hai.

OVER clause kya define karta hai?

Window — related rows ka set jo function dekhta hai, PARTITION BY, ORDER BY, aur optional frame ke zariye.

ROW_NUMBER ties 900,900,700 par?

1, 2, 3 — strict counter, ties arbitrarily break hoti hain.

RANK 900,900,700 par?

1, 1, 3 — ties rank share karte hain, phir skip karta hai.

DENSE_RANK 900,900,700 par?

1, 1, 2 — ties rank share karte hain, koi gaps nahi.

Row r ki RANK ka formula?

1 + (ordering mein r se strictly aage rows ki sankhya).

Row r ki DENSE_RANK ka formula?

1 + (r se strictly aage distinct values ki sankhya).

LAG(col, 2) kya return karta hai?

Window order mein current row se 2 rows pehle col ki value.

LEAD kya karta hai?

Ek following row se value return karta hai (n rows aage, default 1).

Window function ko WHERE mein kyun nahi rakh sakte?

Window functions logical query order mein WHERE ke baad evaluate hoti hain; subquery/CTE/QUALIFY use karo.

PARTITION BY rows remove karta hai?

Nahi — yeh sirf har group ke liye calculation reset karta hai; saari rows return hoti hain.

"Top 3 distinct salary levels" → kaun sa function?

DENSE_RANK (phir <= 3 filter karo).

LAG(col, 1, 0) mein default arg ka purpose?

Woh value jo tab return hoti hai jab koi predecessor exist nahi karta (jaise pehli row), NULL se bachne ke liye.

Connections

GROUP BY and Aggregate Functions — collapsing cousin; windows annotate karte hain iske bajaye.
Common Table Expressions (CTE) — window results par filter karne ka clean tarika.
SQL Logical Query Processing Order — explain karta hai kyun windows WHERE ke baad run hoti hain.
Self Joins — jo LAG/LEAD "previous/next row" problems ke liye replace karte hain.
Window Frames ROWS vs RANGE — running totals/moving averages par deeper control.
Subqueries and Derived Tables — windows ko filter karne ke liye wrap karna.