SQL Interview Questions for Data Scientists and Data Analysts

In what order does SQL run the clauses of a query?

SQL logically runs FROM and the joins first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT and ORDER BY, and it applies LIMIT or FETCH last. This order explains several common errors, because a column alias defined in SELECT does not exist yet when WHERE runs, so you cannot filter on it there. Most databases do let you use the alias in ORDER BY, since that clause runs after SELECT. The WHERE Clauses problem practises filtering on the raw columns.

How do you return the five highest values in a table?

You sort with ORDER BY on the value in descending order and then keep five rows with FETCH FIRST 5 ROWS ONLY, which is the standard form, or LIMIT 5 in PostgreSQL, MySQL and SQLite. Without ORDER BY the database may return any five rows, and without a tie-breaker the rows at the boundary can change between runs. Practise with ORDER BY and LIMIT and OFFSET.

SELECT product_id, revenue
FROM product_sales
ORDER BY revenue DESC, product_id
FETCH FIRST 5 ROWS ONLY;

What is the difference between DISTINCT and GROUP BY?

DISTINCT removes duplicate rows from the result, while GROUP BY collapses rows into groups so that you can compute an aggregate for each one. SELECT DISTINCT city FROM users and SELECT city FROM users GROUP BY city return the same rows, but only GROUP BY lets you add COUNT(*) or SUM(amount) per city. To count unique values, use COUNT(DISTINCT user_id), which is one of the most common building blocks in metric questions. The DISTINCT Values problem practises removing duplicates.

Why does WHERE column = NULL return no rows?

WHERE column = NULL returns no rows because any comparison with NULL evaluates to unknown, and WHERE keeps only rows where the condition is true. SQL uses three-valued logic, so NULL = NULL is also unknown. Use IS NULL and IS NOT NULL to test for missing values, and COALESCE(column, default) to replace them. The NULL Handling problem covers these cases.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row, while COUNT(column) counts only the rows where that column is not NULL. The same rule applies to SUM, AVG, MIN and MAX, which all skip NULL values, so AVG(rating) averages only the rows that have a rating. Interviewers use this to check whether you know that an average over a column with missing values is computed on fewer rows than the table holds. The COUNT, SUM, AVG problem covers these aggregates.

Why can NOT IN return no rows at all?

NOT IN returns no rows when the list or subquery contains a NULL, because x NOT IN (1, 2, NULL) means x is not 1, x is not 2 and x is not NULL, and the last comparison is unknown for every x. A query such as WHERE customer_id NOT IN (SELECT customer_id FROM orders) silently returns nothing if a single order has a NULL customer_id. NOT EXISTS does not have this problem, so it is the safer way to write an anti-join, as the next section shows.

What is the difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN keeps only the rows that have a match in both tables, while a LEFT JOIN keeps every row from the left table and fills the columns of the right table with NULL where there is no match. Use a LEFT JOIN whenever the question asks about all customers, all users or all products, including those with no activity. Practise with INNER JOIN, LEFT JOIN and Multiple Joins.

How do you find customers who have never placed an order?

You find customers with no orders using an anti-join, written either with NOT EXISTS or as a LEFT JOIN followed by WHERE o.order_id IS NULL. Both return the customers that have no matching row in the orders table, and NOT EXISTS stays correct even when the orders table has NULL keys.

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Why does a filter in the WHERE clause turn a LEFT JOIN into an inner join?

A filter on the right table in the WHERE clause removes the rows where that table is NULL, so the customers without a match disappear and the LEFT JOIN behaves like an inner join. Put conditions on the right table in the ON clause instead, which filters the rows being joined while keeping every row from the left table. In the query below, customers with no orders in 2026 appear with a count of zero, because COUNT(o.order_id) skips the NULL rows.

SELECT c.customer_id,
       COUNT(o.order_id) AS orders_2026
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= DATE '2026-01-01'
GROUP BY c.customer_id;

Why does a join duplicate rows and inflate a SUM?

A join duplicates rows when the key is not unique on one side, because each row on the one side is repeated once for every matching row on the many side. If an order has three payments, joining orders to payments repeats the order amount three times, and SUM(o.amount) triples it. Check the grain of each table before joining, and aggregate the many side to one row per key first, as the CTE below does.

WITH paid AS (
  SELECT order_id, SUM(amount) AS paid_amount
  FROM payments
  GROUP BY order_id
)
SELECT o.customer_id,
       SUM(o.amount) AS order_total,
       SUM(COALESCE(p.paid_amount, 0)) AS paid_total
FROM orders o
LEFT JOIN paid p ON p.order_id = o.order_id
GROUP BY o.customer_id;

Comparing COUNT(*) before and after a join is a quick way to catch this, and saying aloud what one row of each table represents helps you spot the problem before you write the join.

How do you find employees who earn more than their managers?

You find employees who earn more than their managers with a self join, which joins the employees table to itself so that each employee row sits next to its manager row. Give the two copies different aliases, join the manager_id of one to the employee_id of the other and compare the salaries. The Self Join problem uses the same pattern.

SELECT e.name AS employee,
       e.salary,
       m.name AS manager,
       m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary;

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped, while HAVING filters groups after the aggregates are computed. A condition on a raw column such as the order date belongs in WHERE, and a condition on an aggregate such as COUNT(*) belongs in HAVING. The query below finds customers with more than three orders in 2026. Practise with GROUP BY and HAVING Clause.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 3;

Why must every selected column appear in GROUP BY?

Every column in SELECT that is not inside an aggregate must appear in GROUP BY, because the query returns one row per group and the database needs to know which single value to show for that column. Selecting customer_name while grouping only by customer_id fails in many databases, unless the database can tell from a primary key that each id has exactly one name. Add the column to GROUP BY, or wrap it in an aggregate such as MAX if you know it is constant within the group.

How do you compute the average of a per-user total?

You compute an average of per-user totals with a nested aggregation, which sums the amount per user in a subquery or CTE and then averages those sums in the outer query. Writing AVG(SUM(amount)) directly is an error in standard SQL, and AVG(amount) on its own gives the average order value, which answers a different question. The Nested Aggregations problem practises this pattern.

SELECT AVG(user_total) AS avg_spend_per_user
FROM (
  SELECT user_id, SUM(amount) AS user_total
  FROM orders
  GROUP BY user_id
) per_user;

How do you count several categories in one query?

You count several categories in one query with conditional aggregation, which puts a CASE expression inside SUM or COUNT so that each row contributes only to the categories it belongs to. This turns rows into columns in a single pass over the table, which is how most report-style questions with one column per status, device or plan are answered. Practise with Conditional Aggregation and CASE Statements.

SELECT order_date,
       COUNT(*) AS order_count,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
       AVG(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0.0 END) AS refund_rate
FROM orders
GROUP BY order_date
ORDER BY order_date;

How do you compute a rate such as a refund rate or conversion rate?

You compute a rate by averaging a CASE expression that is 1 for the rows that count and 0 for the others, or by dividing a conditional count by the total count. Write the 1 as 1.0, or multiply by 1.0 before dividing, because dividing two integers truncates the result to 0 in PostgreSQL and several other databases. PostgreSQL also supports the standard FILTER clause, as in COUNT(*) FILTER (WHERE status = 'refunded'), which reads more cleanly where it is available.

How is a window function different from GROUP BY?

A window function computes a value across a set of related rows while keeping every row in the result, whereas GROUP BY collapses each group into one row. SUM(amount) OVER (PARTITION BY user_id) adds each user's total to every one of that user's orders, so you can compare an order with the user total in the same row. PARTITION BY defines the groups and ORDER BY inside OVER defines the order within each group.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER gives every row a unique number even when values tie, RANK gives tied rows the same number and then skips ahead, and DENSE_RANK gives tied rows the same number without skipping. The table below shows the three functions on salaries of 100, 100 and 90 ordered from highest to lowest. Use ROW_NUMBER when you need exactly N rows, and RANK or DENSE_RANK when tied rows should be kept together. Practise with ROW_NUMBER and RANK and DENSE_RANK.

How do you find the second highest salary?

You find the second highest salary by taking the maximum of the salaries below the overall maximum, or by ranking salaries with DENSE_RANK and keeping rank 2. The version below returns NULL when there is no second value, which is usually the behaviour the question expects. DENSE_RANK generalises to the Nth highest salary, and unlike ROW_NUMBER it still returns the right value when the top salary is shared by several people.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Why can you not filter on a window function in WHERE?

You cannot filter on a window function in WHERE because window functions are computed after WHERE, GROUP BY and HAVING, at the same stage as SELECT. Compute the window function in a subquery or CTE and filter on it in the outer query. Some databases such as Snowflake and BigQuery add a QUALIFY clause for this, but the subquery form works everywhere.

How do you calculate month-over-month revenue growth?

You calculate month-over-month growth by aggregating revenue to one row per month, using LAG to bring the previous month's revenue onto the same row and dividing the difference by that previous value. NULLIF(prev_revenue, 0) avoids a division by zero, and the first month has no previous value, so its growth is NULL. Practise with LAG and LEAD.

WITH monthly AS (
  SELECT EXTRACT(YEAR FROM order_date) AS order_year,
         EXTRACT(MONTH FROM order_date) AS order_month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
),
with_prev AS (
  SELECT order_year, order_month, revenue,
         LAG(revenue) OVER (ORDER BY order_year, order_month) AS prev_revenue
  FROM monthly
)
SELECT order_year, order_month, revenue, prev_revenue,
       100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0) AS pct_change
FROM with_prev
ORDER BY order_year, order_month;

LAG compares a row with the previous row, which is the previous month only if no month is missing. If some months have no orders, build a complete list of months first and join the revenue onto it, or join each month to the same month of the previous year for a year-over-year comparison.

What is the difference between LAG and LEAD?

LAG returns a value from an earlier row in the window and LEAD returns a value from a later row, both counted in the order set by ORDER BY inside OVER. Each takes an optional offset and a default, so LAG(revenue, 12, 0) looks twelve rows back and returns 0 when there is no such row. With PARTITION BY user_id ORDER BY order_date, LAG(order_date) gives each user's previous order date, which is the starting point for questions about the time between purchases.

How do you calculate a running total and a moving average?

You calculate a running total with SUM over a window ordered by date with a frame that runs from the first row to the current row, and a moving average with AVG over a frame of the last N rows. The query below computes cumulative revenue and a seven-day average from a table with one row per day. Add PARTITION BY user_id or PARTITION BY region to restart the total for each group. Practise with Running Totals.

SELECT sale_date,
       daily_revenue,
       SUM(daily_revenue) OVER (
         ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue,
       AVG(daily_revenue) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS revenue_7_day_avg
FROM daily_sales
ORDER BY sale_date;

Why does a running total jump when dates repeat?

A running total jumps when dates repeat because a window with ORDER BY and no frame clause uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default, which includes every row that shares the current date. All the rows on the same date then show the same total for the whole day. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add one row at a time, and add a unique column to ORDER BY so that the order of tied rows is fixed.

When should you use a CTE instead of a subquery?

You should use a CTE when a query has several steps or reuses an intermediate result, because each step gets a name and the query reads from top to bottom. A short subquery used once is fine inline. In most modern databases a CTE and the equivalent subquery perform about the same, so the choice is mainly about readability, which matters in an interview because the interviewer has to follow your logic. Practise with Common Table Expressions and Subqueries.

What is a correlated subquery?

A correlated subquery is a subquery that refers to a column of the outer query, so it is evaluated once for each outer row. The query below finds employees who earn more than the average of their own department by computing the department average for each employee. The same result can come from AVG(salary) OVER (PARTITION BY department) in a CTE, which often runs faster on large tables.

SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

What is the difference between EXISTS and IN?

EXISTS checks whether a subquery returns at least one row, while IN checks whether a value appears in a list or in the results of a subquery. The positive forms usually give the same result, and the difference shows up in the negative forms, where NOT IN returns nothing if the subquery contains a NULL and NOT EXISTS behaves as expected.

How do you select all rows from one month?

You select one month of rows with a half-open range, which keeps rows on or after the first day of the month and before the first day of the next month. This works for date and timestamp columns alike and lets the database use an index on the column. Practise with Date Functions.

SELECT COUNT(*) AS january_orders
FROM orders
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-02-01 00:00:00';

Why can BETWEEN miss rows on a timestamp column?

BETWEEN can miss rows on a timestamp column because it includes both ends, and an upper bound such as '2026-01-31' is read as midnight at the start of January 31. Every order placed later that day falls outside the range. The half-open range in the previous answer avoids the problem, and it also avoids counting a row twice when consecutive ranges share an endpoint.

How do you group rows by month or by week?

You group rows by month with a function that truncates the date or extracts its parts, and the function depends on the database. Standard SQL has EXTRACT(YEAR FROM order_date) and EXTRACT(MONTH FROM order_date), which the month-over-month query above groups by, and PostgreSQL and Snowflake also offer DATE_TRUNC('month', order_date), which returns the first day of the month as one value. Ask which database the interview uses, and say which function you would use there if the platform does not run your query.

How do you find duplicate rows?

You find duplicates by grouping on the columns that should be unique and keeping the groups with HAVING COUNT(*) greater than 1. The query below lists every email address that appears more than once in a users table, together with the number of copies.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

How do you keep only the latest row for each user?

You keep the latest row per user by numbering each user's rows with ROW_NUMBER ordered by the update time, newest first, and keeping the rows numbered 1. ROW_NUMBER is the right function here because it returns exactly one row per user even when two rows share a timestamp, and adding a second column to ORDER BY makes the choice between them repeatable. SELECT DISTINCT only helps when the duplicate rows are identical in every column.

WITH ranked AS (
  SELECT user_id, email, plan, updated_at,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM user_profiles
)
SELECT user_id, email, plan, updated_at
FROM ranked
WHERE rn = 1;

How do you calculate day 7 retention?

You calculate day 7 retention by taking each signup cohort, counting the users who were active exactly seven days after signing up and dividing by the number who signed up. The LEFT JOIN keeps cohorts where nobody returned, and COUNT(DISTINCT e.user_id) stops a user with several events on day 7 from being counted more than once. Some teams define retention as activity on or after day 7, so confirm the definition before you write the query. The free Data Science Mock Interview includes a cohort retention matrix problem.

SELECT s.signup_date,
       COUNT(DISTINCT s.user_id) AS signups,
       COUNT(DISTINCT e.user_id) AS retained_day_7,
       1.0 * COUNT(DISTINCT e.user_id) / COUNT(DISTINCT s.user_id) AS day_7_retention
FROM signups s
LEFT JOIN events e
  ON e.user_id = s.user_id
 AND e.event_date = s.signup_date + INTERVAL '7' DAY
GROUP BY s.signup_date
ORDER BY s.signup_date;

How do you measure a conversion funnel?

You measure a funnel by counting the distinct users who reached each step in a period and dividing each count by the count of the step before it. The query below counts viewers, users who added to cart and buyers in October 2026 with conditional aggregation. It does not check that the steps happened in order, so for a strict funnel compute each user's first time at each step with MIN(CASE WHEN ... THEN event_time END) and require each step to come after the previous one.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS viewed,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS added_to_cart,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchased
FROM events
WHERE event_date >= DATE '2026-10-01'
  AND event_date <  DATE '2026-11-01';

How do you return the top N rows for each group?

You return the top N rows for each group by ranking the rows within each group using a window function with PARTITION BY and keeping the rows whose rank is at most N. The query below returns the two products with the highest revenue in each category. Use RANK or DENSE_RANK instead of ROW_NUMBER if tied products should all be returned, and say which one you chose and why. The StrataScratch alternatives guide shows the same pattern for top earners per department in both SQL and pandas.

WITH product_revenue AS (
  SELECT p.category, p.product_id, SUM(o.amount) AS revenue
  FROM orders o
  JOIN products p ON p.product_id = o.product_id
  GROUP BY p.category, p.product_id
),
ranked AS (
  SELECT category, product_id, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY revenue DESC, product_id
         ) AS rn
  FROM product_revenue
)
SELECT category, product_id, revenue
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

How do you calculate daily active users and each segment's share of a total?

You calculate daily active users with COUNT(DISTINCT user_id) grouped by the event date, because a user with many events on one day should count once. For a share of the total, divide each group's value by SUM(value) OVER (), which is the total across all rows of the result, so revenue / SUM(revenue) OVER () gives each region's share of revenue without a second query.

How much SQL do you need for a data scientist interview?

You need to be comfortable with joins, GROUP BY and HAVING, conditional aggregation, window functions, CTEs and date filtering, and you need to apply them to business tables without help. SQL rounds are usually timed, so practise writing these queries quickly as well as correctly.

Are SQL interview questions different for data analysts and data scientists?

SQL interview questions for data analysts and data scientists cover the same topics, and the difference is mostly in weight. Analyst interviews often spend the whole technical round on SQL and add reporting questions such as period-over-period growth and dashboard metrics. Data scientist interviews usually keep SQL to one round and follow it with statistics, A/B testing and sometimes machine learning.

Which SQL dialect should you practise for an interview?

Practise standard SQL as written in PostgreSQL, because most interview platforms accept it and the core syntax for joins, aggregation, window functions and CTEs is the same across databases. The main differences are in date functions and in how you limit rows, so learn how your target company writes those, and ask the recruiter which database the round uses if you are unsure.

Which window functions should you know for a SQL interview?

You should know ROW_NUMBER, RANK and DENSE_RANK for ranking, LAG and LEAD for comparing rows over time, and SUM, AVG and COUNT with an OVER clause for running totals, moving averages and shares of a total. You should also be able to explain PARTITION BY, ORDER BY inside the window and the ROWS frame clause.

Is there a timed SQL practice test for data scientist interviews?

Yes, TensorTonic has a 30-minute SQL Sprint on Window Functions, which includes top earners per department and year-over-year revenue. The free Data Science Mock Interview is a 75-minute sitting with five problems that mix SQL, pandas, NumPy, math and ML, and it ends with a scored report.