SQL Practice Platform
Article •

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!