Short answer
SQL interview questions for data analyst roles test joins, GROUP BY with HAVING, NULL handling, date filters, CTEs, window functions and query order. Practice them on one small schema, and explain why each query works, not only what it returns.
On this page
- What schema do these questions use?
- Which join questions should you expect?
- Which aggregation questions come up?
- How should you answer NULL handling questions?
- What date logic questions are common?
- How do CTEs and subqueries get tested?
- Which window function questions should you practice?
- What query order questions do interviewers ask?
- How should you practice these questions?
These SQL interview questions for data analyst candidates cover the topics hiring teams test most: joins, aggregation, NULLs, dates, CTEs, window functions and query order. Each question has a short answer and a snippet. The wording and the toy data are ours, written for this page. If you are still choosing a course first, see our learn SQL hub.
The short version
- 31 questions in seven groups, each with a runnable snippet.
- One toy schema (three tables) so you can load it once and try every query.
- NULL traps are testable: PostgreSQL documents that
7 = NULLyields null, not false, and thatNOT INreturns null when the subquery contains a null. - Window functions are permitted only in the SELECT list and ORDER BY, per PostgreSQL’s tutorial, which is why you wrap them to filter.
- Date ranges: a half-open range (
>=start,<next start) avoids midnight edge cases thatBETWEENcreates. - Query order answers most “why does this error” questions; see the life of a SQL query.
What schema do these questions use?
Three small tables. The data is invented for this page (“toy data”) and is only there so the answers are checkable by hand.
CREATE TABLE customers (
customer_id int PRIMARY KEY,
name text,
country text,
signup_date date
);
CREATE TABLE orders (
order_id int PRIMARY KEY,
customer_id int REFERENCES customers(customer_id),
order_date date,
status text, -- 'paid', 'refunded', 'pending'
amount numeric(10,2) -- NULL while pending
);
CREATE TABLE employees (
emp_id int PRIMARY KEY,
name text,
dept text,
manager_id int REFERENCES employees(emp_id),
salary numeric(10,2)
);
| customer_id | name | country | signup_date |
|---|---|---|---|
| 1 | Ana | UK | 2026-01-10 |
| 2 | Ben | US | 2026-02-03 |
| 3 | Cy | US | 2026-02-20 |
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 101 | 1 | 2026-02-01 | paid | 60.00 |
| 102 | 1 | 2026-03-05 | paid | 40.00 |
| 103 | 2 | 2026-03-07 | refunded | 50.00 |
| 104 | 2 | 2026-03-20 | paid | 45.00 |
| 105 | 3 | 2026-03-21 | pending | NULL |
Toy data defined for this article. Snippets use PostgreSQL syntax; differences are noted where they matter.
Which join questions should you expect?
1. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the right side with NULLs when there is no match.
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;
2. How do you find customers with no orders?
LEFT JOIN, then keep the rows where the right side is NULL. NOT EXISTS gives the same answer and reads more clearly.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
3. Why does a WHERE on the right table turn my LEFT JOIN into an INNER JOIN?
Unmatched rows have NULL in the right table’s columns, and a condition like o.status = 'paid' is not true for NULL, so WHERE drops them. Put the filter in the ON clause to keep unmatched left rows.
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id AND o.status = 'paid';
4. How do you list each employee with their manager’s name?
Join the table to itself under two aliases. Use LEFT JOIN so the top manager, who has no manager, still appears.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;
5. Why did my total double after I joined another table?
The join fanned out. If one order matches three shipment rows, the order’s amount now appears three times. Aggregate the many-side first in a subquery or CTE, then join, or count distinct keys.
SELECT o.order_id, o.amount, s.shipments
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS shipments
FROM shipments GROUP BY order_id) s
ON s.order_id = o.order_id;
The shipments table is not in the toy schema; picture a table with many rows per order.
Which aggregation questions come up?
6. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation, so it can use aggregates. Put row conditions in WHERE so the grouping step sees fewer rows.
SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 50;
7. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values.
SELECT COUNT(*) AS all_orders, -- 5
COUNT(amount) AS orders_with_amt, -- 4
COUNT(DISTINCT customer_id) AS buyers -- 3
FROM orders;
8. How do you get revenue per country, highest first?
Join, filter to paid orders, group, then order by the aggregate.
SELECT c.country, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;
9. How do you find customers with more than one order?
Group by customer and keep groups with a count above one.
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
10. How do you pivot paid and refunded revenue into columns?
Conditional aggregation: a CASE expression inside SUM. PostgreSQL also supports FILTER.
SELECT customer_id,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded
FROM orders
GROUP BY customer_id;
How should you answer NULL handling questions?
11. Why does WHERE amount = NULL return nothing?
PostgreSQL’s documentation says ordinary comparison operators yield null, not true or false, when either input is null, and tells you to use IS NULL.
SELECT order_id FROM orders WHERE amount IS NULL; -- 105
12. How do you replace NULL with a default?
COALESCE returns its first non-NULL argument.
SELECT order_id, COALESCE(amount, 0) AS amount_or_zero
FROM orders;
13. What goes wrong with NOT IN when the list contains a NULL?
PostgreSQL documents that if the subquery yields a null and no equal value, NOT IN returns null, not true, so no rows come back. Use NOT EXISTS, or filter NULLs out of the subquery.
-- Risky: returns no rows if any order has a NULL customer_id
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Safer
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
14. Does AVG include NULLs?
No. Aggregate functions other than COUNT(*) skip NULL inputs, so AVG(amount) averages the four non-NULL amounts. If pending orders should count as zero, say so and use AVG(COALESCE(amount, 0)), then state the assumption.
SELECT AVG(amount) AS avg_ignoring_nulls,
AVG(COALESCE(amount, 0)) AS avg_pending_as_zero
FROM orders;
What date logic questions are common?
15. How do you count orders per month?
Truncate the date to the month and group by it. In PostgreSQL, date_trunc sets all less significant fields to their start values.
SELECT date_trunc('month', order_date)::date AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;
SQL Server uses DATETRUNC (recent versions) or DATEFROMPARTS; MySQL uses DATE_FORMAT. Name your dialect.
16. How do you get orders from the last 30 days?
Compare to the current date minus an interval. Filter on the raw column so an index on order_date can still be used.
SELECT * FROM orders
WHERE order_date >= current_date - INTERVAL '30 days';
17. Why prefer a half-open range over BETWEEN for timestamps?
BETWEEN includes both ends. With timestamps, BETWEEN '2026-03-01' AND '2026-03-31' stops at midnight on March 31 and misses the rest of that day. A half-open range is exact.
SELECT * FROM orders
WHERE order_date >= '2026-03-01'
AND order_date < '2026-04-01';
18. How do you find days from signup to first order?
Aggregate to the first order per customer, then subtract dates.
SELECT c.name,
MIN(o.order_date) - c.signup_date AS days_to_first_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.signup_date;
In PostgreSQL, subtracting two dates gives a number of days. Ana’s first order is 22 days after signup (Jan 10 to Feb 1).
How do CTEs and subqueries get tested?
19. What is a CTE, and when would you use one over a subquery?
A CTE (WITH clause) names a query so you can read it top to bottom and reuse it. PostgreSQL folds a non-recursive, side-effect-free CTE into the parent query when referenced once, so the choice is mainly about readability.
WITH paid AS (
SELECT customer_id, SUM(amount) AS total
FROM orders WHERE status = 'paid'
GROUP BY customer_id
)
SELECT c.name, p.total
FROM paid p JOIN customers c ON c.customer_id = p.customer_id;
20. How do you find customers who spent more than the average customer?
Aggregate per customer, then compare to a scalar subquery of the average.
WITH t AS (
SELECT customer_id, SUM(amount) AS total
FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT customer_id, total
FROM t
WHERE total > (SELECT AVG(total) FROM t);
21. What is a correlated subquery?
A subquery that references a column from the outer query, so it cannot run on its own. Microsoft’s documentation describes it as executed once for each row the outer query might select. The full walkthrough is in our correlated subquery guide.
SELECT o.order_id, o.amount
FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o.customer_id);
22. EXISTS or IN: which do you pick?
They return the same rows for plain membership tests. EXISTS stops at the first match and sidesteps the NULL trap in question 13. IN reads well for a short, known list.
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id AND o.status = 'paid');
Which window function questions should you practice?
23. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows within a partition. ROW_NUMBER gives unique numbers. RANK gives ties the same number and skips the next ones (1, 1, 3). DENSE_RANK gives ties the same number without skipping (1, 1, 2).
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees;
24. How do you get each customer’s latest order?
Number each customer’s orders newest first, then keep number one.
SELECT order_id, customer_id, order_date
FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) t
WHERE rn = 1;
25. How do you calculate a running total?
Use a windowed SUM ordered by date. State the frame if ties matter.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders
WHERE status = 'paid';
26. How do you compute month-over-month change?
Aggregate to months, then use LAG to pull the previous month beside the current one.
WITH m AS (
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS revenue
FROM orders WHERE status = 'paid' GROUP BY 1
)
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM m;
27. How do you find the second-highest salary in each department?
Rank with DENSE_RANK so ties do not hide the second value, then filter.
SELECT name, dept, salary
FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees
) t
WHERE dr = 2;
28. What is the difference between a window function and GROUP BY?
GROUP BY collapses rows into one per group. A window function computes across related rows and keeps every row. PostgreSQL’s tutorial says the rows “retain their separate identities”. Use a window when you need the detail row and the group figure together.
SELECT order_id, customer_id, amount,
AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;
Our window functions cheat sheet lists the syntax in one place.
What query order questions do interviewers ask?
29. In what order does SQL evaluate a query?
Logically: FROM and JOIN, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, then LIMIT. Engines may execute differently, but results must match this model. See SQL order of execution for the full walkthrough with a worked example.
30. Why can’t I use a SELECT alias in WHERE?
WHERE runs before SELECT, so the alias does not exist yet. PostgreSQL’s SELECT reference says an output column name can be used in ORDER BY and GROUP BY but not in WHERE or HAVING. Repeat the expression or wrap the query.
-- Fails: gross is not defined at the WHERE step
SELECT amount * 1.2 AS gross FROM orders WHERE gross > 50;
-- Works
SELECT amount * 1.2 AS gross FROM orders WHERE amount * 1.2 > 50;
31. Why can’t I filter on a window function in WHERE?
Window functions run after WHERE, GROUP BY and HAVING, so those clauses cannot see them. Compute the window function in a subquery or CTE, then filter in the outer query, as in questions 24 and 27. Snowflake, BigQuery, DuckDB and Databricks also offer QUALIFY for this.
How should you practice these questions?
Load the toy schema into a free client, write each answer from memory, then compare. Free clients are listed in SQL editors compared, and real data is in SQL practice datasets. If you want guided drills, LearnSQL.com and DataCamp both run practice courses. For the plain vendor pages, see LearnSQL.com and DataCamp.
Questions people ask
What SQL topics come up most in data analyst interviews?
Joins, GROUP BY with HAVING, NULL handling, date filtering, subqueries and CTEs, and window functions such as ROW_NUMBER and LAG. This guide covers all of them in 31 questions. Query order of execution is a common follow-up, so read the life of a SQL query too.
How should I practice SQL for an interview?
Type the queries rather than reading them. Load a small dataset into a free client and write each answer from memory, then check it. Our SQL practice datasets page lists places to find data, and the window functions cheat sheet covers the syntax.
Which SQL dialect should I use in an interview?
Use the one the employer names. If they do not say, ask. The core of SELECT, JOIN, GROUP BY and window functions is shared, but date functions and string functions differ. The snippets here use PostgreSQL syntax and flag where other engines differ.
Do I need to know window functions for a data analyst role?
Expect at least one question. PostgreSQL's documentation describes a window function as a calculation across rows related to the current row that keeps each row separate, and interviewers use that to test ranking and running totals. See questions 23 to 28 below.
Where can I find a course to prepare?
Work through the questions on this page, then drill window functions with the SQL window functions cheat sheet. LearnSQL.com and DataCamp both publish interview-style practice.