30 SQL Interview Questions for Data Analysts

Data analyst SQL interviews usually test the same core areas: filtering and aggregation, joins, NULL handling, window functions, CTEs and turning a business question into a query. Below are 30 common questions with short model answers, grouped from basic to advanced, plus the classic coding problems you should be able to write from memory.

Basic questions (1 to 10)

1. What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation, so it can use SUM or COUNT.
2. What is the logical order in which a query is processed? FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/TOP. This explains why a column alias usually cannot be used in WHERE.
3. What is a primary key and a foreign key? A primary key uniquely identifies each row and cannot be NULL. A foreign key references a primary key in another table to enforce a relationship.
4. DELETE vs TRUNCATE vs DROP? DELETE removes selected rows and can use WHERE; TRUNCATE quickly removes all rows but keeps the table; DROP removes the table itself.
5. UNION vs UNION ALL? UNION removes duplicate rows; UNION ALL keeps them and is faster. Use UNION ALL unless you need de-duplication.
6. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)? COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values.
7. How do you handle NULL values? Test with IS NULL / IS NOT NULL, never = NULL. Replace them with COALESCE(col, 0) when appropriate, and remember most aggregates ignore NULLs.
8. What does DISTINCT do? It removes duplicate rows from the result based on all selected columns, not just the first one.
9. What is the difference between CHAR and VARCHAR? CHAR has a fixed length padded with spaces; VARCHAR stores variable-length text up to a maximum.
10. How does LIKE work? % matches any sequence of characters and _ matches exactly one, so 'A%' finds values starting with A.

Joins and aggregation (11 to 18)

11. Explain INNER, LEFT, RIGHT and FULL OUTER joins. INNER keeps matches only; LEFT keeps all left rows plus matches; RIGHT is the mirror; FULL OUTER keeps all rows from both sides. See SQL joins explained for diagrams.
12. What is a self join? Joining a table to itself with aliases, for example matching employees to their managers in the same table.
13. What is a CROSS JOIN? It returns every combination of rows (a Cartesian product). Useful for building date-by-product grids; dangerous by accident.
14. Why might a join return more rows than expected? The join key is not unique on one side, so rows multiply. Check uniqueness with GROUP BY key HAVING COUNT(*) > 1.
15. How do you find customers with no orders? LEFT JOIN orders and filter WHERE o.order_id IS NULL, or use NOT EXISTS.
16. Can you SELECT a column that is not in GROUP BY? Not in standard SQL unless it is aggregated. Some databases allow it in certain modes, but the result can be unpredictable.
17. How do you count orders by status in one row? Conditional aggregation: SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END).
18. How do you find duplicate records? Group by the columns that define a duplicate and keep groups with HAVING COUNT(*) > 1.

Window functions, subqueries and CTEs (19 to 25)

19. What is a window function? A calculation across related rows defined by OVER() that, unlike GROUP BY, keeps every row. Read SQL window functions for a full guide.
20. ROW_NUMBER vs RANK vs DENSE_RANK? For ties, ROW_NUMBER gives unique numbers (1, 2, 3), RANK leaves gaps (1, 1, 3) and DENSE_RANK does not (1, 1, 2).
21. What does PARTITION BY do? It restarts the window calculation for each group, such as ranking products within each category.
22. How do you compare a value with the previous period? Use LAG(revenue) OVER (ORDER BY month) and subtract, or divide for growth.
23. How do you calculate a running total? SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
24. What is a CTE and why use it? A named temporary result defined with WITH. It makes multi-step logic readable and can be referenced several times in the query.
25. What is a correlated subquery? A subquery that references the outer query and is evaluated per outer row, for example finding orders above each customer's average. More in CTEs and subqueries.

Practical and performance questions (26 to 30)

26. How do you find the second-highest salary? Use DENSE_RANK() and filter rank 2, which handles ties; see the code below.
27. How do you get the top 3 products per category? ROW_NUMBER() or DENSE_RANK() partitioned by category in a CTE, then filter rn <= 3.
28. How do you speed up a slow query? Read the execution plan, index columns used in joins and filters, avoid SELECT *, filter early and avoid wrapping indexed columns in functions inside WHERE.
29. What is an index and what is the trade-off? A structure that speeds up lookups, at the cost of storage and slower inserts and updates.
30. What is normalization, and why do BI models denormalize? Normalization splits data to avoid redundancy in transactional systems. BI uses star schemas that denormalize dimensions for simpler, faster reporting.

Classic coding problems with solutions

Second-highest salary (handles ties)

with ranked as (
    select salary,
           dense_rank() over (order by salary desc) as rnk
    from employees
)
select distinct salary
from ranked
where rnk = 2;

Top 3 products by revenue in each category

with product_sales as (
    select p.category, p.product_name, sum(s.amount) as revenue
    from sales s
    join products p on p.product_id = s.product_id
    group by p.category, p.product_name
),
ranked as (
    select *,
           row_number() over (partition by category order by revenue desc) as rn
    from product_sales
)
select category, product_name, revenue
from ranked
where rn <= 3
order by category, revenue desc;

Month-over-month growth (PostgreSQL)

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))
             / nullif(lag(revenue) over (order by month), 0), 1) as mom_growth_pct
from monthly
order by month;

NULLIF prevents a division-by-zero error, and the first month correctly returns NULL because it has no previous value.

How to prepare in two weeks

1. Days 1 to 3: review basics, joins and GROUP BY; answer questions 1 to 18 out loud without notes.
2. Days 4 to 7: practice window functions and CTEs daily on a sample database.
3. Days 8 to 11: solve timed problems on practice platforms and redo the three classic problems above from memory.
4. Days 12 to 14: do mock interviews and prepare to walk through one of your SQL portfolio projects.

Next steps

Copy these 30 questions into a document and write your own answer to each in one or two sentences. Load a sample database such as Chinook or Northwind, run every code example above, then change one detail (ties, NULLs, a different date range) and predict the result before running it. When you can explain each answer without notes, schedule a mock interview and start applying.

Watch: video lessons

▶

▶

Frequently asked questions