SQL Interview Questions for Data Scientists 2026

SQL is still the single most-tested skill in data science interviews — more than Python, more than statistics. A 2025 Interviewing.io analysis showed 78% of data scientist screens included a live SQL exercise. This guide collects the patterns that actually come up, the questions repeated almost verbatim across companies, and the moves that separate a "good" answer from a hireable one.

The Five Patterns That Cover 80% of Questions

Almost every data science SQL interview pulls from the same playbook. Master these five and you'll recognize most prompts on sight.

1. Aggregation with filtering — "Find users who did X at least N times in period Y."
2. Window functions for ranking — "Top 3 per category" / "Nth highest" / "running total."
3. Self-joins and date arithmetic — "Day-over-day retention," "consecutive logins," "first vs. latest event per user."
4. CTEs for multi-step logic — "Compute X, then filter on X, then aggregate."
5. Edge cases — NULLs, ties, duplicate joins, empty result sets.

For a refresher on the underlying syntax, our SQL for data science guide covers the operators you should already know before walking into an interview.

Pattern 1: Aggregation With Filtering

Q1. Find customers who made more than 3 purchases in the last 30 days.

SELECT customer_id, COUNT(*) AS purchase_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY purchase_count DESC;

What interviewers grade: did you use HAVING (correct) or wrap in a subquery with WHERE (works but sloppy)? Did you handle the date interval syntax for the dialect you're in? Did you cast properly if order_date is a timestamp?

Q2. What is the average order value per customer, excluding customers with fewer than 5 orders?

This combines aggregation, filtering, and a second aggregation. Most candidates over-engineer it.

SELECT AVG(customer_avg) AS overall_avg
FROM (
 SELECT customer_id, AVG(order_total) AS customer_avg
 FROM orders
 GROUP BY customer_id
 HAVING COUNT(*) >= 5
) sub;

The gotcha: averaging averages isn't always equivalent to a single grand AVG(order_total) because customers with more orders get equal weight in the inner query. Interviewers want you to explicitly call out this distinction.

Pattern 2: Window Functions

Window functions are the single biggest separator between junior and senior SQL. If ROW_NUMBER, RANK, DENSE_RANK, and LAG/LEAD aren't second nature, drill them before any interview.

Q3. Find the top 3 highest-paid employees in each department.

SELECT department, name, salary
FROM (
 SELECT department, name, salary,
 DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
 FROM employees
) ranked
WHERE rnk <= 3;

The classic mistake: using ROW_NUMBER instead of DENSE_RANK when the question says "top 3" with possible ties. If two people tie at #2, ROW_NUMBER picks one arbitrarily, RANK skips #3, DENSE_RANK includes both at #2 and continues at #3. Always clarify what behavior the interviewer wants.

Q4. Calculate a 7-day rolling average of daily active users.

SELECT day,
 AVG(dau) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily_metrics
ORDER BY day;

Watch the frame clause: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives you 7 rows. Off-by-one is the most common bug.

Q5. For each user, find the time between their first and second login.

WITH ranked AS (
 SELECT user_id, login_time,
 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS rn
 FROM logins
)
SELECT user_id,
 MAX(CASE WHEN rn = 2 THEN login_time END)
 - MAX(CASE WHEN rn = 1 THEN login_time END) AS time_diff
FROM ranked
WHERE rn <= 2
GROUP BY user_id;

The cleaner alternative uses LEAD: LEAD(login_time) OVER (PARTITION BY user_id ORDER BY login_time). Both work — show the second if asked for an alternative.

Pattern 3: Self-Joins and Date Arithmetic

Q6. Find consecutive days where revenue grew.

WITH daily AS (
 SELECT day, SUM(amount) AS revenue
 FROM orders GROUP BY day
)
SELECT a.day, a.revenue, b.revenue AS prev_revenue
FROM daily a
JOIN daily b ON a.day = b.day + INTERVAL '1 day'
WHERE a.revenue > b.revenue;

The window-function alternative with LAG is usually preferred in modern interviews — show both if you have time.

Q7. Day-7 retention: of users who signed up on day X, how many returned within 7 days?

SELECT s.signup_date,
 COUNT(DISTINCT s.user_id) AS cohort_size,
 COUNT(DISTINCT CASE 
 WHEN a.activity_date BETWEEN s.signup_date + 1 AND s.signup_date + 7
 THEN a.user_id END) AS retained_7d
FROM signups s
LEFT JOIN activity a ON s.user_id = a.user_id
GROUP BY s.signup_date;

The LEFT JOIN is critical — without it, users who never returned drop out entirely. This is the single most common mistake on retention questions.

Pattern 4: CTEs for Multi-Step Logic

Modern interviewers prefer CTEs over nested subqueries. They're easier to read, easier to debug, and signal that you've written real production SQL.

Q8. Find the second-highest salary in each department, excluding interns.

WITH filtered AS (
 SELECT * FROM employees WHERE role != 'intern'
),
ranked AS (
 SELECT department, name, salary,
 DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
 FROM filtered
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2;

The same logic in nested subqueries reads as gibberish. Use CTEs.

Q9. For each product, what percentage of total revenue does it represent?

WITH product_revenue AS (
 SELECT product_id, SUM(amount) AS revenue
 FROM orders GROUP BY product_id
)
SELECT product_id, revenue,
 ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM product_revenue
ORDER BY revenue DESC;

The empty OVER () window is the trick — it makes SUM operate on the whole result set without a partition.

Pattern 5: Edge Cases (The Senior-Level Differentiator)

Most candidates pass through Patterns 1–4 fine. They get cut at the edge cases.

Q10. Count distinct users, treating NULLs as a separate group.

COUNT(DISTINCT user_id) excludes NULLs. If you want NULLs counted, use COUNT(DISTINCT COALESCE(user_id, 'NULL')). Senior interviewers love this question because it separates the "I know SQL" candidates from "I've debugged SQL in prod."

Q11. What happens when you JOIN two tables with duplicate keys?

The answer: row explosion. If left has 3 rows with key X and right has 2 rows with key X, the join produces 6 rows. Real-world consequence: aggregate functions silently double-count. Always check cardinality before joining, especially on engagement tables.

Q12. Why is WHERE col = NULL always false?

NULL comparisons require IS NULL or IS NOT NULL. Equality with NULL returns NULL, which evaluates as false in WHERE. This is on every interviewer's "easy trap" list. For deeper data-quality patterns, see our guide on handling missing data.

How Interviewers Actually Grade You

Three things determine pass/fail beyond correctness:

1. You think out loud. Silent candidates get cut even if they write correct SQL. Narrate your trade-offs.
2. You sanity-check your output. Mentally walking through "for one user, this returns X rows" catches 90% of mistakes before submission.
3. You handle the follow-up. "Now do this without a window function" or "now make it work on a 1B-row table" is where the bar is set.

For broader interview prep beyond SQL, see our data scientist portfolio guide for the projects that get you to on-site interviews.

Pattern 6: Subqueries vs CTEs — Readability Matters

As SQL projects scale, choosing between subqueries and CTEs significantly impacts maintainability. Modern data teams heavily favor CTEs because they read like natural prose — each CTE is a named intermediate step, making the logic transparent.

-- Subquery approach (hard to read — nested like Russian dolls)
SELECT department, avg_salary
FROM (
 SELECT department, AVG(salary) AS avg_salary
 FROM (
   SELECT * FROM employees WHERE hire_date > '2020-01-01'
 ) recent_hires
 GROUP BY department
) dept_avgs
WHERE avg_salary > 80000;

-- CTE approach (clear, layer-by-layer)
WITH recent_hires AS (
 SELECT * FROM employees WHERE hire_date > '2020-01-01'
),
dept_avgs AS (
 SELECT department, AVG(salary) AS avg_salary
 FROM recent_hires
 GROUP BY department
)
SELECT department, avg_salary
FROM dept_avgs
WHERE avg_salary > 80000;

The second version reads like an outline. At scale (500+ line queries), CTEs beat nested subqueries because engineers can trace logic without scanning for matching parentheses. Interviewers test this because it signals you've worked on real codebases.

Q13. Find users who made purchases in all 12 months of 2025.

WITH monthly_purchases AS (
 SELECT DISTINCT user_id, EXTRACT(MONTH FROM order_date) AS month
 FROM orders
 WHERE EXTRACT(YEAR FROM order_date) = 2025
)
SELECT user_id, COUNT(DISTINCT month) AS months_active
FROM monthly_purchases
GROUP BY user_id
HAVING COUNT(DISTINCT month) = 12
ORDER BY user_id;

The key: DISTINCT on the month before aggregation prevents double-counting if a user made multiple purchases in the same month. Without it, counting unique months still works due to DISTINCT month, but the logic is clearer.

Pattern 7: Performance and Query Optimization

An interviewer might follow up: "Make this work efficiently on a 10 billion row table." This is where senior-level thinking separates from mid-level.

Q14. Optimize: Count distinct users per day efficiently.

-- Version 1: Direct (actually fine for most scales)
SELECT order_date, COUNT(DISTINCT user_id) AS daily_active
FROM orders
GROUP BY order_date;

-- Optimization: Index strategy (real world)
-- CREATE INDEX idx_orders_date_user ON orders(order_date, user_id);
-- This "covering index" lets the DB scan the index without touching the table.

Real optimization is usually infrastructure (indexes, partitioning, columnar storage) not query rewrites. But know the gotchas:

-- ❌ Slow on high-cardinality columns (billions of distinct values)
SELECT COUNT(DISTINCT customer_id) FROM transactions;

-- ✅ Faster: Estimate with HyperLogLog (Postgres)
SELECT hll_cardinality(hll_add_agg(hll_hash_bigint(customer_id))) FROM transactions;

Knowing HyperLogLog (approximate distinct counts), sharding strategies, or when to use APPROX_DISTINCT shows real depth in interviews.

Q15. Why does a query slow down when data doubles but indexes exist?

Common causes:

1. Correlated subquery — Re-executes per row, turning O(N) into O(N²).
2. Hash join buffer overflow — Query doesn't fit in memory, spills to disk (100× slower).
3. Planner mistake — Cardinality estimates are wrong; optimizer picks bad join order.
4. Lock contention — Other queries holding locks on the same tables.
5. Full table scan despite index — Index on the wrong column or selectivity too high.

Explaining these shows you think about complexity, not just syntax.

Building Confidence: Practice Strategy

Most candidates spend weeks memorizing syntax. The top performers spend time understanding when to use each pattern.

Deliberate practice workflow:

1. Do the problem cold — No hints, no looking at solutions. 10 minutes max. Write something, even if it feels wrong.
2. Check the optimal solution — Read 2–3 versions. Note which one is clearest.
3. Understand the tradeoff — Why is this better? Is it always better, or just for this case?
4. Teach it back — Explain the solution to a friend or write a comment block. If you can't explain it, you don't own it.
5. Solve it again in 1 week — Without looking at your notes. Did you remember the pattern?

Doing 30 problems this way beats 100 problems rushed.

Common Mistakes That Cost Offers

1. Silent coding — Typing without narrating. Interviewers can't grade what they don't hear.
2. Not testing edge cases — "What if a user has zero orders?" Test it. Add a WHERE clause.
3. Assuming schema — "Let's say there's a user_id column." Ask first: "Can I see the table definitions?"
4. Over-engineering — A simple query beats a clever one. KISS.
5. Forgetting NULL handling — NULL breaks equality. Use IS NULL, not = NULL.

These cost interviews because they signal inexperience with production code.

---

Frequently Asked Questions

Q: Which SQL dialect should I learn for interviews?
A: PostgreSQL is the 2026 standard for tech interviews — it's the default at startups and has clean, standard-compliant syntax. BigQuery and Snowflake are close cousins; learn PostgreSQL and adapting takes 1-2 hours.

Q: How much time should I spend preparing?
A: If you've used SQL professionally, 2–3 weeks of daily practice (LeetCode SQL or DataLemur) is enough. Starting from scratch: 6–8 weeks minimum. Window functions are the difficulty spike.

Q: Is StrataScratch worth the premium subscription?
A: Yes, if you want company-specific question banks tagged by employer (Google, Amazon, etc.). DataLemur is free and covers fundamentals; StrataScratch paid accelerates learning if you're in a time crunch.

Q: Do I need to memorize window function syntax?
A: Absolutely yes. You cannot look things up during a live interview. OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) should be second nature — write it 100 times if needed.

Q: What comes after SQL in data science interviews?
A: SQL gets you past the screen; modeling, statistics, and coding get you the offer. Once you pass the SQL screen, review machine learning concepts and system design — see our guide on how machine learning actually works.

Q: Should I use ChatGPT while practicing?
A: Use it for hints and explanations only, not for solving. Solve problems cold first, then ask the model to grade your work. Otherwise you'll skip the pattern-recognition step that transfers to interviews.

Pattern 8: Handling Duplicates and Data Quality in SQL

Data quality is a silent killer in SQL interviews. Even perfect logic on perfect data gets crushed by messy reality. Senior interviews often include a question like: "Handle duplicates in this dataset" or "This query returns 3x expected rows — debug it."

Q16. Identify duplicate users (same email, different IDs).

WITH duplicates AS (
 SELECT email, COUNT(*) AS count
 FROM users
 GROUP BY email
 HAVING COUNT(*) > 1
)
SELECT u.*
FROM users u
JOIN duplicates d ON u.email = d.email
ORDER BY u.email, u.user_id;

Then the follow-up: "For each email, keep the oldest user, delete the rest." This requires thinking about idempotent deletes — code that's safe to run multiple times without creating chaos.

DELETE FROM users
WHERE user_id NOT IN (
 SELECT MIN(user_id)
 FROM users
 GROUP BY email
);

This is safe: running it twice changes nothing the second time. If you'd written DELETE FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1), you'd delete the first user on the second run — a disaster.

Q17. Why do LEFT JOINs sometimes silently cause overcounting?

-- Naive: join orders to order_details
SELECT o.order_id, COUNT(*) AS item_count
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id;

-- This counts correctly — only orders with details.

-- But add a second LEFT JOIN:
SELECT o.order_id, COUNT(*) AS item_count
FROM orders o
LEFT JOIN order_details od ON o.order_id = od.order_id
LEFT JOIN reviews r ON od.detail_id = r.detail_id
GROUP BY o.order_id;

-- Now each review row is counted multiple times if there are multiple details per order!
-- One order with 2 details and 3 reviews becomes COUNT = 6.

The fix: use a subquery or CTE to aggregate separately, then join the aggregates:

WITH detail_counts AS (
 SELECT order_id, COUNT(*) AS item_count
 FROM order_details
 GROUP BY order_id
),
review_counts AS (
 SELECT order_detail_id, COUNT(*) AS review_count
 FROM reviews
 GROUP BY order_detail_id
)
-- Now join these aggregates without creating a Cartesian product

This is a classic "gotcha" that separates candidates who've shipped production code from those who've only practiced.

Source: Kaggle SQL Course (2024) — Join cardinality and data integrity patterns.

Pattern 9: Advanced Window Functions for Competitive Analysis

Window functions go beyond ranking. They enable complex cohort analysis that normally requires loops or self-joins.

Q18. For each customer, find their nth purchase and compare to the cohort median.

WITH customer_purchases AS (
 SELECT customer_id, order_date, amount,
 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS purchase_num,
 PERCENTILE_CONT(0.5) OVER (PARTITION BY customer_id) AS customer_median
 FROM orders
),
nth_purchase AS (
 SELECT *,
 PERCENTILE_CONT(0.5) OVER (ORDER BY amount) AS global_median
 FROM customer_purchases
 WHERE purchase_num = 5  -- Example: 5th purchase
)
SELECT customer_id, amount, customer_median, global_median,
 ROUND(100.0 * (amount - global_median) / global_median, 2) AS pct_vs_global
FROM nth_purchase
ORDER BY pct_vs_global DESC;

This shows customers whose 5th purchase is above the global median for 5th purchases — useful for identifying high-value customer cohorts early.

Q19. Tenure-based retention: for each signup cohort, survival rate at 30, 60, 90 days.

WITH signups AS (
 SELECT user_id, signup_date,
 DATE_TRUNC('month', signup_date) AS cohort_month
 FROM users
),
activity_flags AS (
 SELECT s.user_id, s.cohort_month,
 MAX(CASE WHEN a.activity_date <= s.signup_date + 30 THEN 1 ELSE 0 END) AS active_30d,
 MAX(CASE WHEN a.activity_date <= s.signup_date + 60 THEN 1 ELSE 0 END) AS active_60d,
 MAX(CASE WHEN a.activity_date <= s.signup_date + 90 THEN 1 ELSE 0 END) AS active_90d
 FROM signups s
 LEFT JOIN activity a ON s.user_id = a.user_id
 GROUP BY s.user_id, s.cohort_month
)
SELECT cohort_month,
 COUNT(*) AS cohort_size,
 ROUND(100.0 * SUM(active_30d) / COUNT(*), 1) AS pct_retained_30d,
 ROUND(100.0 * SUM(active_60d) / COUNT(*), 1) AS pct_retained_60d,
 ROUND(100.0 * SUM(active_90d) / COUNT(*), 1) AS pct_retained_90d
FROM activity_flags
GROUP BY cohort_month
ORDER BY cohort_month;

This is real-world analysis that data scientists run constantly. Show this and you've immediately looked senior.

Source: Mode Analytics (2024) — Cohort retention SQL patterns.

Performance Optimization for Large Datasets

Interview follow-ups often include: "This query runs fine on 1 million rows but times out on 1 billion. How would you optimize?"

Index strategy: Most queries are limited by full table scans. If you're filtering on user_id and order_date, create a composite index: CREATE INDEX idx_orders_user_date ON orders(user_id, order_date). The optimizer can use this for both filtering and sorting.

Partitioning: On a 10B row orders table, partition by order_year. Queries filtering on recent years only scan relevant partitions, skipping historical data.

Materialized views: For aggregate queries run repeatedly (like daily revenue reports), create a materialized view computed overnight and refreshed hourly. Query the view (fast) instead of computing aggregates live (slow).

Approximate aggregations: For massive datasets, you sometimes don't need exact counts. Approximate distinct counts using HyperLogLog are 100× faster than exact counts and accurate to within 2%.

-- Exact (slow on billions of rows)
SELECT COUNT(DISTINCT user_id) FROM clicks;

-- Approximate (fast)
SELECT hll_cardinality(hll_add_agg(hll_hash_bigint(user_id))) FROM clicks;

Knowing this separates candidates who think "write the correct query" from candidates who think "write the correct query that fits the infrastructure."

The Takeaway

SQL interview success comes down to three things: pattern recognition (knowing the five archetypes), edge case thinking (NULLs, duplicates, ties), and production awareness (thinking about data quality, performance, and maintainability). Master window functions and CTEs, always use LEFT JOIN when unsure, and narrate your reasoning aloud. The model query is always the clearest query, not the cleverest one.

---

Read next:

• Data Cleaning: 80% of a Data Scientist's Job
• How to Handle Missing Data — Imputation Strategies
• Python vs R for Data Science