SQL Interview Questions for Data Roles: What to Know

Most candidates treat SQL interview prep as memorizing syntax. That's the wrong frame. Interviewers at data-heavy companies — Stripe, Careem, SABIC's digital teams — don't care that you know how to write a JOIN. They care whether you can reason about data under ambiguity, catch edge cases before they ask, and defend your approach when pushed.

If you've been running practice problems on LeetCode and feel ready, you might not be. Here's what the questions actually test.

SQL interview questions for data analyst and data scientist roles test three things: your query mechanics (JOINs, aggregations, window functions), your data modeling intuition (understanding table grain, handling NULLs, thinking about fan-out), and your communication under ambiguity (restating assumptions before writing, asking about edge cases mid-problem). According to DataLemur's analysis of 200+ company interview reports, window functions appear in roughly 40% of SQL problems at mid-to-senior data analyst roles — yet they're the category most candidates under-prepare. The question types you'll see most: aggregation and GROUP BY, multi-table JOINs, window functions (ROW_NUMBER, RANK, LAG), and complex multi-step problems requiring CTEs. To prepare, spend two weeks on fundamentals, one week on window functions specifically, and one week on timed mixed practice. Say your assumptions out loud. Know your target company's SQL dialect — Snowflake, BigQuery, Redshift, and Postgres all differ on syntax.

What kinds of SQL questions actually show up in data interviews?

A SQL interview question is a structured query problem where you must extract, aggregate, or transform a dataset — typically under time pressure and with a deliberately incomplete spec. They fall into four main buckets: aggregation and grouping, joins and data relationships, window functions and analytical queries, and multi-step problems requiring CTEs. Most candidates feel fine with the first two and struggle with the last two.

Window functions appear in roughly 40% of SQL problems at mid-to-senior data roles, according to DataLemur's analysis of 200+ company interview reports — yet most candidates spend less than 20% of their prep time on them.

The spec you're given in an interview is almost always underspecified. A table described as "users" with columns user_id, created_at, status doesn't tell you whether duplicate user IDs are possible, whether status can be NULL, or what the grain of the table is. Knowing what to ask about before writing is the first competency being evaluated.

What do most candidates get wrong about SQL interview questions?

The standard failure mode: see the prompt, immediately start typing, produce technically correct syntax, miss the data logic.

The right approach treats every SQL question as a short data modeling conversation before it becomes a coding exercise.

Start by restating the problem in data terms out loud. "So I need to find users who placed more than three orders in their first 30 days — I'm assuming one row per order and that created_at is the order timestamp, not account creation?" This surfaces assumptions before you're halfway through a flawed query, and it tells the interviewer you think like a data professional.

Three specific traps that catch experienced candidates off guard:

1. Forgetting NULLs. COUNT(column) ignores NULLs; COUNT(*) counts every row. In an interview context, the test dataset may have NULLs you don't expect. This one error changes your answer.
2. Using a window function in a WHERE clause. WHERE ROW_NUMBER() OVER (...) = 1 throws an error. The fix — wrap in a CTE or subquery — is basic, but forgetting it under pressure signals you've never run this in production.
3. Over-joining. If you're joining five tables for an aggregation that should require two, the interviewer is noting your data model understanding, not just your syntax.

After writing, verify your logic by mentally running the query on a two-row example. Candidates who catch their own edge cases before the interviewer points them out stand out.

How do window functions and CTEs actually get tested?

Window functions are the differentiating question type — not because they're hard once you know them, but because they reveal whether your SQL practice has been academic or real.

The canonical window function test: "Find the second-highest revenue order for each customer."

The right answer uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) wrapped in a CTE, then filters on rn = 2. If you reach for a subquery with MAX and NOT IN, it sometimes works — but it signals old-school thinking. The PARTITION BY / ORDER BY construct is what you should reach for first.

CTE questions test structuring, not just syntax. Interviewers sometimes present a complex multi-step problem — cohort analysis, funnel drop-off, month-over-month retention — where the cleanest solution requires three or four named CTEs. Candidates who write one long tangled query versus those who break it into readable named stages are immediately distinguishable. Readable queries are production-grade signals.

A LinkedIn 2024 Workforce Report found SQL listed as the most in-demand technical skill in data analyst job postings globally, appearing in 63% of descriptions — ahead of Python at 57%.

Worked examples from three different domains

What makes these examples useful is the pre-question, not the query — that's the pattern to internalize.

Example 1 — E-commerce (Careem / Noon)

Interviewer: "We have an orders table: order_id, customer_id, driver_id, amount, completed_at. Write a query to find the top 10% of customers by total spending in the last 90 days."

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  WHERE completed_at >= CURRENT_DATE - INTERVAL '90 days'
    AND status = 'completed'
  GROUP BY customer_id
),
ranked AS (
  SELECT *, NTILE(10) OVER (ORDER BY total_spend DESC) AS spending_decile
  FROM customer_totals
)
SELECT * FROM ranked WHERE spending_decile = 1;

Good candidate, before writing: "Are cancelled orders in this table? Should I filter by status? And is the 90-day window rolling from today or from a specific fixed date?"

That pre-question is the signal. The query is almost secondary.

Example 2 — Fintech (Tabby / Tamara)

Interviewer: "Given a transactions table: user_id, transaction_date, status, amount — find users who made at least one successful transaction every month for the past six months."

The trap: grouping by user and counting rows misses sparse months. The correct logic counts distinct month-year combinations per user and checks the count equals six. But before writing anything:

Good candidate: "Do you mean the six calendar months before today, or a rolling 180-day window? The query structure changes significantly."

Example 3 — Corporate analytics (SABIC / Aramco digital team)

Interviewer: "You have an employee_performance table. Write a query showing each employee's score against their department average, flagging anyone more than one standard deviation below."

The solution uses STDDEV() OVER (PARTITION BY dept_id) and AVG() OVER (PARTITION BY dept_id) as window functions, then a CASE expression to flag outliers. But the differentiating question:

Good candidate: "Should I use population or sample standard deviation? STDDEV defaults to sample. If this is the full employee population for each department, STDDEV_POP would be more accurate."

That one question marks the candidate as someone who has used statistical SQL in production, not just practiced it.

What separates a passing answer from a senior-level one?

Senior candidates treat SQL questions as data modeling conversations, not code exercises.

Passing answer: correct query on the test dataset, delivered in time.

Senior answer: correct query, plus one question about data quality, plus one note on performance at scale, plus one alternative approach explicitly mentioned and rejected.

In practice, those additions sound like:

• "I wrote this as a CTE chain because it's readable, but if this table has 100 million rows you'd want to push the filter down earlier to avoid materializing the full dataset."
• "This JOIN assumes no duplicates in the customer table. If there are any, the JOIN creates row fan-out and my SUM will be inflated — should I add a deduplication step?"
• "This syntax works in Postgres; in Snowflake, QUALIFY would be cleaner. What dialect is your data warehouse?"

Those three types of comments — performance awareness, data quality checking, dialect awareness — separate a mid-level from a senior data hire. They're also easy to practice once you know to look for them.

SQL vs Python: what interviewers are really comparing

Some interviewers ask directly: "Would you do this in SQL or Python?" Others simply note which tool you reach for first. Either way, the comparison comes up in data role interviews.

Pick SQL when the task involves aggregating or filtering a database table. Pick Python when the task involves reshaping data, building models, or iterating exploratory logic.

Most data roles expect fluency in both. In practice, senior analysts run SQL to extract and aggregate, then Python to transform and analyze. Knowing where each tool fits — and being able to explain that choice in an interview — is itself a signal. IntervYou's mock sessions include both SQL and Python questions, so you can practice switching between them the way a real data interview requires it.

How should you practice SQL for data interviews?

Four weeks is enough if you're deliberate.

• Week 1: Aggregation, GROUP BY, HAVING, multi-table JOINs. Do 20 problems on DataLemur or Mode Analytics. Time each one.
• Week 2: Window functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, running totals with SUM OVER. Focus on understanding PARTITION BY before ORDER BY.
• Week 3: CTEs and multi-step analytical problems. Rewrite at least five of your Week 1 queries to be more readable. Practice cohort analysis and funnel problems.
• Week 4: Timed mixed practice. Cap each problem at 25 minutes. Say your assumptions out loud before writing. Fix your own edge cases before asking for a hint.

The thing most prep guides skip: learn your target company's SQL dialect before the interview. Snowflake's QUALIFY clause, BigQuery's UNNEST, Redshift's LISTAGG, and Postgres's GENERATE_SERIES are different enough to trip you up mid-answer. Fifteen minutes on a dialect cheat sheet pays out.

If you're interviewing at MENA companies — Careem, Tabby, SABIC, or regional banks — data teams commonly run Snowflake or BigQuery. IntervYou's mock sessions include SQL questions from those environments so you practice the actual dialect, not just academic Postgres problems.

The SQL question isn't just testing SQL. It's testing how you think about data, handle ambiguity, and communicate with technical peers — and those habits are worth building deliberately.

Start a free mock →

Related reading

• How to Approach and Present a Data Science Take-Home
• Data Scientist Interview Questions: Structure Your Answers
• Behavioral Interview Questions and Answers: The Complete Guide