15 SQL interview questions for data analysts, with answers

By Chetna Sharma, Handwritten eBooks & Tech Academy · Updated 25 October 2026

The examples use simple tables such as employees(id, name, department, salary, manager_id) and orders(order_id, customer_id, order_date, amount). The syntax works in PostgreSQL and MySQL 8 unless a note says otherwise.

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation. Use WHERE for conditions on raw columns and HAVING for conditions on aggregates such as COUNT or SUM.

SELECT department, COUNT(*) AS staff
FROM employees
WHERE salary > 30000          -- rows
GROUP BY department
HAVING COUNT(*) >= 5;         -- groups

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

INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table and fills NULLs where the right table has no match. Use LEFT JOIN when you must not lose records, for example customers who have never ordered.

SELECT c.customer_id, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

3. How do you find the second highest salary?

Use DENSE_RANK so that ties do not skip the second value.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

A shorter version is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

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

All three number rows in order. ROW_NUMBER always gives 1, 2, 3, 4 even for ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).

5. How do you find duplicate rows?

Group by the columns that define a duplicate and keep groups with more than one row.

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

6. How do you delete duplicates but keep one row?

Number the copies with ROW_NUMBER and delete every copy after the first. This works in PostgreSQL:

DELETE FROM customers
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

7. How do you get the top 3 earners in each department?

Rank inside each department with PARTITION BY, then filter.

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 3;

8. How do you calculate a running total?

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

9. How do you calculate month-over-month growth?

Aggregate by month, then compare each month with the previous one using LAG.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly;

In MySQL, replace DATE_TRUNC('month', order_date) with DATE_FORMAT(order_date, '%Y-%m-01').

10. How do NULLs behave in SQL?

NULL means unknown, so NULL = NULL is not true. Test with IS NULL or IS NOT NULL. COUNT(*) counts every row but COUNT(column) skips NULLs, and AVG ignores NULLs. Use COALESCE(column, 0) to replace them.

11. What is a CTE and why use one?

A CTE (common table expression) is a named temporary result defined with WITH. It makes long queries readable by breaking them into steps, and a recursive CTE can walk hierarchies such as an org chart. Question 9 above uses a CTE.

12. Find employees who earn more than their manager

Join the table to itself.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

13. Find customers who have never placed an order

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

NOT EXISTS also works and is safer than NOT IN when the subquery can return NULLs.

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

UNION removes duplicate rows from the combined result; UNION ALL keeps them. UNION ALL is faster because it skips the de-duplication step, so use it when you know there are no duplicates or you want to keep them.

15. What is the logical order in which SQL runs a query?

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. This is why you cannot use a SELECT alias in WHERE, but you can use it in ORDER BY.

How to answer SQL questions in an interview

• Restate the question and ask what the table grain is (one row per what?).
• Say how you would handle ties, NULLs and duplicates before you write the query.
• Write the query in steps with CTEs, then check it against a tiny example.

Frequently asked questions

Related