interview questions
SQL interview questions and answers for data analysts
The SQL questions that actually come up in analyst interviews, with answers written the way you would say them out loud — and notes on what the interviewer is really testing.
By the Samyak faculty team · Published · 9 min read
Analyst SQL interviews are more predictable than they feel. The same eight or nine ideas come up repeatedly, and interviewers are usually listening for reasoning rather than recall. What follows is organised by what is being tested, because that is what tells you how to answer.
Joins: testing whether you think about row counts
What is the difference between an INNER JOIN and a LEFT JOIN?
An INNER JOIN returns only rows with a match on both sides. A LEFT JOIN returns every row from the left table, filling the right side with NULLs where there is no match.
What they are actually testing: whether you know that a LEFT JOIN can silently become an INNER
JOIN. If you filter on a right-side column in the WHERE clause — WHERE orders.status = 'paid' —
the NULL rows are removed and you have lost the left rows you were trying to preserve. Moving that
condition into the ON clause keeps them. Mentioning this unprompted is a strong signal.
A join made my row count go up. Why?
Because the join key is not unique on the side you joined to. One customer row matching three address rows returns three rows. This is a fan-out, and it silently inflates every SUM and COUNT downstream.
How to answer well: say that you check the row count before and after any join as a habit, and that if a fan-out is expected you aggregate the right-hand table down to one row per key before joining.
What does a self join do?
It joins a table to itself, aliased twice, usually to compare rows within the same table — an employee to their manager, or a row to the previous period’s row. Expect a follow-up asking you to write one, and expect the window function version to be the better answer if it is available.
Aggregation and NULLs: testing carefulness
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. You cannot use an aggregate in WHERE, and filtering in HAVING when WHERE would do makes the query read less clearly and often run slower.
How does COUNT treat NULLs?
COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values. This distinction is asked constantly because it is the
most common quiet source of a wrong number.
The same care applies to averages: AVG(column) ignores NULLs entirely rather than treating them as
zero, which is usually — but not always — what you want.
Why does NULL = NULL return nothing?
Because NULL means unknown, and two unknowns cannot be asserted equal. Use IS NULL and IS NOT NULL. Follow-up territory: NOT IN against a list containing a NULL returns no rows at all, which
is a classic production bug.
Window functions: the section that separates candidates
What is a window function and how does it differ from GROUP BY?
A window function computes across a set of rows related to the current row, without collapsing them. GROUP BY returns one row per group; a window function returns every original row with the calculation attached.
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
With tied values: ROW_NUMBER assigns distinct sequential numbers arbitrarily among ties. RANK
gives ties the same number and then skips — 1, 2, 2, 4. DENSE_RANK gives ties the same number and
does not skip — 1, 2, 2, 3.
Common live question: find the second-highest salary per department. Say why you are choosing one of the three, because that reasoning is the answer.
How would you calculate a running total?
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date). Be ready to explain that adding
ORDER BY inside the window changes the default frame from the whole partition to everything up to
the current row — which is precisely what makes it cumulative.
How would you compare each month with the previous month?
LAG(revenue) OVER (ORDER BY month) gives you the previous row’s value on the same row, so the
difference is a simple subtraction. LEAD does the same in the other direction. Mention that gaps
in the data will silently compare non-adjacent months unless you join against a complete date
series first.
Structure: testing how you organise thinking
When would you use a CTE instead of a subquery?
Readability, mostly. A CTE names an intermediate step, which makes a multi-step query reviewable and lets you reference the same intermediate result more than once. Recursive CTEs also handle hierarchies, which subqueries cannot.
What is the difference between UNION and UNION ALL?
UNION removes duplicates, which requires a sort and costs time. UNION ALL keeps everything and
is faster. Default to UNION ALL unless you specifically need deduplication — using UNION out of
habit on large tables is a common and avoidable performance mistake.
The question that is not about SQL
You run a query and the number looks wrong. What do you do?
This is the most revealing question in an analyst interview and it has no syntax in the answer.
Say that you check the grain first — what one row is meant to represent — then the row counts around each join, then whether NULLs are being dropped by a filter or an aggregate, then whether the date range is doing what you think. Say that you validate against a known total from another source before you believe your own output.
Candidates who answer this well are usually the ones who get the offer, because it is the closest thing in the interview to the actual job.