Feature	GROUP BY	Window Function
Rows Output	Collapses — one row per group	Retains — all individual rows
Detail Level	Summary only	Row detail + summary together
Use Case	Aggregated reports	Row-level analytics with context
Example	"Total sales per region" (5 rows)	"Each sale + its region's total" (100 rows)

⚠️ Common Mistake: Window functions ko GROUP BY ke saath confuse karna. GROUP BY rows COLLAPSE karta hai — details khatam. Window function rows RETAIN karta hai — details ke saath aggregate. Interview mein pehla question — "difference between GROUP BY and Window Function?"

Basic Syntax:
window_function() OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3 [ASC|DESC], ...]
[frame_clause]
)

Parts Explained:
• PARTITION BY → divide rows into groups (like GROUP BY but retains rows)
•
ORDER BY → sort rows within each partition
• frame_clause → subset of window (advanced — covered later)

3 Common Patterns:

Pattern 1 — Entire table as window:
SUM(amount) OVER ()

Pattern 2 —
Group by region:
SUM(amount) OVER (PARTITION BY region)

Pattern 3 — Running total by date:
SUM(amount) OVER (
PARTITION BY region
ORDER BY sale_date
)

-- Example 1: OVER() empty — entire result set as window
SELECT product_name, amount, SUM(amount) OVER () AS total_sales,
AVG(amount) OVER () AS avg_sales
FROM sales;

-- Example 2: OVER(PARTITION BY) — grouped windows
SELECT product_name, category, amount, SUM(amount) OVER ( PARTITION BY category ) AS category_total
FROM sales;

-- WITH GROUP BY (rows collapse!)
SELECT category, SUM(amount) AS category_total
FROM sales
GROUP BY category;

-- WITH WINDOW FUNCTION (rows retained!)
SELECT
product_name,
category,
amount,
SUM(amount) OVER (
PARTITION BY category
) AS category_total

FROM sales;

⚠️ Common Mistakes:
• OVER() clause bhoolna — SUM(amount) aggregate hai, SUM(amount) OVER() window function hai. OVER() zaroori hai window function banane ke liye.
• PARTITION BY aur GROUP BY confuse karna — GROUP BY rows collapse karta hai, PARTITION BY rows retain karta hai window ke andar.

-- Create Sales table
CREATE TABLE sales ( sale_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), region VARCHAR(50), salesperson VARCHAR(100), amount INT, sale_date DATE );

-- Insert sample data

INSERT INTO sales
(sale_id, product_name, category, region, salesperson, amount, sale_date)

VALUES
(1, 'Laptop', 'Electronics', 'North', 'Ravi', 55000, '2026-01-05'),
(2, 'Monitor', 'Electronics', 'South', 'Sneha', 18000, '2026-01-10'),
(3, 'Keyboard', 'Accessories', 'North', 'Ravi', 3500, '2026-01-15'),
(4, 'Mouse', 'Accessories', 'South', 'Sneha', 2000, '2026-01-20'),
(5, 'Chair', 'Furniture', 'East', 'Arjun', 8000, '2026-02-05'),
(6, 'Desk', 'Furniture', 'East', 'Arjun', 12000, '2026-02-10'),
(7, 'Laptop', 'Electronics', 'West', 'Priya', 60000, '2026-02-15'),
(8, 'Tablet', 'Electronics', 'North', 'Ravi', 25000, '2026-02-20'),
(9, 'Headphones','Accessories', 'West', 'Priya', 4500, '2026-03-05'),
(10, 'Sofa', 'Furniture', 'South', 'Sneha', 35000, '2026-03-10'),
(11, 'Monitor', 'Electronics', 'East', 'Arjun', 22000, '2026-03-15'),
(12, 'Keyboard', 'Accessories', 'West', 'Priya', 3500, '2026-03-20');

SELECT *
FROM sales
ORDER BY sale_id;

📋 Dataset Summary:
• Categories: Electronics, Accessories, Furniture
• Regions: North, South, East, West
• Salespersons: Ravi, Sneha, Arjun, Priya
• Date Range: January to March 2026
• Amount Range: ₹2,000 to ₹60,000
Perfect for demonstrating rankings, groupings, time-based calculations!

ROW_NUMBER() OVER ( [PARTITION BY column_name] ORDER BY column_name [ASC | DESC] )
Rules:
•
ORDER BY is REQUIRED — without it, row
order is unpredictable
• PARTITION BY is OPTIONAL — if omitted, entire table is one partition
• Returns unique 1, 2, 3, 4... within each partition
• Ties in
ORDER BY are broken arbitrarily (unique numbers regardless)

-- Assign row numbers to all sales ordered by amount (highest first)
SELECT ROW_NUMBER() OVER ( ORDER BY amount DESC ) AS row_num,
product_name, category, amount
FROM sales;

-- Rank products within each category (highest amount first)
SELECT category, product_name, amount, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY amount DESC ) AS rank_in_category
FROM sales
ORDER BY category, rank_in_category;

-- Find top 2 products in each category by amount WITH ranked_sales AS (
SELECT category, product_name, amount, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY amount DESC ) AS rn
FROM sales ) SELECT category, product_name, amount, rn AS position
FROM ranked_sales
WHERE rn <= 2
ORDER BY category, rn;

⚠️ Common Mistakes:
• ORDER BY skip karna — ROW_NUMBER() OVER() without ORDER BY unpredictable results deta hai. Hamesha ORDER BY specify karo.
• Top N filtering WHERE mein directly likhna — WHERE ROW_NUMBER() OVER (...) <= 2 ERROR! Window functions WHERE clause mein directly use nahi hote — pehle CTE ya subquery mein calculate karo, phir outer query mein WHERE lagao.

Function	Tie Handling	Sequence Example
ROW_NUMBER()	Always unique — no ties	1, 2, 3, 4, 5
RANK()	Same rank for ties, GAPS after	1, 2, 2, 4, 5 (skips 3)
DENSE_RANK()	Same rank for ties, NO gaps	1, 2, 2, 3, 4 (no skip)

-- Compare ROW_NUMBER, RANK, DENSE_RANK on same data
SELECT product_name, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
RANK() OVER (ORDER BY amount DESC) AS rank_num, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales
ORDER BY amount DESC;

Notice the difference at Mouse:
• row_num: 12 (always unique)
• rank_num: 12 (skipped 11 because tie above)
• dense_rank: 11 (no gap)

-- Find the 3rd highest sale amount (using DENSE_RANK) WITH ranked_sales AS (
SELECT product_name, amount, DENSE_RANK() OVER ( ORDER BY amount DESC ) AS rnk
FROM sales ) SELECT product_name, amount
FROM ranked_sales
WHERE rnk = 3;

DENSE_RANK is safer than RANK for "Nth highest" queries
— handles ties without skipping ranks!

-- Rank salespersons by total sales within each region
SELECT region, salesperson, SUM(amount) AS total_sales, DENSE_RANK() OVER ( PARTITION BY region ORDER BY SUM(amount) DESC ) AS rank_in_region
FROM sales
GROUP BY region, salesperson
ORDER BY region, rank_in_region;

Each region has one top performer!

⚠️ Common Mistakes:
• RANK() ka use "Nth highest" queries mein — agar ties hain toh gap ki wajah se rank N kabhi exist hi nahi karega. Example: 1, 2, 2, 4 mein "rank = 3" query kuch return nahi karegi! DENSE_RANK safer hai — 1, 2, 2, 3 pattern.
• Kaunsa use kab karein? Sports rankings (Olympic medals) → RANK. Nth highest queries → DENSE_RANK. Serial numbers/pagination → ROW_NUMBER.

-- Divide sales into 4 quartiles based on amount
SELECT product_name, amount, NTILE(4) OVER ( ORDER BY amount DESC ) AS quartile
FROM sales
ORDER BY amount DESC;

12 rows ÷ 4 buckets = 3 rows per bucket ✅

-- Find TOP 25% sales (highest quartile only) WITH quartiles AS (
SELECT product_name, category, region, amount, NTILE(4) OVER ( ORDER BY amount DESC ) AS quartile
FROM sales ) SELECT product_name, category, region, amount
FROM quartiles
WHERE quartile = 1
ORDER BY amount DESC;

Top 25% high-value sales — target for premium marketing!

-- Categorize sales into A (top), B (middle), C (bottom) tiers
SELECT product_name, category, amount, CASE NTILE(3) OVER (ORDER BY amount DESC)
WHEN 1
THEN 'A - High Value'
WHEN 2
THEN 'B - Medium Value'
WHEN 3
THEN 'C - Low Value'
END AS tier
FROM sales
ORDER BY amount DESC;

Product tier categorization — ready for marketing strategy!

⚠️ Common Mistakes:
• NTILE ke saath ORDER BY zaroori hai — bina order ke random buckets banenge.
• NTILE ties handle nahi karta smartly — same amount wale rows different buckets mein ja sakte hain (yeh common gotcha hai). Agar ties matter karti hain toh DENSE_RANK better hai NTILE se.
• Bucket count sensibly choose karo — NTILE(4) 12 rows par sahi, but NTILE(10) sirf 12 rows par uneven distribution dega.

📘 Definition: LAG(column, offset, default) accesses a value from a PREVIOUS row without a self-join. LEAD(column, offset, default) accesses a value from the NEXT row. Both take 3 parameters: the column to fetch, how many rows back/forward (default 1), and a default value if no such row exists (default NULL). These are essential for period-over-period comparisons — MoM growth, YoY comparison, previous vs current.

🎯 Explanation: LAG = "pichli row ki value dikhao" (jaise pichle month ki sales). LEAD = "agli row ki value dikhao" (next month ki sales). MoM growth calculate karne ke liye perfect — (current - LAG(current)) / LAG(current) × 100. Bina LAG/LEAD ke self-join karna padta — complex aur slow. LAG/LEAD simple aur fast hai. Data analysts ke liye must-know function — dashboards mein daily use hota hai.

📋 Syntax:

LAG(column, offset, default_value) OVER ( [PARTITION BY column_name] ORDER BY column_name )
LEAD(column, offset, default_value) OVER (
[PARTITION BY column_name]
ORDER BY column_name
)

Parameters:
• column → which column to fetch
•
offset → how many rows back/forward (default 1)
• default_value → what to return if no such row exists (default NULL)

💻 Example 1 — Basic LAG and LEAD:

-- Show current, previous, and next sale for each row
SELECT sale_id, product_name, amount, LAG(amount, 1, 0) OVER ( ORDER BY sale_id ) AS previous_amount,
LEAD(amount, 1, 0) OVER ( ORDER BY sale_id ) AS next_amount
FROM sales
ORDER BY sale_id;

💻 Example 2 — Month-over-Month Growth (Real Use Case):

-- Monthly sales with MoM growth percentage WITH monthly_sales AS (
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m') ) SELECT month, total_sales,
LAG(total_sales, 1) OVER ( ORDER BY month ) AS prev_month_sales,
ROUND( (total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 / LAG(total_sales) OVER (ORDER BY month), 2 ) AS mom_growth_pct
FROM monthly_sales
ORDER BY month;

💻 Example 3 — LAG with PARTITION BY (Per Category):

-- Previous sale amount within same category
SELECT category, product_name, sale_date, amount, LAG(amount, 1) OVER ( PARTITION BY category ORDER BY sale_date ) AS prev_sale_in_category
FROM sales
ORDER BY category, sale_date;

📘 Definition: FIRST_VALUE(column) returns the FIRST value in an ordered window/partition. LAST_VALUE(column) returns the LAST value in an ordered window/partition. Both are useful for comparing individual rows against the extreme values (first/last) of their group. IMPORTANT: LAST_VALUE requires an explicit frame clause — without it, you get the wrong result (returns current row instead of actual last).

🎯 Explanation: FIRST_VALUE = "har row ke saath us group ki pehli value bhi dikhao." LAST_VALUE = "aakhri value dikhao." Real use: "Har category mein highest priced product ka naam dikhao har row ke saath." "Employee ka salary + department ka top earner ka salary — compare karo." LAST_VALUE ke saath ek gotcha hai — default frame current row tak hai, isliye actual last chahiye toh full frame specify karna padta hai: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

💻 Example 1 — Highest Amount Product per Category:

-- Show each product with the TOP selling product name in its category
SELECT category, product_name, amount, FIRST_VALUE(product_name) OVER ( PARTITION BY category ORDER BY amount DESC ) AS top_product,
FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY amount DESC ) AS top_amount
FROM sales
ORDER BY category, amount DESC;

💻 Example 2 — LAST_VALUE Gotcha (Wrong vs Right):

-- WRONG WAY (returns current row, not actual last!)
SELECT category, product_name, amount, LAST_VALUE(product_name) OVER ( PARTITION BY category ORDER BY amount DESC ) AS wrong_last
FROM sales;

-- CORRECT WAY (with explicit frame)
SELECT
category,
product_name,
amount,
LAST_VALUE(product_name) OVER (
PARTITION BY category
ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS correct_lowest

FROM sales

ORDER BY category, amount DESC;

💻 Example 3 — Compare with Highest/Lowest in Category:

-- Show each product's amount + highest + lowest in its category
SELECT category, product_name, amount, FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY amount DESC ) AS highest_in_cat,
LAST_VALUE(amount) OVER ( PARTITION BY category ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_in_cat,
FIRST_VALUE(amount) OVER ( PARTITION BY category ORDER BY amount DESC ) - amount AS gap_from_top
FROM sales
ORDER BY category, amount DESC;

📘 Definition: NTH_VALUE(column, N) returns the value of the specified column from the Nth row within a window/partition. Unlike FIRST_VALUE (1st row) and LAST_VALUE (last row), NTH_VALUE gives you flexibility to fetch ANY specific position. It's less commonly used but powerful for scenarios like "show me the 2nd or 3rd highest value in each group."

🎯 Explanation: NTH_VALUE = specific position ka value nikaalo. FIRST_VALUE = 1st, LAST_VALUE = last, NTH_VALUE(col, 2) = 2nd, NTH_VALUE(col, 3) = 3rd, etc. Real use: "Har category ka 2nd highest product dikhao" — perfect for medal-style rankings. Same LAST_VALUE gotcha — explicit frame ki zaroorat padti hai proper output ke liye.

💻 Example — Show 2nd Highest in Each Category:

-- Show 1st, 2nd, and 3rd highest products in each category
SELECT DISTINCT category, FIRST_VALUE(product_name) OVER ( PARTITION BY category ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS top_1st,
NTH_VALUE(product_name, 2) OVER ( PARTITION BY category ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS top_2nd,
NTH_VALUE(product_name, 3) OVER ( PARTITION BY category ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS top_3rd
FROM sales;

📘 Definition: Regular aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be used AS WINDOW FUNCTIONS by adding the OVER() clause. This transforms them from collapsing rows (like GROUP BY) into keeping all rows while adding cumulative calculations. This enables Running Totals, Moving Averages, Cumulative Counts, and other time-series analytics — extremely powerful for dashboards and trend analysis.

🎯 Explanation: Jab SUM/AVG/COUNT ke saath OVER() lagate hain — yeh cumulative kaam karte hain instead of collapsing. Running Total = "abhi tak ka total" — har row par pichli sab rows ka sum. Moving Average = "last N rows ka average" — trends smooth karta hai. Business dashboards mein YTD sales, cumulative customer count, 7-day moving average — sab yehi se banti hain. Data analysts ke liye MUST-KNOW.

💻 Example 1 — Running Total (Cumulative Sum):

-- Running total of sales by date
SELECT sale_id, sale_date, product_name, amount, SUM(amount) OVER ( ORDER BY sale_date, sale_id ) AS running_total
FROM sales
ORDER BY sale_date, sale_id;

💻 Example 2 — Running Total Per Category:

-- Running total within each category
SELECT category, sale_date, product_name, amount, SUM(amount) OVER ( PARTITION BY category ORDER BY sale_date ) AS cat_running_total,
COUNT(*) OVER ( PARTITION BY category ORDER BY sale_date ) AS cat_running_count
FROM sales
ORDER BY category, sale_date;

💻 Example 3 — Category Total Comparison + % of Total:

-- Show each sale amount + category total + % of category total
SELECT category, product_name, amount, SUM(amount) OVER ( PARTITION BY category ) AS category_total,
ROUND( amount * 100.0 / SUM(amount) OVER (PARTITION BY category), 2 ) AS pct_of_category,
ROUND( AVG(amount) OVER (PARTITION BY category), 0 ) AS category_avg
FROM sales
ORDER BY category, amount DESC;

📘 Definition: Window Frames define EXACTLY which rows are included in the window calculation for each row. By default, when you use ORDER BY without a frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. You can customize this with ROWS BETWEEN N PRECEDING AND M FOLLOWING to create Moving Averages (3-day, 7-day) or centered calculations. This is the most advanced window function concept.

🎯 Explanation: Frame = "window ke andar kaunse rows include karein calculation ke liye." Default: beginning se current row tak. Custom frames se moving average ban sakta hai — "current row + last 2 rows ka average" (3-day moving avg). Bahut powerful for trend analysis — daily fluctuations smooth karke long-term trends dikhata hai. Stock market, sales trends, KPI monitoring — sab mein moving averages use hote hain.

📋 Frame Clause Syntax:

Frame Options:
ROWS BETWEEN  AND

Common Frame Patterns:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
→
From beginning to current row (default with ORDER BY)
→ Used for: Running Total

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
→ Current row + previous 2 rows (3 total)
→ Used for: 3-row Moving Average

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
→ Previous + current + next (centered 3 rows)
→ Used for: Centered smoothing

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
→
From current to
end
→ Used for: "How much left after this row?"

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
→ Entire partition
→ Used for: Overall totals (fixed for LAST_VALUE)

💻 Example 1 — 3-Row Moving Average:

-- 3-row moving average of sales amount
SELECT sale_id, sale_date, amount, ROUND( AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2 ) AS moving_avg_3row
FROM sales
ORDER BY sale_date;

💻 Example 2 — Comparing Different Frame Types:

-- Compare different frame types side by side
SELECT sale_id, sale_date, amount, SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total,
SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS sum_prev_curr_next,
SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS remaining_from_here
FROM sales
ORDER BY sale_date;

📘 Definition: Real-world scenarios that combine multiple window functions concepts. These are the exact problems asked in data analyst interviews and needed in daily dashboard work. Master these 5 scenarios and you'll be able to solve 90% of window function problems in interviews and real projects.

💻 Scenario 1 — Top N Per Group (Top 2 sales per region):

-- Top 2 highest sales in each region WITH ranked_sales AS (
SELECT region, product_name, salesperson, amount, ROW_NUMBER() OVER ( PARTITION BY region ORDER BY amount DESC ) AS rn
FROM sales ) SELECT region, product_name, salesperson, amount
FROM ranked_sales
WHERE rn <= 2
ORDER BY region, amount DESC;

💻 Scenario 2 — Salesperson Total + Rank + % of Company Total:

-- Complete salesperson performance analysis
SELECT salesperson, SUM(amount) AS total_sales, COUNT(*) AS total_orders,
RANK() OVER ( ORDER BY SUM(amount) DESC ) AS sales_rank,
ROUND( SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 2 ) AS pct_of_company
FROM sales
GROUP BY salesperson
ORDER BY total_sales DESC;

💻 Scenario 3 — Compare Current Sale with Previous (LAG):

-- Compare each sale with previous sale (day-over-day)
SELECT sale_id, sale_date, product_name, amount, LAG(amount, 1, 0) OVER ( ORDER BY sale_date, sale_id ) AS previous_amount,
amount - LAG(amount, 1, 0) OVER ( ORDER BY sale_date, sale_id ) AS change_amount,
CASE
WHEN amount > LAG(amount) OVER (ORDER BY sale_date, sale_id)
THEN '📈 Increased'
WHEN amount < LAG(amount) OVER (ORDER BY sale_date, sale_id)
THEN '📉 Decreased'
ELSE 'First Sale'
END AS trend
FROM sales
ORDER BY sale_date, sale_id;

💻 Scenario 4 — Nth Highest Sale Per Category:

-- Find 2nd highest sale in each category (DENSE_RANK handles ties) WITH ranked_sales AS (
SELECT category, product_name, amount, DENSE_RANK() OVER ( PARTITION BY category ORDER BY amount DESC ) AS rnk
FROM sales ) SELECT category, product_name, amount
FROM ranked_sales
WHERE rnk = 2
ORDER BY category;

💻 Scenario 5 — Cumulative % Contribution (Pareto Analysis):

-- 80/20 rule — cumulative % of sales by product
SELECT product_name, amount, SUM(amount) OVER ( ORDER BY amount DESC ) AS running_total,
ROUND( SUM(amount) OVER (ORDER BY amount DESC) * 100.0 / SUM(amount) OVER (), 2 ) AS cumulative_pct
FROM sales
ORDER BY amount DESC;

🎉 SQL Window Functions — Complete!

12 topics mein SQL Window Functions ka complete deep dive — Introduction se lekar Real Scenarios tak. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, Aggregate Windows, Frames, aur 5 real-world scenarios. Yeh SQL ka most powerful feature hai — data analysts ke liye MUST-KNOW. Interviews mein 100% puchha jaata hai. Data Insights SQL Topic Wise ka doosra topic complete! Aage aur SQL topics aayenge — JOINs Masterclass, Subqueries & CTEs, Date Functions, aur bahut kuch. Data Insights par sab FREE, detailed, Hinglish mein.

Happy Querying & Keep Analyzing! 🚀