SQL interview questions
SQL questions a Data analyst / BI can be asked, from first job to senior. Open one to see the strongest answer and why it works.
A stakeholder asks for “orders per customer.” What do you pin down first?
The strongest answer: The grain — one row per order, per customer-day, or per customer-lifetime — then the join, not a chart that silently mixes them
Why it works: Exactly. Grain is the question. A pretty mix of grains is how two people get two numbers.
- Name the grain first.
- Then join.
- Do not mix grains in one number.
- Do not average averages to paper over it.
What is the difference between an INNER JOIN and a LEFT JOIN?
The strongest answer: INNER returns only rows that match on both sides; LEFT returns every row from the left table and fills the right side with NULLs where there is no match — so LEFT is how you keep the customers with no orders
Why it works: Right. The question to ask is: do I want rows with no match to survive?
- INNER: matches on both sides only.
- LEFT: all left rows, NULLs for missing right.
- Use LEFT to keep the ones without a match.
- A WHERE on the right table can silently turn LEFT into INNER.
When do you use LEFT JOIN instead of INNER?
The strongest answer: When you must keep the left grain even if the right side is missing — customers without orders still exist
Why it works: Exactly. INNER drops; LEFT keeps. Choose from the grain, not from a fear of NULL.
- LEFT keeps the left grain.
- INNER drops unmatched.
- Choose from the question.
- Do not hide a drop.
Why does WHERE status != 'closed' not return rows where status is NULL?
The strongest answer: Because NULL is unknown, and any comparison with NULL is neither true nor false — the row is filtered out; use IS NULL or COALESCE to include them deliberately
Why it works: Exactly. Three-valued logic: comparisons with NULL are unknown and WHERE keeps only true.
- NULL means unknown, not empty.
- Comparisons with NULL are unknown, so WHERE drops them.
- IS NULL / IS NOT NULL to test.
- COALESCE to give NULL a stand-in value.
You need last month’s paid orders joined to customers. Where does the date filter go?
The strongest answer: On the orders side, before or in the join logic, so you do not multiply then “filter the mess” — and you say whether cancelled rows count
Why it works: Exactly. Filter the fact at the right grain. Chart-only filters are how extracts lie.
- Filter the fact at its grain.
- Say who is excluded.
- Do not leave it to the chart.
- Caches are not a where clause.
When do you use HAVING instead of WHERE?
The strongest answer: HAVING filters after grouping, on aggregates — customers with more than five orders; WHERE filters rows before they are grouped
Why it works: Right. WHERE first on rows, GROUP BY, then HAVING on the groups.
- WHERE: rows, before grouping.
- HAVING: groups, after aggregation.
- COUNT, SUM, AVG live in HAVING.
- Order: WHERE → GROUP BY → HAVING.
A join fans one customer into five rows. What does COUNT(*) now mean?
The strongest answer: Rows after the fan-out, not customers — if you want people, you need a distinct of the customer key or a pre-aggregate
Why it works: Exactly. COUNT(*) counts rows. Fan-out is how “users” become a multiple.
- COUNT(*) = rows.
- People need a distinct key or a pre-agg.
- Name the fan-out.
- Bigger is not safer.
What is a primary key, and what is a foreign key?
The strongest answer: A primary key uniquely identifies each row and cannot be NULL; a foreign key is a column that references another table's primary key, and the database enforces that the referenced row exists
Why it works: Exactly. Identity on one side, an enforced reference on the other.
- Primary key: unique, not NULL, identifies the row.
- Foreign key: references a primary key elsewhere.
- Referential integrity is enforced by the database.
- Keys are constraints; indexes are structures.
What does an index do, and what does it cost?
The strongest answer: It lets the database find rows by a column without scanning the whole table, like a book's index; it costs storage and makes every insert, update and delete on that column slower because the index must be maintained
Why it works: Right. Faster reads on that column, a tax on every write.
- Index: fast lookup on a column.
- Costs storage and write time.
- Index what you filter and join on.
- Every index is maintained on every write.
“Orders on 1 September” differs in Tel Aviv and UTC. What do you write on the dashboard?
The strongest answer: The timezone and the clock the business uses — I do not leave “the date” unnamed
Why it works: Exactly. A day is a cut on a clock. Unnamed days are how two teams fight.
- Name the timezone.
- Use the business clock.
- Do not pick the bigger cut.
- Do not use the laptop’s zone.
What is the difference between LIKE 'smith%' and LIKE '%smith'?
The strongest answer: The first matches values starting with smith and can use an index; the second matches values ending with smith and must scan every row because an index cannot help with a leading wildcard
Why it works: Right. A leading wildcard throws away the index.
- % matches any characters.
- Prefix patterns can use an index.
- Leading % forces a scan.
- Full-text search for real text queries.
A report shows duplicate customers. Is SELECT DISTINCT the fix?
The strongest answer: Usually not — duplicates from a join mean the join or the grouping is wrong; DISTINCT hides the symptom and costs a sort, so find why rows multiply first
Why it works: Right. DISTINCT on a wrong join is a bandage over a bug.
- Ask why rows are duplicated.
- A one-to-many join multiplies rows.
- DISTINCT hides the cause and costs a sort.
- Aggregate or fix the join instead.
What does ORDER BY ... LIMIT 10 without an index on the sort column cost?
The strongest answer: The database must read every matching row and sort them all before it can take ten; with an index on the sort column it can walk the index and stop after ten
Why it works: Right. Sort first, then limit — unless the index already provides the order.
- ORDER BY before LIMIT.
- Without an index: sort everything.
- Index on the sort column: stop after N.
- Top-N queries want the index.
What is the difference between COUNT(*) and COUNT(email)?
The strongest answer: COUNT(*) counts rows; COUNT(email) counts rows where email is not NULL, so the two differ whenever the column has NULLs
Why it works: Right. Column counts skip NULLs; star counts rows.
- COUNT(*): rows.
- COUNT(col): non-NULL values.
- COUNT(DISTINCT col): distinct non-NULL values.
- NULLs are the difference.
UNION versus UNION ALL — which do you reach for by default?
The strongest answer: UNION ALL, unless you need duplicates removed: UNION deduplicates, which means a sort or hash over the whole result, and most of the time the two sides cannot overlap anyway
Why it works: Right. ALL is cheaper; use plain UNION only when duplicates are a real possibility you want gone.
- UNION: deduplicates, costs a sort.
- UNION ALL: keeps everything, cheap.
- Default to ALL.
- Same column count and compatible types.
What does a transaction guarantee, in plain words?
The strongest answer: That a group of statements either all take effect or none do — a transfer debits one account and credits another or does neither — and that once committed the change is durable
Why it works: Right. All or nothing, and permanent once committed.
- All statements commit or none do.
- Committed means durable.
- Wrap multi-step changes in one transaction.
- Keep transactions short.
Why must user input go into a query as a parameter rather than pasted into the string?
The strongest answer: Because a parameter is sent as data and can never change the query's structure; string concatenation lets ' OR 1=1 -- become part of the SQL, which is SQL injection
Why it works: Exactly. Parameters keep data as data.
- Never concatenate user input into SQL.
- Parameters separate data from structure.
- Applies to every input and type.
- ORMs parameterise for you — until you write raw SQL.
SELECT customer_id, SUM(amount) without GROUP BY — what is wrong?
The strongest answer: The grain is mixed — an aggregate next to a raw key is either an error or a hidden window; I group or I window on purpose
Why it works: Exactly. Aggregates need a grain. Old engines that guessed a group are not a standard.
- Aggregates need GROUP BY or a window.
- Name the grain.
- Do not rely on old dialects.
- ORDER BY is not a group.
You LEFT JOIN orders, then WHERE orders.status = 'paid'. What did you just do?
The strongest answer: Turned the LEFT into an INNER — unpaid and no-order customers disappear; I filter in the join or keep NULLs on purpose
Why it works: Exactly. A WHERE on the right side drops the unmatched. That is an INNER with extra steps.
- WHERE on the right kills the LEFT.
- Filter in the ON or accept the INNER.
- NULLs are the unmatched.
- Say which you meant.
You need each order and the customer’s lifetime total on the same row. What do you use?
The strongest answer: A window sum partitioned by customer — I do not join the table to an aggregate of itself if a window is clearer, and I do not GROUP BY and lose the order grain
Why it works: Exactly. Windows keep the grain and add a measure. GROUP BY collapses it.
- Window to keep the row grain.
- GROUP BY collapses.
- Do not hope dialects keep extras.
- Spreadsheets are not the join.
You join orders to refunds (many refunds per order) then average order value. What bites you?
The strongest answer: The average is now refund-weighted unless you pre-aggregate refunds to order grain — fan-out changes the denominator
Why it works: Exactly. Pre-agg to the grain you will average. DISTINCT on amount is a different lie.
- Pre-aggregate to the measure grain.
- Fan-out changes weights.
- More rows ≠ more units.
- Do not DISTINCT the measure.
A customer dimension has two rows per person after an address change. How do you join a fact?
The strongest answer: On the key and the date range that was true when the fact happened — or a current flag if the question is “now” — I do not blindly join on id
Why it works: Exactly. SCD is a time join. Blind id joins double or mis-attribute.
- Join key + valid time, or say “current.”
- Blind id joins fan out.
- Latest ≠ as-was.
- Do not sum people.
A “helpful” dashboard takes a raw table name from a query string. What do you say?
The strongest answer: That is injection — allow-list the names or use parameters; I do not concatenate SQL from the browser
Why it works: Exactly. Analysts write SQL that runs. Internal is still a boundary.
- Allow-list or parameters.
- No concatenated table names.
- Internal ≠ safe.
- Do not hand-escape.
You want each user’s first paid order date next to every order. How?
The strongest answer: A window MIN(paid_at) or a pre-agg joined back — I do not correlate a MIN in a way that re-filters the detail incorrectly
Why it works: Exactly. Partitioned min or a clean dim. Global MIN is everyone’s first as one date.
- Window or pre-agg per user.
- Keep the order grain.
- Do not use a global MIN.
- Do not trust a spreadsheet sort.
A query that was fine on a sample scans the lake for 20 minutes in prod. What do you look at?
The strongest answer: Partition filters, selected columns, and whether I blew the partition prune with a function on the date — I do not SELECT * “for now”
Why it works: Exactly. Pruning and projection. Bigger boxes fund a bad scan. Averaging clocks is not a plan.
- Keep partition filters naked.
- Project only needed columns.
- No SELECT * for now.
- Size last.
A popular dashboard scans a year of raw events every open. What do you do?
The strongest answer: Pre-aggregate to the grain the chart needs, and partition/filter the rest — I do not ask people to “open it less”
Why it works: Exactly. Shape the data to the question. Guilt and disable are not modelling.
- Roll up to the chart grain.
- Keep filters sargable.
- Size last.
- Do not guilt people first.
A junior’s query is correct and unreadable. How do you review?
The strongest answer: Rename, CTE the grains, and keep their name on the win — I do not rewrite it as a flex
Why it works: Exactly. Teach the shape. Stealing and rubber-stamping are how you lose them or the next reader.
- Name the grains in CTEs.
- Keep their credit.
- Do not hero-rewrite.
- Matching a number is not enough.
You need “the plan the customer was on when they churned.” What join?
The strongest answer: A point-in-time join to the subscription history — current plan is a different question
Why it works: Exactly. As-was versus as-is. Random rows are not a method.
- Point-in-time to history.
- Name as-was vs as-is.
- Do not use current by habit.
- Do not pick a random row.