Top 30 SQL Interview Questions for Data Analysts at Deloitte

1. What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before grouping and aggregation, while HAVING filters groups after GROUP BY and aggregation.
For example:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

Here, WHERE first removes employees earning ₹50,000 or less. HAVING then keeps only departments containing more than five remaining employees.

A good interview answer should also mention that filtering rows early with WHERE can reduce the amount of data that needs to be grouped.

2. What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows with matching values in both tables.

A LEFT JOIN returns all rows from the left table, together with matching rows from the right table. When there is no match, the right-side columns contain NULL.

For example, to identify customers who have never purchased:

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Understanding how unmatched rows behave is particularly important when working with customer and transaction data.

3. What is a SELF JOIN, and when would you use it?

A SELF JOIN joins a table to itself using different aliases.

A common hierarchical example is finding employees who earn more than their managers:

SELECT e.name AS employee,
       e.salary AS employee_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;

The same table is treated as both the employee and manager dataset.

4. What is the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicate rows.

UNION ALL combines result sets without removing duplicates.

Because duplicate elimination requires additional processing, the supplied research recommends using UNION ALL when deduplication is not a business requirement.

5. What is a CROSS JOIN?

A CROSS JOIN produces the Cartesian product of two tables.

If table A contains 10 rows and table B contains 5 rows, the result can contain 50 combinations.

It can be useful when generating combinations or analytical grids, such as a date-and-customer framework for cohort analysis.

6. How would you find the Nth highest salary?

Window functions provide a scalable approach.
For example, DENSE_RANK() can identify salary levels while handling ties:

WITH ranked_employees AS (
    SELECT employee_id,
           name,
           salary,
           DENSE_RANK() OVER (
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE salary_rank = 2;

If the requirement is specifically the second-highest distinct salary, DENSE_RANK() is useful because tied salaries receive the same rank.

7. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

For example, with salaries producing ties:

• ROW_NUMBER() → 1, 2, 3, 4
• RANK() → 1, 1, 3, 4
• DENSE_RANK() → 1, 1, 2, 3

Choosing the right function depends on whether ties should consume ranking positions.

8. How would you calculate month-over-month growth using SQL?

LAG() can retrieve the previous period's value.
Conceptually:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_revenue
FROM monthly_revenue;

The percentage change can then be calculated using the current and previous values.

However, the research highlights an important issue: LAG() operates on rows, not on an abstract calendar. Missing dates can therefore produce misleading comparisons.

9. What is the difference between LAG() and LEAD()?

LAG() accesses a previous row within an ordered window.
LEAD() accesses a subsequent row.

For example:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_month,
    LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM revenue;

These functions are useful for time-series comparisons and period-over-period analysis.

10. What is a date spine, and why can it matter when using LAG()?

A date spine is a continuous sequence of dates used as a reference calendar.

This becomes important when transactional data does not contain a row for every calendar day. For example, if Sunday has no sales record, directly applying LAG() may compare Saturday with Monday.

The supplied research recommends generating a continuous date spine and joining transaction data onto it before performing chronological calculations.

11. What is the difference between ROWS and RANGE in a window frame?

ROWS works with physical rows.
RANGE works with logical peers that share the same ordering value.

This distinction matters when multiple records have the same ORDER BY value.
For precise row-based calculations, an explicit frame such as:

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

can be used for a seven-row trailing calculation.

12. How would you calculate a running total?

A windowed SUM() can calculate a cumulative total:

SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM daily_sales;

Explicitly defining the frame can make the intended calculation clearer, particularly when duplicate ordering values exist.

13. How would you identify customers who purchased from every product category?

This is a relational-division type problem.
One approach is to count each customer's distinct categories and compare that number with the total number of available categories:

SELECT customer_id
FROM customer_contracts
GROUP BY customer_id
HAVING COUNT(DISTINCT product_category) =
       (
         SELECT COUNT(DISTINCT product_category)
         FROM products
       );

The dynamic comparison means the query can continue working if additional categories are added to the catalog.

14. How would you calculate RFM metrics using SQL?

RFM stands for:

• Recency — how recently the customer interacted or purchased
• Frequency — how often they purchased
• Monetary — how much they spent

The research describes calculating these metrics by customer using aggregations such as:

• MAX(order_date)
• COUNT(DISTINCT order_id)
• SUM(order_value)

The resulting customer-level dataset can then be segmented using CASE logic or functions such as NTILE().

15. How would you identify at-risk customers using SQL?

Suppose the business defines an at-risk customer as someone who:

• had transactions during an earlier period, but
• has made no purchase during the most recent 60 days.

One approach is to calculate each customer's latest transaction date:

SELECT customer_id,
       MAX(transaction_date) AS last_transaction
FROM transactions
GROUP BY customer_id;

The resulting date can then be compared against the defined time windows.

The research specifically describes identifying customers whose latest transaction falls between 60 and 150 days ago for the stated scenario.

16. How would you calculate a conversion rate using SQL?

Conditional aggregation can convert event-level records into business metrics.
For example:

SELECT
    100.0 *
    SUM(CASE WHEN action = 'add_to_cart' THEN 1 ELSE 0 END)
    /
    NULLIF(
        SUM(CASE WHEN action = 'click' THEN 1 ELSE 0 END),
        0
    ) AS conversion_rate
FROM events;

The supplied research highlights integer division as an important issue. Using a decimal value such as 100.0 can ensure the calculation retains its fractional component in SQL dialects where integer division would otherwise truncate it.

17. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts rows.
COUNT(column_name) counts only rows where that particular column is not NULL.

For example:

SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS rows_with_email
FROM customers;

If these numbers differ, some rows contain a NULL email value. Comparing total rows with distinct keys can also help investigate the grain and potential duplication of a dataset.

18. What is the NULL problem with NOT IN?

NULL can produce unexpected results with NOT IN.

For example, if a subquery used by NOT IN contains a NULL, SQL's three-valued logic can cause comparisons to evaluate as UNKNOWN.
The supplied research recommends considering NOT EXISTS as an alternative:

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This avoids the particular NULL behavior associated with NOT IN.

19. Why can a LEFT JOIN accidentally behave like an INNER JOIN?

Consider:

SELECT *
FROM orders o
LEFT JOIN payments p
    ON p.order_id = o.order_id
WHERE p.status = 'success';

Orders without a matching payment receive NULL in the payment columns. The WHERE condition then removes those rows.

The result therefore behaves like an inner join for the relevant condition.
If the intention is to filter matching payment records while preserving unmatched orders, the condition can instead be placed in the join:

LEFT JOIN payments p
    ON p.order_id = o.order_id
   AND p.status = 'success'

This is an important SQL edge case when preserving the left-side dataset matters.

20. What is join fan-out, and how can it inflate revenue?

Suppose one order has multiple order-item records.

Joining the order table to order items can duplicate the order-level information across multiple rows. If you then calculate:

SUM(order_total)

the same order total may be counted multiple times.

A safer approach is to aggregate the many-side table first and then join it back to the one-side table.

The research specifically warns against using SUM(DISTINCT order_total) as a quick fix because two legitimate orders can have the same total.

21. What is query execution plan analysis?

An execution plan shows how a database optimizer intends to execute a query.

When investigating a slow query, candidates should understand tools such as:

EXPLAIN

or:

EXPLAIN ANALYZE

Depending on the database system, the plan can reveal operations such as scans, joins, lookups, and differences between estimated and actual row counts.

22. What is the difference between an index seek and a full table scan?

An index seek uses an index to locate relevant records more directly.

A full table scan reads the table to find qualifying records.

When diagnosing a slow query, examining whether the execution plan is performing unnecessary scans can help identify potential optimization opportunities.

23. What is the difference between clustered and non-clustered indexes?

The research describes:

• Clustered index: determines the physical ordering of data and therefore a table can have only one clustered ordering.
• Non-clustered index: exists separately from the underlying data and can provide additional access paths.

Non-clustered indexes can improve read performance, but they also introduce maintenance overhead when data is inserted, updated, or deleted.

24. What is data skew in a distributed data system?

Data skew occurs when data is distributed unevenly across processing partitions.

For example, if a partitioning key causes 85% of records to be assigned to one partition, that partition can become a bottleneck while others remain underutilized.

The research describes salting as one technique for splitting a heavily concentrated partition key into smaller partitions that can be processed in parallel.

25. When would you use a CTE instead of a temporary table?

A CTE is useful for organizing query logic into named, readable steps:

WITH customer_totals AS (
    SELECT customer_id,
           SUM(order_value) AS total_spend
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_spend > 10000;

The research contrasts this with temporary tables, which can be useful for large intermediate datasets where physical indexing and multi-step processing are beneficial.

26. What is a materialized view, and when can it be useful?

A standard view stores a query definition, while a materialized view stores the computed result.

Materialized views can therefore be useful for expensive reporting workloads where slightly older data is acceptable in exchange for faster query response.

The research gives executive reporting dashboards as an example of a situation where this trade-off can make sense.

27. How would you remove duplicate records while keeping the latest record?

ROW_NUMBER() can be used to rank records within each business key:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_timestamp DESC
           ) AS rn
    FROM customer_records
)
SELECT *
FROM ranked
WHERE rn = 1;

This keeps the most recently updated record for each customer according to the specified ordering.

The research presents this pattern as a way to isolate the current record when duplicate records have been incorrectly inserted.

28. How would you safely backfill a new column in a very large table?

For a massive production table, a simple ALTER TABLE followed by a large update may create operational risks depending on the database architecture and workload.

The research recommends considering a phased backfill strategy, potentially using a staging structure, incremental migration, and a final controlled transition to reduce disruption to live reporting.

The important interview point is not merely writing the update statement; it is explaining how you would protect production workloads.

29. How should NULL values be handled when calculating averages?

SQL aggregate functions generally ignore NULL values.
For example:

SELECT AVG(survey_score)
FROM surveys;

will calculate the average using available non-NULL scores.
However, replacing NULL with zero changes the meaning of the data:

AVG(COALESCE(survey_score, 0))

This should only be done when the business definition explicitly treats missing responses as zero. The research emphasizes that analysts should state and justify their assumptions about missing data rather than automatically treating unknown values as zero.

30. How would you approach a SQL business case in a Deloitte Data Analyst interview?

A strong approach is to avoid immediately writing SQL.
First clarify:

• What business question are we answering?
• What metric defines success?
• What is the grain of the data?
• Which tables contain the required information?
• What assumptions or edge cases exist?
• What output does the stakeholder actually need?

Then build the query step by step and explain the reasoning.

The supplied research emphasizes that technical interviews can involve business scenarios such as conversion-rate declines, customer churn, and revenue analysis, where candidates are expected to connect SQL logic with the underlying business problem.