SQL is tested in virtually every software engineering, data engineering, data analyst, and backend engineering interview in India. The questions have evolved: 2026 interviews expect candidates to handle window functions, CTEs, and query optimisation, not just basic GROUP BY and JOIN. This guide covers the most commonly asked SQL problems at Indian tech companies, from second-highest salary queries to ranking within groups, plus indexing and query optimisation questions asked at mid-to-senior levels.
Learn the pattern, not just the answer:
Second highest salarySELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or with DENSE_RANK: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2.
Employees earning more than their manager (self-join): SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary.
Duplicate recordsSELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.
Running totalSELECT name, salary, SUM(salary) OVER (ORDER BY hire_date) AS running_total FROM employees.
Employees with no managerSELECT * FROM employees WHERE manager_id IS NULL.
Delete duplicates keeping one: DELETE FROM employees WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY email).
Department with highest average salary: SELECT department FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1.
ROW_NUMBER(): unique sequential integer per row in partition. No ties. Use for: top-N per group, pagination, deduplication.
RANK(): same rank for tied rows with gaps (1, 1, 3). Use for: leaderboards where ties share a rank.
DENSE_RANK(): same rank for tied rows, no gaps (1, 1, 2). Use for: 'find all employees whose salary rank is in the top 3' where you want exactly 3 distinct ranks.
LAG(col, n, default): value from n rows before current row in partition. Use for: month-over-month comparison, detecting consecutive events.
LEAD(col, n, default): value from n rows after current row. Use for: time-to-next-event.
SUM/AVG/COUNT OVER: running totals, rolling averages, percentage of total without collapsing rows.
SyntaxFUNCTION() OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Problemfind the top 3 highest-paid employees in each department.
SolutionSELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 3
Why ROW_NUMBER and not RANK? ROW_NUMBER gives exactly N rows per department even with ties. RANK might give more than N rows when tied employees share rank 3.
Month-over-month growth: SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct FROM monthly_revenue
Index typesB-tree (default; equality and range queries), Hash (equality only, no range), Composite (multiple columns; leftmost prefix rule), Covering (includes all columns a query needs, no table read required).
Clustered vs non-clustered: Clustered = physical row order = one per table (InnoDB: always the primary key). Non-clustered = separate structure pointing to rows; multiple allowed per table.
Query optimisationEXPLAIN/EXPLAIN ANALYZE shows the execution plan. Look for: Seq Scan (full table scan, needs an index), Index Scan (good), Nested Loop on large tables (may need a hash join). Fixes: add index on WHERE/JOIN/ORDER BY columns; avoid functions on indexed columns in WHERE (WHERE YEAR(created_at) = 2026 prevents index use; use WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'); rewrite correlated subqueries as JOINs; avoid SELECT *; use CTEs for readability and optimisation opportunities.
INNER JOINmatching rows in both tables only. Use when you only want records with a match on both sides.
LEFT JOINall rows from the left table + matching rows from the right (NULL for non-matches). Use when you want all records from the left table regardless of whether a match exists on the right.
FULL OUTER JOINall rows from both tables, NULL on the unmatched side. Use for: records in either table with no match in the other.
CROSS JOINCartesian product (every row from A with every row from B). Use for: generating combinations, date series.
SELF JOINtable joined to itself. Use for: hierarchical data (employees and managers), finding pairs within the same table.
Common mistakeusing INNER JOIN when LEFT JOIN is needed (losing rows with no right-side match).