SQL Interview Questions India 2026: Queries to Window Funcs

SQL interview topics by frequency:

1. SELECT and filtering: SELECT, WHERE, BETWEEN, LIKE, IN, IS NULL, ORDER BY, LIMIT/OFFSET, DISTINCT

1. Joins: INNER JOIN (matching rows only), LEFT JOIN (all from left, NULL for non-matching right rows), RIGHT JOIN (all from right), FULL OUTER JOIN (all rows from both), SELF JOIN (join a table with itself to find manager-employee pairs), CROSS JOIN (cartesian product)

1. Aggregation: COUNT, SUM, AVG, MAX, MIN, GROUP BY, HAVING (filter on aggregated values, WHERE cannot be used for aggregates)

1. Subqueries: scalar subquery (returns one value), row subquery (returns one row), table subquery (returns a result set used in FROM), correlated subquery (references the outer query; re-executes for each outer row)

1. CTEs (WITH clause): define a named temporary result set; used to break complex queries into readable steps; multiple CTEs in sequence with comma separation

1. Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, PARTITION BY, ORDER BY within OVER

1. String and date functions: CONCAT, SUBSTRING, UPPER, LOWER, LENGTH, TRIM, REPLACE, DATEDIFF, DATE_ADD, DATE_FORMAT, EXTRACT, NOW()

Top SQL queries that appear in Indian interviews:

1. Find employees with salary above the average: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

2. Find the second highest salary (with DENSE_RANK): WITH ranked AS ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM employees ) SELECT MIN(salary) FROM ranked WHERE rk = 2;

3. Find customers who have not placed any order: SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL;

4. Count of orders per customer including customers with zero orders: SELECT c.name, COUNT(o.id) AS order_count FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name ORDER BY order_count DESC;

5. Top 3 products by revenue per category: WITH ranked AS ( SELECT product_id, category, SUM(price * quantity) AS revenue, DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(price * quantity) DESC) AS rk FROM order_items GROUP BY product_id, category ) SELECT * FROM ranked WHERE rk <= 3;

Window function questions:

1. 'What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?' - ROW_NUMBER: unique consecutive number regardless of ties (1,2,3,4,5) - RANK: same number for ties, skips subsequent ranks (1,2,2,4,5 for scores 100,90,90,80,70) - DENSE_RANK: same number for ties, does not skip (1,2,2,3,4) Use ROW_NUMBER for 'top N per group' with deduplication; use RANK or DENSE_RANK when ties should receive equal ranking.

2. LAG and LEAD: - LAG(column, 1) OVER (ORDER BY date): value from the previous row - LEAD(column, 1) OVER (ORDER BY date): value from the next row Use for month-over-month comparison: revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change

3. Running total: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

4. 7-day moving average: AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

5. PARTITION BY: Divides the result set into groups; the window function resets for each partition. Example: rank sales reps within each region: RANK() OVER (PARTITION BY region ORDER BY sales DESC)

SQL testing varies significantly by role:

Software engineer interviews: - Focused on DBMS theory (ACID, normalisation, indexing, query optimisation) plus ability to write correct JOINs and GROUP BY queries - Depth: 1-3 SQL questions, typically medium complexity - Common: 'write a query to find the top 5 customers by order count', 'explain what an index is and when to add one'

Data analyst interviews- More SQL queries, higher complexity - Window functions (RANK, LAG, running totals), complex multi-table JOINs, CTEs - Expected to write correct, efficient SQL from scratch within 10-15 minutes - Companies: Swiggy, Zomato, Razorpay, PhonePe, OLX, Urban Company

Data engineer interviews- Additionally includes: query optimisation (execution plans, index design, partitioning), distributed SQL (Spark SQL, BigQuery, Presto/Trino), stored procedures and triggers, ETL query patterns (incremental loads, deduplication with ROW_NUMBER, slowly changing dimensions)

Tipalways clarify which database system is being used before writing queries (MySQL vs PostgreSQL vs SQL Server vs Spark SQL have syntax differences). If not specified, write standard SQL and note any assumptions.

SQL performance questions asked in India:

• 1. 'What is a database index and when should you add one?' B-tree index stores sorted keys with pointers to rows; O(log n) lookup vs O(n) full scan.
• Add on. frequently-queried columns (WHERE clause), JOIN columns (FK columns), ORDER BY columns.
• Avoid on. very low-cardinality columns (boolean fields: an index on is_active barely helps), tables with heavy write load where index maintenance overhead is significant.

2. 'How do you find a slow query and optimise it?' Step 1: identify using slow query log (MySQL) or pg_stat_statements (PostgreSQL). Step 2: run EXPLAIN or EXPLAIN ANALYZE to see the query execution plan. Look for: full table scans (type = ALL in MySQL), sequential scans on large tables (Seq Scan in PostgreSQL without an index), missing indexes on JOIN and WHERE columns. Step 3: add appropriate indexes. Step 4: rewrite the query to reduce the result set early (add WHERE conditions before JOINs, use EXISTS instead of IN for large subqueries).

3. 'What is normalisation and when do you denormalise?' Normalisation: removing data redundancy by decomposing tables into smaller ones with clear relationships (1NF: atomic values; 2NF: no partial dependency on composite key; 3NF: no transitive dependency). Denormalise when: read performance is more critical than write consistency, query patterns always join the same tables and the joins are expensive, or you are building a data warehouse (star schema/snowflake schema for analytical queries).