Top 30 Advanced SQL Interview Questions & Answers

Window Functions, CTEs, Recursive Queries, Stored Procedures, Query Optimization, EXPLAIN Plan aur real-world scenario-based questions — yeh 2+ years experience level ke liye hain. Har question mein Answer (English), Explanation (Hinglish) aur SQL Query. Data Insights par.

📑 Topics Covered:

• Window Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE (Q1-Q6)
• Window Functions — LAG, LEAD, Running Totals, Moving Avg (Q7-Q11)
• CTEs — WITH Clause & Recursive CTEs (Q12-Q15)
• Stored Procedures & Triggers (Q16-Q18)
• Query Optimization & EXPLAIN (Q19-Q22)
• Advanced Scenario Questions (Q23-Q30)

📋 Level: Advanced — 2+ Years Experience. Window Functions aur CTEs modern SQL ka core hain — har senior-level interview mein puchhe jaate hain. Prerequisite: Basic + Intermediate SQL Interview Questions (Data Insights par available).

Answer: Window Functions perform calculations across a set of table rows that are related to the current row — called a "window." Unlike GROUP BY (which collapses rows into groups), Window Functions retain all individual rows while adding calculated values. They use the OVER() clause to define the window. Types: Ranking (ROW_NUMBER, RANK, DENSE_RANK), Aggregate (SUM, AVG, COUNT over window), Value (LAG, LEAD, FIRST_VALUE, LAST_VALUE).

🎯 Explanation: GROUP BY mein 100 rows → 5 groups (rows collapse ho jaati hain). Window Functions mein 100 rows → 100 rows PLUS har row ke saath ek extra calculated column (rows collapse nahi hoti). Jaise "har employee ke saath uske department ki average salary bhi dikhao" — GROUP BY se nahi ho sakta (rows collapse hongi), Window Function se ho jaayega. OVER() ke andar PARTITION BY (group define karo) aur ORDER BY (sort karo) likhte hain.

-- Window Function syntax:
-- function_name() OVER (PARTITION BY col ORDER BY col)
-- Show each employee's salary
WITH their department average
SELECT name, department, salary,
AVG(salary) OVER
(PARTITION BY department) AS dept_avg

FROM employees;

name │ department │ salary│ dept_avg
Amit Kumar │ Sales │ 55000 │ 58000 ← All Sales rows
Ravi Sharma│ Sales │ 61000 │ 58000 show same avg
Priya Patel│ IT │ 72000 │ 67000 ← All IT rows
Neha Gupta │ IT │ 62000 │ 67000
show same avg Note: ALL rows retained — no collapsing!

Answer: ROW_NUMBER() assigns a unique sequential integer to each row within a partition. Even if two rows have the same value, they get different row numbers. It always produces unique, consecutive numbers: 1, 2, 3, 4...

🎯 Explanation: ROW_NUMBER() = serial number dena — har row ko unique number milta hai, chahe values same hon. PARTITION BY se groups banao — har group mein numbering 1 se start hoti hai. ORDER BY se sequence define karo — "salary descending se number do." Sabse zyada use hota hai — "har department ka top earner dikhao" ya "duplicate rows mein se ek rakho."

-- Row number within each department (sorted by salary desc)
SELECT name, department, salary,
ROW_NUMBER()
OVER ( PARTITION BY
department ORDER BY salary DESC )
AS rn

FROM employees;

name        │ department │ salary│ rn 
Priya Patel │ IT         │ 72000 │ 1 ← Top earner in IT 
Neha Gupta  │ IT         │ 62000 │ 2 
Ravi Sharma │ Sales      │ 61000 │ 1 ← Top earner in Sales 
Amit Kumar  │ Sales      │ 55000 │ 2

Answer: All three assign rankings, but handle ties (equal values) differently. ROW_NUMBER gives unique numbers regardless of ties. RANK gives same rank for ties but SKIPS numbers after. DENSE_RANK gives same rank for ties but does NOT skip — next rank is consecutive.

SELECT name, salary,
ROW_NUMBER()OVER
(ORDER BY salary DESC)
AS row_num,RANK()
OVER (ORDER BY salary
DESC) AS rnk,
DENSE_RANK() OVER
(ORDER BY salary DESC)
AS dense_rnk
FROM employees;

ROW_NUMBER: 1, 2, 3, 4, 5 (always unique) 
RANK: 1, 2, 2, 4, 5 (tie=same, then SKIP) 
DENSE_RANK: 1, 2, 2, 3, 4 (tie=same, NO skip)

🎯 Explanation: ROW_NUMBER = serial number (hamesha unique). RANK = competition ranking (2 logon ko tied 2nd mila, next 4th — 3rd skip). DENSE_RANK = dense ranking (2 logon ko tied 2nd, next 3rd — skip nahi). Interview mein puchha jaata hai: "Nth highest salary DENSE_RANK se nikalo" — kyunki ties handle hote hain properly.

Answer: Use DENSE_RANK() with a CTE or subquery. DENSE_RANK handles ties correctly — if two people have the same salary, they share the same rank and the next distinct salary gets the next rank.

🎯 Explanation: Intermediate mein LIMIT OFFSET method dekha tha — lekin woh ties handle nahi karta properly. Advanced approach: DENSE_RANK() se har unique salary ko rank do, phir WHERE rank = N karke Nth highest nikalo. Yeh production-grade solution hai — ties bhi handle hote hain aur department-wise Nth salary bhi nikal sakte ho PARTITION BY se.

-- Find 3rd highest salary (overall)
WITH ranked_salaries
AS ( SELECT name, salary,
DENSE_RANK() OVER
(ORDER BY salary DESC)
AS rnk FROM employees )
SELECT name, salary
FROM ranked_salaries

WHERE rnk = 3;

-- Nth highest salary PER DEPARTMENT
WITH
dept_ranked AS ( SELECT
name, department, salary, DENSE_RANK() OVER
( PARTITION BY department ORDER BY
salary DESC ) AS rnk
FROM employees ) SELECT *

FROM dept_ranked
WHERE rnk = 2
;
-- 2nd highest salary in EACH department!

Answer: NTILE(n) divides ordered rows into n approximately equal groups (buckets) and assigns a bucket number to each row. If rows cannot be divided equally, earlier buckets get one extra row.

🎯 Explanation: NTILE(4) = data ko 4 equal groups (quartiles) mein baanto. 100 employees hain — NTILE(4) se Q1 (top 25), Q2, Q3, Q4 (bottom 25) ban jayenge. HR analytics mein use hota hai — "top 25% performers" identify karo. NTILE(3) = tertiles, NTILE(10) = deciles, NTILE(100) = percentiles.

-- Divide employees into 4 salary quartiles
SELECT name, salary, NTILE
(4) OVER
(ORDER BY salary DESC)
AS quartile
FROM employees;

-- Use: Find top 25% earners (quartile = 1)
WITH
quartiles AS ( SELECT *,
NTILE(4)
OVER (ORDER BY salary
DESC) AS q
FROM employees ) SELECT *

FROM quartiles
WHERE q = 1;

Answer: GROUP BY collapses rows into summary groups — you lose individual row details. PARTITION BY (used in Window Functions) groups rows for calculation purposes but retains ALL individual rows in the output.

🎯 Explanation: GROUP BY = summary chahiye (har department ki avg salary — 5 departments = 5 rows). PARTITION BY = detail ke saath summary chahiye (har employee ke saath uski department ki avg salary — 100 employees = 100 rows). Yeh sabse important difference hai — interview mein 100% puchha jaata hai!

Answer: LAG() accesses a value from a PREVIOUS row without self-join. LEAD() accesses a value from the NEXT row. Both accept offset (how many rows back/forward, default 1) and default value (if no previous/next row exists, returns this instead of NULL).

🎯 Explanation: LAG = "pichli row ki value dikhao." LEAD = "agli row ki value dikhao." Month-over-Month comparison ke liye perfect — "is month ki sales aur pichle month ki sales ek row mein dikhao." MoM Growth = (Current - LAG) / LAG. Bina LAG/LEAD ke yeh kaam self-join se karna padta — complex aur slow. LAG/LEAD simple aur fast hai.

-- Monthly sales with previous month comparison
SELECT month_name, total_sales, LAG
(total_sales, 1, 0)
OVER (ORDER BY month_num)
AS prev_month_sales, total_sales - LAG
(total_sales, 1, 0)
OVER (ORDER BY month_num)
AS mom_change, LEAD(total_sales,
1) OVER
(ORDER BY month_num) AS
next_month_sales
FROM monthly_sales;

month    │ name   │ total_sales │ prev_month │ mom_change 
January  │ 115000 │ 0           │ 115000     │ 24000
February │ 24000  │ 115000      │ -91000     │ 18000 
March    │ 18000  │ 24000       │ -6000      │ 7500 
April    │ 7500   │ 18000       │ -10500     │ NULL

Answer: Use SUM() as a window function with ORDER BY. The frame automatically becomes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — accumulating from start to current row.

🎯 Explanation: Running Total = har row par pichle saare rows ka total + current row. SUM() OVER(ORDER BY date) automatically cumulative sum karta hai — ORDER BY lagane se frame default "beginning se current row tak" ban jaata hai. Yeh self-join approach se bahut clean aur fast hai.

-- Running Total of monthly sales
SELECT month_name, total_sales,
SUM(total_sales) OVER
(ORDER BY month_num) AS running_total

FROM monthly_sales;
-- Running Total PER department
SELECT department, order_date, amount, SUM(amount)
OVER ( PARTITION BY
department ORDER BY order_date ) AS
dept_running_total
FROM orders;

Answer: Use AVG() with ROWS BETWEEN clause to define the sliding window. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW creates a 3-row moving average (current + 2 previous rows).

🎯 Explanation: Moving Average = sliding window ka average. 3-month moving average = "current month + pichle 2 months ka average." ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = current row aur usse pehle ki 2 rows — total 3 rows ka window. Yeh window har row ke saath slide karta hai. Trend analysis aur seasonality smoothing mein bahut use hota hai.

-- 3-month moving average
SELECT month_name, total_sales, ROUND
(AVG(total_sales) OVER
( ORDER BY month_num ROWS BETWEEN
2 PRECEDING AND CURRENT ROW ),
0) AS moving_avg_3m

FROM monthly_sales;

Answer: FIRST_VALUE() returns the first value in an ordered partition. LAST_VALUE() returns the last value. LAST_VALUE requires explicit frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — otherwise default frame gives wrong results.

SELECT name, department, salary, FIRST_VALUE
(name) OVER ( PARTITION BY
department ORDER BY salary DESC
) AS highest_earner, LAST_VALUE
(name) OVER ( PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
AS lowest_earner
FROM
employees;

⚡ Important: LAST_VALUE mein explicit frame MANDATORY hai — bina frame ke default "CURRENT ROW tak" hota hai — galat result aata hai. Hamesha ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING likho LAST_VALUE ke saath.

Answer: Window Frame defines exactly which rows relative to the current row are included in the window function's calculation. It specifies the start and end boundaries of the window.

Common Window Frame Options:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
← Running Total (default with ORDER BY) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
← Entire partition ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
← Last 3 rows (moving avg) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
← Current ± 1 row (centered avg) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
← Current to
end

🎯 Explanation: Window Frame = "kaunsi rows include karni hain calculation mein." UNBOUNDED PRECEDING = partition ki sabse pehli row. CURRENT ROW = abhi wali row. UNBOUNDED FOLLOWING = partition ki sabse aakhri row. "2 PRECEDING" = current se 2 row peeche. Frame define karna bahut important hai — especially LAST_VALUE aur Moving Averages ke liye.

Answer: A CTE (Common Table Expression) is a temporary named result set defined using the WITH clause. It exists only during the execution of that query. CTEs improve readability, allow breaking complex queries into steps, and can be referenced multiple times in the main query.

🎯 Explanation: CTE ek temporary variable hai — jaise DAX mein VAR. Pehle ek result calculate karo (WITH block mein), naam do, phir main query mein use karo. Complex queries ko steps mein todne ke liye perfect hai. Subquery se zyada readable hai aur multiple baar reference kar sakte ho. CTE query ke end hone ke baad khatam ho jaata hai — stored nahi hota.

-- CTE: Department stats, then filter
WITH dept_stats AS
( SELECT department, AVG
(salary) AS avg_sal, COUNT
(*) AS emp_count FROM
employees GROUP BY department )
SELECT *
FROM dept_stats

WHERE avg_sal > 55000
AND emp_count > 3;

-- Multiple CTEs
WITH sales_data
AS ( SELECT product_id,
SUM(amount) AS total
FROM orders GROUP BY product_id ), product_data
AS ( SELECT product_id, product_name, category
FROM products ) SELECT
p.product_name, s.total
FROM sales_data s

JOIN product_data p
ON
s.product_id = p.product_id;

Answer: A Recursive CTE references itself in its definition — creating a loop. It has two parts: an anchor query (base case) and a recursive query (references the CTE itself). Recursion continues until no new rows are produced or a limit is reached. Used for hierarchical data (org charts, file systems, category trees).

-- Recursive CTE: Employee org hierarchy
WITH RECURSIVE org_chart
AS ( -- Anchor: Top-level manager (no manager_id)
SELECT emp_id, name, manager_id, 1
AS level FROM employees
WHERE manager_id IS NULL
UNION ALL -- Recursive: Employees reporting to previous level
SELECT e.emp_id, e.name, e.manager_id, oc.level + 1
FROM employees e INNER JOIN org_chart oc
ON e.manager_id = oc.emp_id ) SELECT *
FROM
org_chart
ORDER BY level;

🎯 Explanation: Recursive CTE = khud ko call karta hai — loop jaisa. Anchor = starting point (CEO, jiska koi manager nahi). Recursive = "is level ke employees ke neeche kaun report karta hai?" — yeh tab tak chalta hai jab tak aur koi nahi bachta. Org chart, category tree, bill of materials — hierarchical data ke liye perfect hai.

-- Generate numbers 1 to 10
WITH RECURSIVE numbers AS
( SELECT 1
AS n UNION ALL
SELECT n + 1
FROM numbers WHERE n <
10 ) SELECT n
FROM numbers;

-- Generate date series for 2026
WITH RECURSIVE dates
AS ( SELECT '2026-01-01'
AS dt UNION ALL SELECT
DATE_ADD(dt, INTERVAL 1
DAY) FROM dates
WHERE dt < '2026-12-31' )
SELECT dt
FROM dates;

Answer: A Stored Procedure is a saved collection of SQL statements that can be executed as a single unit by calling its name. It can accept input parameters, perform logic, and return results. Stored Procedures are precompiled — faster execution, reusable, and maintain consistency.

-- Create Stored Procedure DELIMITER
// CREATE PROCEDURE GetEmployeesByDept( IN
dept_name VARCHAR(50) )
BEGIN SELECT emp_id, name, salary

FROM employees
WHERE department = dept_name

ORDER BY salary DESC;
END
// DELIMITER ;
-- Call the Stored Procedure
CALL GetEmployeesByDept('Sales');

🎯 Explanation: Stored Procedure = saved recipe — ek baar banao, baar baar use karo. Complex query baar baar likhne ki jagah — procedure banao, naam do, CALL karo. Parameters pass kar sakte ho (IN = input, OUT = output). Precompiled hota hai — fast execution. Security: users ko direct table access mat do — sirf procedure execute karne do.

Answer: A Trigger is a stored program that automatically executes (fires) when a specific event (INSERT, UPDATE, DELETE) occurs on a table. Triggers can run BEFORE or AFTER the event. They are used for audit logging, data validation, and automatic data updates.

-- Audit Trigger: Log salary changes CREATE TRIGGER salary_audit AFTER

UPDATE
ON employees FOR EACH ROW BEGIN IF OLD.salary <> NEW.salary
THEN
INSERT INTO salary_audit_log (emp_id, old_salary, new_salary, changed_at)
VALUES (NEW.emp_id, OLD.salary, NEW.salary, NOW());
END IF;
END;

🎯 Explanation: Trigger = automatic alarm. Jab koi salary update kare — trigger automatically old salary aur new salary audit log mein save kar deta hai. Manually kuch karne ki zaroorat nahi. OLD = update se pehle ki value. NEW = update ke baad ki value. BEFORE triggers data validate karne ke liye (insert se pehle check karo). AFTER triggers logging ke liye.

Answer: Query optimization reduces execution time and resource usage. Key techniques:

• SELECT specific columns instead of SELECT * — less data transferred
• Use proper indexes on WHERE, JOIN, ORDER BY columns
• Avoid SELECT DISTINCT unless necessary — sorting overhead
• Use EXISTS instead of IN for large subqueries — short-circuit evaluation
• Avoid functions on indexed columns in WHERE — index won't be used. WHERE YEAR(date) = 2026 ❌ → WHERE date BETWEEN '2026-01-01' AND '2026-12-31' ✅
• Use LIMIT when full result not needed
• Avoid correlated subqueries — use JOINs instead
• Use UNION ALL instead of UNION when duplicates don't matter
• Optimize JOINs — smaller table as driving table, indexed join columns
• Use EXPLAIN to analyze query execution plan

Answer: EXPLAIN shows the query execution plan — how MySQL will execute a query. It reveals which indexes are used, join order, scan type, estimated rows, and other optimization details.

EXPLAIN SELECT *
FROM employees
WHERE department = 'Sales' AND salary > 50000;

 Key EXPLAIN columns to look at: ═══════════════════════════════════════════════ type: ALL (bad-full scan), index, range, ref, const (best) key: Which index is being used (NULL = no index!) rows: Estimated rows to scan (lower = better) Extra: "Using filesort" (slow), "Using index" (good) Goal: type should be "ref" or "range", not "ALL" key should NOT be NULL rows should be as low as possible

Answer: Normalization organizes database tables to reduce redundancy and improve data integrity. Normal forms are progressive rules:

Answer: Denormalization is intentionally adding redundancy back into a normalized database to improve READ performance. It reduces JOINs by storing duplicate data in fewer tables. Used in data warehouses and reporting databases where read speed is critical.

🎯 Explanation: Normalization = data clean, no redundancy, but zyada JOINs (slow reads). Denormalization = some redundancy, fewer JOINs (fast reads). OLTP databases (banking, transactions) = Normalized (data integrity priority). OLAP databases (reporting, analytics) = Denormalized (query speed priority). Data Analyst ke liye — tum mostly denormalized data par kaam karte ho (star schema, flat tables).

-- Top 3 earners in each department WITH ranked AS (
SELECT *, ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary DESC ) AS rn
FROM employees ) SELECT *
FROM ranked
WHERE rn <= 3;

WITH yearly AS ( SELECT YEAR(order_date) AS yr, SUM(amount) AS total_sales FROM orders GROUP BY YEAR(order_date) ) SELECT yr,
total_sales, LAG(total_sales) OVER (ORDER BY yr) AS prev_year,
ROUND( (total_sales - LAG(total_sales) OVER (ORDER BY yr)) / LAG(total_sales) OVER (ORDER BY yr) * 100, 1 ) AS yoy_growth_pct
FROM yearly;

-- Find users with 3+ consecutive login days WITH login_groups AS (
SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY ) AS grp
FROM logins ) SELECT user_id, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

🎯 Explanation: Yeh "Islands and Gaps" problem hai — classic advanced SQL. Trick: login_date minus ROW_NUMBER = same group for consecutive days. Agar dates consecutive hain toh minus ROW_NUMBER same value dega (group identifier). Non-consecutive dates alag group banayengi. Bahut popular interview question hai!

-- Find missing order IDs in sequence
SELECT order_id + 1 AS gap_start, LEAD(order_id) OVER (ORDER BY order_id) - 1 AS gap_end
FROM orders
WHERE LEAD(order_id) OVER (ORDER BY order_id) - order_id > 1;

-- Monthly sales as columns (pivot)
SELECT product_name, SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS Mar
FROM orders o
JOIN products p
ON o.product_id = p.product_id
GROUP BY product_name;

SELECT e.name AS employee, e.salary AS emp_salary, m.name AS manager,
m.salary AS mgr_salary
FROM employees e
JOIN employees m
ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

🎯 Explanation: Classic SELF JOIN question — employees table khud se join hoti hai. e = employee, m = manager. e.manager_id = m.emp_id se manager find karo. WHERE e.salary > m.salary — employee ki salary manager se zyada. Yeh LeetCode/HackerRank ka famous problem hai!

-- Each product's % of total sales (Window Function approach) SELECT product_name, total_sales, ROUND( total_sales * 100.0 / SUM(total_sales) OVER (), 1 ) AS pct_of_total FROM ( SELECT product_name, SUM(amount) AS total_sales FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY product_name ) AS t ORDER BY pct_of_total DESC; -- SUM() OVER() without PARTITION BY = grand total -- Each row's sales / grand total × 100 = percentage

-- Keep latest record per email, delete older duplicates WITH ranked AS (
SELECT *, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY created_at DESC ) AS rn
FROM employees ) -- View duplicates (verify before deleting) SELECT *
FROM ranked
WHERE rn > 1;
-- Delete duplicates (keep latest — rn = 1) DELETE FROM employees WHERE emp_id IN ( SELECT emp_id FROM (
SELECT emp_id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY created_at DESC ) AS rn
FROM employees ) AS temp
WHERE rn > 1 );

🎯 Explanation: Advanced approach — ROW_NUMBER() se har duplicate group mein latest record ko rn=1 do. rn > 1 wale duplicates hain — delete karo. Intermediate mein jo self-join approach dekhi thi — yeh ROW_NUMBER approach zyada clean aur flexible hai (easily change kar sakte ho ki oldest rakhna hai ya latest, kaunse columns par partition karna hai).

🎉 SQL Interview Questions — Complete!

3 blogs mein 90 SQL Interview Questions cover kiye — Basic (30), Intermediate (30), Advanced (30). Freshers se lekar 3+ years experience tak — saare levels covered. Previously available on Data Insights: MySQL Masterclass (7 Parts), Excel Masterclass (8 Parts), Power BI Masterclass (7 Parts), Pandas, NumPy, Matplotlib, Seaborn, Plotly, Data Cleaning + Statistics — sab FREE, detailed, Hinglish mein.

Happy Learning & Crack That Interview! 🚀