Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Deloitte hiring for SQL roles
Deloitte is one of the Big Four professional services networks, offering audit, tax, consulting, risk advisory and financial advisory services. Its Indian entities, including Deloitte USI, support global clients with technology consulting, analytics, cyber and testing work. It hires in India for analyst and consultant roles in data analytics, business intelligence, QA, ERP and risk advisory.
For SQL roles, this Deloitte practice set focuses most on Transactions and Advanced SQL (6), Joins (5), Subqueries and CTEs (5), Database Design (4). It has 6 easy, 21 medium, 3 hard questions, so you can start easy and work up to the harder ones.
Company type
Consulting
Headquarters
London, United Kingdom
Main India offices
Hyderabad, Bengaluru, Mumbai, Gurugram, Pune
Deloitte SQL interview process
Online assessment (aptitude + technical)
Group discussion or case discussion
Technical interview
Partner or HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for DeloitteStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
02
Join three tables to get total sales by product category.
SELECT cat.category_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;
Join from the most detailed table outward and aggregate at the end.
03
What are dirty reads, non-repeatable reads and phantom reads?
A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.
04
An interviewer asks for the Nth highest salary in a reusable way. How do you answer?
Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.
05
How would you design data archiving for a table growing by 10 million rows a month?
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
07
Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?
Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.
08
Rewrite a nested query using CTEs for readability.
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
),
ranked AS (
SELECT customer_id, total,
NTILE(10) OVER (ORDER BY total DESC) AS decile
FROM order_totals
)
SELECT * FROM ranked WHERE decile = 1;
Each CTE is a named step, making top 10 percent customer logic easy to read.
09
Management wants the percentage of each category in total sales. How do you write it?
Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.
10
Find employees who earn more than their department average.
SELECT e.emp_id, e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.
11
What is the difference between a stored procedure and a function?
A function must return a value or table and can be used inside SELECT or WHERE, but in most databases cannot change data or manage transactions. A stored procedure is executed independently, can return multiple result sets or output parameters, can modify data and control transactions. Use functions for reusable calculations and procedures for workflows such as month-end processing.
12
Write an upsert that inserts a row or updates it if it exists.
PostgreSQL:
INSERT INTO inventory (sku, qty)
VALUES ('A100', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
MySQL:
INSERT INTO inventory (sku, qty) VALUES ('A100', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SQL Server and others use MERGE. A unique key on sku is required.
13
Find users who logged in on 3 or more consecutive days.
WITH d AS (
SELECT DISTINCT user_id, login_date FROM logins
), g AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
FROM d
)
SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days
FROM g GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).
14
Describe a time you reviewed someone else's SQL and suggested improvements.
Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.
15
What is the difference between a primary key and a unique key?
Both enforce uniqueness. A table can have only one primary key, and its columns cannot contain NULL. A table can have many unique constraints, and in most databases a unique column can hold NULL, often more than one, though SQL Server allows only one NULL by default. Primary keys often create a clustered index in SQL Server; unique keys create non-clustered indexes.
16
What is GROUP BY and what is the rule for selected columns?
GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.
17
Design tables for students, courses and enrolments.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL
);
CREATE TABLE enrolments (
student_id INT REFERENCES students(student_id),
course_id INT REFERENCES courses(course_id),
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);
The junction table resolves the many-to-many relationship.
SELECT o.order_id, o.order_date, c.name, o.amount
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;
Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.
19
A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?
Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.
20
What does DISTINCT do and how is it different from GROUP BY?
DISTINCT removes duplicate rows from the result based on all selected columns. GROUP BY groups rows so you can apply aggregates per group. SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city give the same output, but GROUP BY is needed when you also want counts or sums. Overusing DISTINCT to hide duplicate join rows is a common mistake.
21
Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?
Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.
22
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
23
What is the difference between a unique index and a normal index?
A unique index enforces that no two rows have the same value in the indexed columns, and it is created automatically for primary keys and unique constraints. A normal index only speeds up lookups and allows duplicates. Both speed up searches, but a unique index also protects data integrity, for example preventing two users from registering with the same email.
24
What is the difference between SUM with CASE and filtering with WHERE?
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
25
You need to update 50 million rows in production. How do you do it safely?
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
26
A LEFT JOIN is returning fewer rows than the left table. Why could that happen?
A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.
27
Tell me about a time you automated a manual data task with SQL.
Situation: the operations team spent four hours every Monday merging Excel files for a weekly report. Task: reduce the effort. Action: I loaded the files into staging tables, wrote SQL with joins and aggregations to produce the report, and scheduled it as a job. Result: the report was ready automatically at 8 AM, saving about 16 hours a month and removing copy-paste errors.
28
A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?
The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.
29
An interviewer asks to show each employee's salary next to their department's average. How do you do it?
Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.
30
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.
Tips to crack the Deloitte SQL interview
Practise guesstimates and simple business cases, since consulting interviews often test structured thinking alongside technical skills.
Be ready to explain dashboards or analyses you built in Excel, SQL, Power BI or Tableau and the business decisions they supported.
Read about Deloitte's service lines such as Risk Advisory and Consulting so you can say which practice suits you.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; Deloitte interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Deloitte SQL interview?
Candidates usually go through 4 stages: Online assessment (aptitude + technical); Group discussion or case discussion; Technical interview; Partner or HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the Deloitte SQL interview?
This practice set leans on Transactions and Advanced SQL (6), Joins (5), Subqueries and CTEs (5), Database Design (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Deloitte interviewers ask you to walk through real work.
Are these the exact questions asked at Deloitte?
No. These are curated practice questions that match the skills and difficulty usually tested in consulting interviews like Deloitte's. They are not a leaked or verified list of past questions.
Where does Deloitte hire SQL professionals in India?
Deloitte has large teams in Hyderabad, Bengaluru, Mumbai, Gurugram, Pune. Openings and locations change often, so check the Deloitte careers page and filter by city and experience.