SQL Interview Questions for Data Analysts (2026): 20 with Answers

SQL interviews for data analyst roles test whether you can turn a business question into a correct query: joins that neither lose nor duplicate rows, GROUP BY with HAVING, window functions for rankings and trends, CTEs for readable steps, and careful NULL handling. These 20 questions use one small sales dataset and SQL Server (T-SQL) syntax, and the results shown come from running each query on that data.

At a glance

The sample data

Three tables are used throughout. Sketch them on paper before you start, because many interviewers describe tables out loud and expect you to keep track.

CREATE TABLE customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50),
                        city VARCHAR(30), email VARCHAR(100));
CREATE TABLE orders    (order_id INT PRIMARY KEY, customer_id INT, order_date DATE,
                        amount DECIMAL(10,2), status VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept VARCHAR(30),
                        salary INT, manager_id INT);

There are six customers in Chennai, Pune, Kolkata and Kochi (two share an email address and one has none), nine orders from July to September 2026 (one has no customer_id), and six employees in Analytics and Finance, two of whom earn the same salary.

Joins (questions 1 to 3)

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

An INNER JOIN keeps only rows that match in both tables. A LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match.

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

Here the INNER JOIN returns 8 rows and the LEFT JOIN returns 10, because two customers have no orders. The order with a NULL customer_id appears in neither. Always say what happens to unmatched rows on both sides.

2. Find customers who have never placed an order

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

This returns the two customers without orders. A LEFT JOIN with WHERE o.order_id IS NULL gives the same result. Mention both, and prefer NOT EXISTS for the reason in question 16.

3. Show each employee with their manager's name

A self-join treats one table as two. Use LEFT JOIN so employees without a manager still appear.

SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.emp_id = e.manager_id;

Filtering, grouping and counting (questions 4 to 8)

4. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation, so only HAVING can use COUNT, SUM and other aggregates.

SELECT city, COUNT(*) AS customers
FROM customers
WHERE city <> 'Kochi'
GROUP BY city
HAVING COUNT(*) >= 2;    -- Chennai, 3

5. Why can't I use a column alias in WHERE?

Because of the logical processing order. Simplified, Microsoft's documentation lists it as FROM, ON, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and TOP. Aliases are created in SELECT, so WHERE has not seen them yet, while ORDER BY has.

SELECT order_id, amount * 1.18 AS amount_with_gst   -- example: add 18% GST
FROM orders
WHERE amount * 1.18 > 1000           -- WHERE amount_with_gst > 1000 fails
ORDER BY amount_with_gst DESC;       -- the alias works here

This one answer explains a whole family of error messages, so learn the order by heart.

6. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(email) counts non-NULL values. COUNT(DISTINCT email) counts unique non-NULL values. On our customers table they return 6, 5 and 4. A difference between the first two tells you about missing data, which is worth saying aloud.

7. Find duplicate email addresses

SELECT email, COUNT(*) AS copies
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

8. Delete the duplicates but keep one row for each email

Number the rows inside each email group and delete everything after the first. In SQL Server you can delete through a CTE that reads one table.

WITH ranked AS (
    SELECT customer_id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
    FROM customers
    WHERE email IS NOT NULL
)
DELETE FROM ranked
WHERE rn > 1;

Say that you would first run it as a SELECT to check which rows go, and wrap the delete in a transaction on real data.

Window functions (questions 9 to 13)

9. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows in an order. They differ only on ties.

SELECT emp_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept = 'Analytics';

RANK leaves a gap after a tie (no 3); DENSE_RANK does not. ROW_NUMBER breaks the tie arbitrarily, so add a second column to ORDER BY when the result must be repeatable.

10. Find the second highest salary in each department

WITH ranked AS (
    SELECT emp_name, dept, salary,
           DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
    FROM employees
)
SELECT emp_name, dept, salary
FROM ranked
WHERE dr = 2;

This returns both Arun and Divya for Analytics, because they tie, plus Sam for Finance. Ask the interviewer whether ties should return one row or all. For the company-wide figure, SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) also works.

11. Top two customers by revenue in each city

WITH customer_revenue AS (
    SELECT c.city, c.customer_name, SUM(o.amount) AS revenue
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.customer_id
    WHERE o.status = 'Delivered'
    GROUP BY c.city, c.customer_name
), ranked AS (
    SELECT city, customer_name, revenue,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY revenue DESC) AS rn
    FROM customer_revenue
)
SELECT city, customer_name, revenue
FROM ranked
WHERE rn <= 2;

"Top N per group" is the most useful window function pattern for analysts. In Fabric Data Warehouse a QUALIFY clause can filter on the window function directly, but SQL Server needs the CTE.

12. Calculate a running total of revenue

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
WHERE status = 'Delivered';

State the frame explicitly. With ORDER BY but no frame, SQL Server uses RANGE, which adds rows that tie on the ORDER BY columns in a single step.

13. Show month-over-month revenue growth

WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
           SUM(amount) AS revenue
    FROM orders
    WHERE status = 'Delivered'
    GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)
)
SELECT month_start, revenue,
       LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
             / NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0), 1) AS mom_pct
FROM monthly
ORDER BY month_start;

The result is 2,000 for July, 2,500 for August (25.0%) and 5,800 for September (132.0%). On SQL Server 2022 and later, DATETRUNC(month, order_date) is a shorter way to get the month start.

CTEs, NULLs and set operations (questions 14 to 18)

14. When do you use a CTE instead of a subquery or a temp table?

A CTE names a step so a long query reads top to bottom, as in questions 11 and 13. It exists only for the one statement that follows it, and SQL Server does not store its result: every reference to the CTE runs its query again. A subquery is fine for a single small filter. A temp table stores the result physically, which is the better choice when you reuse an expensive result several times or want to add an index. A recursive CTE can also walk hierarchies such as the employee and manager chain.

15. Why does WHERE email = NULL return nothing?

NULL means unknown, and any comparison with an unknown value is itself unknown, not true. Use IS NULL or IS NOT NULL. Here WHERE email = NULL returns 0 rows and WHERE email IS NULL returns Joseph.

16. Why did my NOT IN query return no rows?

If the subquery returns even one NULL, NOT IN returns nothing, because SQL cannot prove a value differs from an unknown.

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);   -- empty: one order has a NULL customer_id

Use NOT EXISTS (question 2), or add WHERE customer_id IS NOT NULL inside the subquery. Interviewers ask it because it causes real bugs in production reports.

17. What is the difference between COALESCE and ISNULL, and what does NULLIF do?

Both replace NULL. ISNULL is SQL Server specific, takes exactly two arguments and returns the data type of the first. COALESCE is standard SQL, takes any number of arguments and returns the type with the highest precedence. NULLIF(a, b) returns NULL when the two are equal, which is the standard way to avoid divide-by-zero errors, as in question 13.

SELECT customer_name, COALESCE(email, 'not given') AS email
FROM customers;

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

UNION removes duplicate rows from the combined result; UNION ALL keeps them all. UNION ALL is faster because it skips the duplicate check, so use it unless you actually need de-duplication.

Pivots and a practical case (questions 19 and 20)

19. Count delivered, returned and cancelled orders per month in one row each

Conditional aggregation with CASE works in every major SQL dialect, so it is often preferred to the PIVOT operator.

SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'Returned'  THEN 1 ELSE 0 END) AS returned,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)
ORDER BY month_start;

20. Case study: each month's top city and its growth

The question: "For each month, which city had the highest delivered revenue, and how did that city do compared with its own previous month?"

WITH monthly_city AS (
    SELECT DATEFROMPARTS(YEAR(o.order_date), MONTH(o.order_date), 1) AS month_start,
           c.city, SUM(o.amount) AS revenue
    FROM orders AS o
    JOIN customers AS c ON c.customer_id = o.customer_id
    WHERE o.status = 'Delivered'
    GROUP BY DATEFROMPARTS(YEAR(o.order_date), MONTH(o.order_date), 1), c.city
), with_growth AS (
    SELECT month_start, city, revenue,
           LAG(revenue) OVER (PARTITION BY city ORDER BY month_start) AS prev_revenue,
           RANK() OVER (PARTITION BY month_start ORDER BY revenue DESC) AS city_rank
    FROM monthly_city
)
SELECT month_start, city, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS growth_pct
FROM with_growth
WHERE city_rank = 1
ORDER BY month_start;

Chennai tops July and August (up 108.3% in August), and Kolkata tops September with no previous month to compare. Talk through the steps: first fix the grain (one row per month and city), then compare each city with itself using LAG partitioned by city, then rank cities within each month. Finally, mention that the order with no customer_id is excluded by the join and should be reported as a data quality issue rather than silently dropped. That last sentence is what separates an analyst from someone who only writes queries.

How to answer SQL questions in the interview

1. Restate the grain. "One row per customer per month" prevents most join and GROUP BY mistakes.
2. Check row counts. After each join, ask whether rows could be lost or duplicated.
3. Name the edge cases: NULLs, ties, empty months, and division by zero.
4. Mention dialect differences. SQL Server uses TOP (n) or OFFSET ... FETCH; MySQL and PostgreSQL use LIMIT.

To build a portfolio that proves these skills, try our SQL projects using Indian open data, and see the data analyst roadmap for India for where SQL fits.

How CertTulen Academy can help

CertTulen Academy teaches SQL Server in live, MCT-led online classes with hands-on labs. Microsoft SQL Server Fundamentals covers joins, grouping, NULLs and data types, which is questions 1 to 8 and 15 to 19. Microsoft SQL Server Advanced goes into window functions, CTEs, performance and real reporting queries. Teams can book a private corporate cohort. Message us on WhatsApp at +91 93618 83583 or use our contact page for a batch that suits you.