SQL Tutorial
•
•
5 min read
Deloitte SQL Interview Questions for 2-4 Years Experience: Complete Preparation Guide (2026)
Deloitte SQL Interview Questions for 2-4 Years Experience: Complete Preparation Guide (2026)
This article compiles the most frequently asked SQL topics and questions at Deloitte for mid-level roles (SQL Developer, Data Analyst, Data Engineer). It builds directly on the performance series you've published (Views, Materialized Views, CTEs, Indexes, Execution Plans, GROUP BY, Connection Pooling).
---
Deloitte SQL Interview Questions for 2-4 Years Experience: Must-Know Topics & Answers
Deloitte interviews for roles like SQL Developer, Data Analyst, or Data Engineer (2-4 years experience) heavily test practical SQL skills, query optimization, and real-world problem-solving. At this experience level, interviewers expect you to write clean, efficient queries, explain trade-offs, and discuss performance tuning.
They rarely ask only basic questions. Instead, they combine concepts like CTEs, GROUP BY + HAVING, Window Functions, Indexes, Execution Plans, Views vs Materialized Views, and optimization.
In this guide, you'll find:
- Most common theoretical questions
- Frequently asked coding/query questions with solutions
- Performance & optimization questions (highly emphasized at Deloitte)
- Preparation tips
1. Core Theoretical Questions (Frequently Asked)
1. What is the difference between WHERE and HAVING clauses?
- `WHERE` filters individual rows before grouping/aggregation.
- `HAVING` filters groups after `GROUP BY` and aggregation.
Example:
```sql
SELECT department, COUNT() AS emp_count, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000 -- Filters rows first
GROUP BY department
HAVING COUNT() > 10 AND AVG(salary) > 80000; -- Filters groups
```
2. Explain different types of JOINs with examples.
- INNER JOIN: Returns only matching rows from both tables.
- LEFT JOIN (LEFT OUTER): All rows from left table + matching from right (NULLs for non-matches).
- RIGHT JOIN: Opposite of LEFT JOIN.
- FULL OUTER JOIN: All rows from both tables.
3. What are Views, Materialized Views, and CTEs? When do you use each?
- View: Virtual table (stores query definition). Always fresh data, but runs the query every time.
- Materialized View: Physically stores data. Faster for complex queries but needs refreshing. Best for reporting/dashboards.
- CTE: Temporary named result set within a single query. Great for readability and breaking complex logic. Supports recursion.
(Refer to your earlier articles for detailed syntax and comparison.)
4. What are Clustered and Non-Clustered Indexes? Which one would you create on a frequently filtered column?
- Clustered: Determines physical order of data (only 1 per table, usually on Primary Key).
- Non-Clustered: Separate structure with pointers (multiple allowed). Use `INCLUDE` for covering indexes.
5. What is a CTE vs Temporary Table vs Subquery?
- CTE: Query-scoped, readable, supports recursion.
- Temp Table: Persists in the session, can be indexed, better for very large intermediate results.
- Subquery: Nested, less readable for complex logic.
6. Explain Query Execution Plans. How do you read them?
- Generated by the optimizer. Look for Index Seek (good) vs Table Scan (bad), Key Lookups, high-cost operators, and estimated vs actual rows mismatch.
- Use `EXPLAIN ANALYZE` (PostgreSQL) or Actual Execution Plan (SQL Server).
7. What is Normalization? When do you consider Denormalization?
- Normalization reduces redundancy (1NF, 2NF, 3NF).
- Denormalization improves read performance in reporting/analytics systems by adding redundancy (at the cost of write overhead and data inconsistency risk).
2. Common Coding / Query Questions (2-4 Yrs Level)
Question 1: Find employees who earn more than their manager (Self-Join)
```sql
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
```
Question 2: Find the second highest salary in each department
```sql
WITH ranked AS (
SELECT
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT department, salary AS second_highest_salary
FROM ranked
WHERE rnk = 2;
```
Question 3: Find duplicate records and delete them keeping one
```sql
-- Find duplicates
SELECT employee_id, COUNT()
FROM employees
GROUP BY employee_id
HAVING COUNT() > 1;
-- Delete duplicates (using CTE or ROW_NUMBER)
WITH duplicates AS (
SELECT ,
ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY created_at) AS rn
FROM employees
)
DELETE FROM duplicates WHERE rn > 1;
```
Question 4: Calculate running total or monthly revenue trend (Window Function + GROUP BY)
```sql
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS monthly_revenue,
SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
```
Question 5: Identify VIP / Premium customers (Common Deloitte-style)
```sql
WITH customer_metrics AS (
SELECT
customer_id,
COUNT(order_id) AS order_count,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id
)
SELECT
c.name,
cm.order_count,
cm.total_spent,
CASE
WHEN cm.total_spent > 10000 AND cm.order_count > 5 THEN 'VIP'
WHEN cm.total_spent > 5000 THEN 'Premium'
ELSE 'Regular'
END AS customer_segment
FROM customer_metrics cm
JOIN customers c ON cm.customer_id = c.id
ORDER BY cm.total_spent DESC;
```
3. Performance & Optimization Questions (Very Important at Deloitte)
- How do you optimize a slow `GROUP BY` query on a large table?
- What is the difference between Hash Aggregate and Stream Aggregate in execution plans?
- How does Connection Pooling help in high-traffic applications?
- When would you recommend a Materialized View over a regular View?
- How do you find missing indexes or tune a query using execution plans?
Tips they like to hear:
- Filter early with `WHERE` instead of `HAVING`.
- Use covering indexes.
- Prefer `UNION ALL` over `UNION`.
- Monitor with `EXPLAIN ANALYZE` or Query Store.
4. Preparation Tips for Deloitte SQL Round (2-4 Yrs)
1. Revise all topics from this series: Views, Materialized Views, CTEs, Indexes, GROUP BY, Execution Plans, and Connection Pooling.
2. Practice writing queries on LeetCode, HackerRank, or DataLemur (many Deloitte-style questions available).
3. Always explain your approach: “I would use a CTE here for readability… then apply window function because…”
4. Be ready to discuss trade-offs (performance vs readability, real-time vs batch).
5. Know database-specific differences (PostgreSQL vs SQL Server vs Oracle/MySQL).
6. Prepare behavioral examples: “Tell me about a time you optimized a slow query in production.”
Expected Salary Range (India, 2-4 yrs): 9–18 LPA (varies by location and negotiation).
Conclusion
Deloitte SQL interviews for 2-4 years experience focus on balanced knowledge — you must write correct queries quickly and explain why your solution is efficient. Mastering CTEs, Window Functions, Indexing strategies, and Execution Plans gives you a strong edge.
Practice the questions above on real datasets. Combine them with your understanding of GROUP BY, Views/Materialized Views, and optimization techniques for the best results.
Good luck with your Deloitte interview!