SQL interview questions for data science, worked through
Data science SQL rounds ask product questions: how many people were active, how many came back, where a funnel leaks. The query is the easy half. The half that gets scored is pinning down what one row of the answer means, what counts as active, how ties and missing values behave, and how you would check the result before you trust it.
Key points
- Before typing, say what one output row represents and what defines each metric.
- Build the query in named steps (CTEs) and say each step as you write it.
- Watch for rows a join can multiply, NULLs that drop out of comparisons, and time zones that move events across days.
- Window functions answer most “first”, “previous” and “running” questions.
- End with a check: a row count, a hand-computed case or a total that must match.
The tables used below
All examples use one invented app with two tables. Event times are stored in UTC.
| Table | Columns | Notes |
|---|---|---|
| users | user_id, signup_at, country | One row per user |
| events | event_id, user_id, event_name, event_at | Names include open_app, view_item, add_to_cart, purchase. The pipeline retries, so a few events arrive twice with the same event_id. |
Clarify before you write
Take “What is our daily active user count?”. Three questions settle most of it, and asking them out loud is what the interviewer is listening for:
- Which events count as active? Opening the app, or any event at all? A background refresh should probably not count.
- Whose day? A user in Tokyo who opens the app at 08:00 local time is still on the previous day in UTC. Product teams usually report in one business time zone; ask which.
- Duplicates: the retry problem means you count distinct users and, for event metrics, distinct event_id.
Worked examples
Daily actives. One CTE converts event_at to the reporting time zone and keeps only the events that count as active; the outer query groups by that local date and counts distinct user_id. Saying “distinct” out loud matters: the same user opening the app eight times is one active user.
Day-7 retention. For each user, the cohort day is the local date of signup_at. A user is retained on day 7 if they have at least one active event on cohort day + 7. Build a cohort CTE from users, left join to a CTE of distinct active days per user on user_id and on the date being exactly seven days later, and compute the share of cohort users with a match. The left join keeps users who never came back; an inner join would quietly drop them and push retention to 100%.
Funnel. View item, add to cart, purchase within one day. For each user and day, take the earliest time of each step with MIN(CASE WHEN ...), then count users whose times are in order. If a purchase can happen without a recorded view (a reorder button), say whether those users count; it changes the conversion rate.
First purchase. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_at, event_id) and keep row 1. The tie-breaker on event_id makes the result stable when two purchases share a timestamp. RANK ordered by event_at alone would keep both, which is wrong for “first” and right for “all purchases at the earliest time”.
A join that multiplies rows
Suppose user 42 has 3 purchase events and 4 view_item events on one day. Joining purchases to views on user_id and date gives 3 × 4 = 12 rows. Summing order values across that join counts each purchase four times.
Aggregate each side to one row per user and day first, then join. Say this before the interviewer has to ask; it is one of the follow-ups the SQL round is known for.
Talking through the query
“One row of my output is a signup date and the share of that day’s new users who were active seven days later.”
“First CTE: each user’s cohort date, in the business time zone. Second: the distinct days each user was active. I’m dropping duplicate events by event_id there.”
“Then a left join from cohorts to activity on user and on cohort date plus seven, so people who never returned stay in the denominator.”
“To check it, I’d pick one cohort day, count its users by hand, and confirm the numerator never exceeds the denominator.”
NULLs and other quiet errors
- WHERE country <> 'US' also drops rows where country is NULL, because a comparison with NULL is unknown and WHERE keeps only true rows. Ask whether unknown countries belong in the answer.
- COUNT(column) skips NULLs and COUNT(*) does not. Pick on purpose.
- AVG over a column with NULLs averages only the known values; a missing order value is not a zero.
- NOT IN with a subquery that returns a NULL returns no rows at all. NOT EXISTS avoids it.
- Integer division in some dialects turns 7 / 20 into 0. Cast before dividing when you compute a rate.
Follow-ups after the query works
Once the answer is right, the interviewer usually changes one thing. Three follow-ups are common, and each has a short spoken answer.
- The same answer in pandas: drop duplicates on event_id, convert the time zone, then groupby on the local date with nunique on user_id. Say which step matches which CTE.
- A much larger table: filter by date before joining, aggregate before joining, and ask whether the events table is partitioned by date so the filter skips whole partitions.
- A metric that moved: before writing more SQL, check whether the drop is in every platform, country and app version, or in one. A drop in one app version usually points to logging, not users.
Common mistakes
- Writing the query before saying what one output row means.
- Counting events when the question asks for users.
- Using an inner join for retention or funnel steps and losing the users who dropped out.
- Ignoring the time zone, so late-evening activity lands on the wrong day.
- Summing across a join that multiplies rows.
- Declaring the answer correct without a check against a number you can compute by hand.
Questions
Which SQL topics come up most in data science interviews?
Joins and their row counts, GROUP BY with conditional aggregates, window functions for first, previous and running values, date handling, and metrics such as retention and funnels.
Should I write one big query or several CTEs?
CTEs. Each one is a step you can name out loud and check on its own, and the interviewer can follow your plan.
What if I forget a function name in the interview?
Say what you need, for example “the previous event’s time for the same user”, and write the closest version you know. Logic and edge cases weigh more than exact syntax.
Practise this out loud with Vera
Mockich’s SQL round is the data science coding round: 45 min, two problems at regular and one deeper at lead, in SQL or pandas. Where a question has tables, your query runs on them and Vera reads the result, asks about ties, NULLs and joins, and gives at most three small nudges, never the answer. The report scores problem solving, code quality, complexity, testing and communication (plus production thinking at lead) and quotes what you said.
Related guides
Sources
We read these sources on 2026-11-03 for the topics product SQL rounds cover and the usual traps; the schema, the questions and the numbers are our own.
- [1] Alexey Grigorev, Data Science Interviews (SQL questions) (CC BY 4.0)
- [2] Shaw Lu, Beyond LeetCode SQL (anti-patterns and NULL behavior) (MIT)
- [3] KDnuggets, How to crack SQL interviews
- [4] HackerNoon, Stop grinding LeetCode SQL, do this instead