SQL Interview Questions for Indian Companies 2026

JOIN types tested at every level: INNER JOIN (only matching rows in both tables), LEFT JOIN (all from left, matching from right, NULL if no match), RIGHT JOIN (all from right), FULL OUTER JOIN (all rows from both). Self join: a table joined with itself (used for hierarchical data: manager-employee relationships). Subqueries vs JOINs: subqueries in WHERE (correlated vs non-correlated), in FROM (derived tables), in SELECT (scalar subqueries). GROUP BY and HAVING: GROUP BY aggregates rows; HAVING filters aggregated results (WHERE filters before aggregation, HAVING filters after). DISTINCT vs GROUP BY: use GROUP BY when you also need an aggregate, DISTINCT for unique values only.

Window functions are the most commonly tested advanced SQL topic at Indian product and analytics companies. ROWNUMBER(): unique sequential number per partition (removes duplicates: keep row with ROWNUMBER = 1). RANK(): same rank for ties, skips numbers (1, 1, 3). DENSE_RANK(): same rank for ties, no skips (1, 1, 2). LAG(col, n): value from n rows previous in the partition: used for comparing current vs previous period. LEAD(col, n): value from n rows ahead. SUM OVER (PARTITION BY...): running totals and cumulative sums. Most common interview questions using window functions: find the second highest salary per department, find users who made purchases in 3 consecutive months, rank products by sales within each category.

Scenario questions that distinguish intermediate from advanced SQL: (1) Find users who logged in every day for the past 30 days. Solution: count distinct dates, filter where count = 30, use date arithmetic. (2) Find the most recent order per customer. Solution: ROWNUMBER() OVER (PARTITION BY customerid ORDER BY orderdate DESC) = 1 in a subquery. (3) Calculate month-over-month revenue growth. Solution: LAG(revenue, 1) OVER (ORDER BY month) to get previous month, then (current - previous) / previous. (4) Find duplicate records and keep only one. Solution: ROWNUMBER() OVER (PARTITION BY uniquecolumns) and delete rows where rownumber > 1. (5) Pivot data from rows to columns. Solution: CASE WHEN with GROUP BY, or database-specific PIVOT syntax.

Query optimisation is tested at senior data engineering and senior backend roles. Key concepts: EXPLAIN/EXPLAIN ANALYZE: show the query execution plan: identify full table scans, index usage, join types. Index types: B-tree (default: equality and range queries), Hash (equality only), GIN (arrays, JSON, full text), BRIN (large sequential data like timestamps). When NOT to index: high-write tables (indexes slow writes), low-cardinality columns (gender, boolean: index not helpful). Common optimisation techniques: avoid SELECT *, use covering indexes, avoid functions on indexed columns in WHERE (prevents index use), partition large tables, materialise views for complex repeated queries. N+1 query problem: executing one query to get N records then N queries for related data: use JOINs or batch loading instead.