SQL & data access interview questions
9 questions across 3 sections, in the order you would learn them. Every answer here is one that works — but an answer you have read is not an answer you can give, so use Test yourself and say it out loud first.
Getting the data out
Read the section that teaches thisIf this comes up in an interview
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. You cannot reference an aggregate in WHERE because it has not been computed yet.
When do you use a LEFT JOIN?
When you need every row from the left side regardless of whether there is a match, and absence is meaningful. Watch the counting trap: COUNT(*) counts the unmatched row, COUNT(right_column) does not.
A query is slow. How do you diagnose it?
EXPLAIN ANALYZE and look at whether it is scanning the table or using an index, and how many rows it reads compared to how many it returns. A sequential scan on a large table where you filter on a column usually means a missing index.
What is a window function for?
Computing across a set of related rows while keeping every row, rather than collapsing them as GROUP BY does. Ranking within a group, comparing to the previous row, and taking the latest record per entity are the common uses.
The queries you will actually be asked to write
Read the section that teaches thisIf this comes up in an interview
Write a query for each user's most recent order.
Rank orders within each user by created_at descending with ROW_NUMBER in a CTE, then keep rows where the rank is 1. Mention that if two orders share a timestamp, you need a tiebreaker such as the id.
Then they push further
The table has a billion rows and this is slow. What do you do?
Check the plan. An index on (user_id, created_at) helps. If this is needed often, maintain a latest-order table or column updated on write rather than computing it on every read.
Could you do it without a window function?
Yes: join each order to the maximum created_at for its user from a grouped subquery. It is usually less clear and handles ties worse, which is why the window version is preferred.
What gets marked up. Marked up for handling ties explicitly and for thinking about indexes. Correct SQL with no mention of either passes; with both it stands out.
Why can NOT IN return nothing?
If the subquery contains a NULL, the comparison with that NULL is unknown for every row, so no row satisfies NOT IN. NOT EXISTS does not have this problem.
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL.
Writing data safely
Read the section that teaches thisIf this comes up in an interview
A client retries a request after a timeout. How do you make sure it is not processed twice?
The client sends a unique request id. The server stores it with a unique constraint as part of the same transaction as the work, and on conflict returns the original result instead of repeating the work.
Then they push further
What if the work includes calling an external API that is not idempotent?
Record the intent with the request id before calling, and on retry check that record and ask the external system what happened rather than calling again.
What gets marked up. Marked up for enforcing it in the database with a constraint rather than only checking in application code, which races.
What is a race condition in a read-modify-write, and how do you prevent it?
Two transactions read the same value, each modifies it, and one write overwrites the other. Prevent it by doing the update atomically in one statement, locking the row before reading, or using optimistic concurrency with a version column.
These are the questions for one topic. The whole question bank covers every topic on the site.