SQL is tested in almost every data analyst interview, usually as live queries on a small sales or employee table. These 25 questions move from basics to window functions and real business problems. Every answer includes a query you can run in any free SQL tool such as DB Fiddle, SQLite or PostgreSQL.
The examples use two tables: employees(id, name, dept, salary, manager_id, hire_date) and orders(id, customer_id, order_date, amount). For the full skill set, see our data analyst roadmap.
SQL basics
1. What is the order of execution of a SQL 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 inside WHERE in most databases.
2. WHERE vs HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
SELECT dept, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2022-01-01'
GROUP BY dept
HAVING AVG(salary) > 60000;3. DELETE vs TRUNCATE vs DROP?
| Command | What it does |
|---|---|
| DELETE | Removes selected rows, can use WHERE, can be rolled back |
| TRUNCATE | Removes all rows quickly, keeps the table |
| DROP | Removes the whole table and its structure |
4. What is a primary key vs a foreign key?
A primary key uniquely identifies each row and cannot be null. A foreign key is a column that refers to the primary key of another table, linking the two, such as orders.customer_id pointing to customers.id.
5. COUNT(*) vs COUNT(column)?
COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not null. COUNT(DISTINCT column) counts unique non-null values.
6. How do you handle NULL values?
Use IS NULL or IS NOT NULL, never = NULL. Replace nulls with COALESCE(column, 0). Remember that most aggregate functions ignore nulls.
Joins
7. Explain the types of joins.
INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with nulls where there is no match. RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination.
8. Find customers who never placed an order.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;9. Show each employee with their manager’s name.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;This is a self join: the same table joined to itself with two aliases.
10. UNION vs UNION ALL?
UNION combines results and removes duplicates. UNION ALL keeps duplicates and is faster. Use UNION ALL unless you need de-duplication.
Aggregation and subqueries
11. Find the second highest salary.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);12. Find duplicate records.
SELECT name, dept, COUNT(*) AS copies
FROM employees
GROUP BY name, dept
HAVING COUNT(*) > 1;13. Find employees who earn more than their department average.
SELECT name, dept, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees WHERE dept = e.dept
);14. What is a CTE and why use it?
A Common Table Expression is a named temporary result defined with WITH. It makes long queries readable by breaking them into steps.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT * FROM monthly ORDER BY month;15. Use CASE WHEN to group values.
SELECT name,
CASE WHEN salary >= 100000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low' END AS band
FROM employees;Window functions
16. ROW_NUMBER vs RANK vs DENSE_RANK?
| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90,000 | 1 | 1 | 1 |
| 80,000 | 2 | 2 | 2 |
| 80,000 | 3 | 2 | 2 |
| 70,000 | 4 | 4 | 3 |
17. Find the top 3 earners in each department.
SELECT * FROM (
SELECT name, dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk <= 3;18. Calculate a running total.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;19. Compare each month with the previous month.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;20. Find each customer’s first order.
SELECT * FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders o
) t
WHERE rn = 1;Performance and business questions
21. How do you speed up a slow query?
Check the execution plan with EXPLAIN, add indexes on columns used in joins and filters, select only the columns you need, filter early, and avoid functions on indexed columns in WHERE clauses.
22. What is normalisation?
Organising tables to reduce duplicate data, for example storing customer details once in a customers table instead of on every order. Analytics warehouses often denormalise on purpose to make reporting faster.
23. Calculate monthly active customers.
SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY 1
ORDER BY 1;24. What percentage of revenue comes from the top 10 customers?
WITH totals AS (
SELECT customer_id, SUM(amount) AS spend
FROM orders GROUP BY customer_id
)
SELECT ROUND(100.0 * SUM(spend) / (SELECT SUM(spend) FROM totals), 1) AS top10_pct
FROM (SELECT spend FROM totals ORDER BY spend DESC LIMIT 10) t;25. How do you check your query result is correct?
Compare totals with a known report, check row counts before and after joins to catch duplicates, test edge cases such as nulls and empty months, and spot-check a few individual records by hand.
Frequently asked questions
Which SQL database should I practise on?
PostgreSQL or MySQL. The syntax above works in PostgreSQL; date functions differ slightly in MySQL and SQL Server, so learn the equivalents for the database the company uses.
How much SQL does a data analyst need?
Confident joins, aggregation, subqueries, CTEs and window functions. That covers most analyst interviews and daily work.
Where can I practise SQL interview questions?
Free platforms such as SQLZoo, LeetCode’s database section, HackerRank and StrataScratch have hundreds of graded problems.
See more data analytics tutorials, or go further with data science interview questions.
