SQL and Data Science Interview Questions: A Practical Guide to Practice and Preparation

SQL is the one skill that shows up in almost every data analyst, data scientist, and analytics engineer interview. Whether it’s a live coding session, a take-home assignment, or a whiteboard question, you’ll be asked to turn a business question into a query — and to explain how you got there.

The best way to prepare isn’t memorizing syntax. It’s practicing on realistic data until you can look at a question, picture the tables involved, and write the query step by step. That’s what this guide is for.

You’ll work through six interview-style SQL questions — three easy, two medium, and one hard — all based on the same dataset: a small online store. For each one, you’ll see the question, the answer, the result, and an explanation of the thinking behind it, including the traps interviewers like to set. You can download the data and run every query yourself.

The Dataset: A Small Online Store

The dataset has four related tables, the same kind of structure you’ll see in many interview questions:

• customers — who the customers are and when they signed up.
• products — what the store sells, by category and price.
• orders — each order a customer placed, with a status.
• order_items — the products and quantities inside each order.

They’re connected by ID columns:

customers                orders                    order_items               products
---------                ------                    -----------               --------
customer_id  ◄───────┐   order_id      ◄───────┐   order_item_id        ┌──► product_id
name                 └── customer_id           └── order_id             │    product_name
city                     order_date                product_id  ─────────┘    category
signup_date              status                    quantity                  price

Each customer can have many orders, and each order can contain many products. (For more on designing tables like these, see Database Design: A Beginner’s Guide to Planning It Right.)

Download the Data

Download the dataset from GitHub

The folder includes:

• setup.sql — creates the four tables and inserts all the data. It works in SQLite, PostgreSQL, and MySQL 8+.
• store.db — a ready-to-use SQLite database, the fastest way to start.
• customers.csv, products.csv, orders.csv, and order_items.csv — the raw data, if you’d rather load it into a spreadsheet, Pandas, or another tool.

The quickest way to start is with SQLite, which is free and needs no server:

sqlite3 store.db

Or build the database yourself from the setup script:

sqlite3 store.db < setup.sql        # SQLite
psql -d mydb -f setup.sql           # PostgreSQL
mysql mydb < setup.sql              # MySQL 8+

To practice with MySQL without installing it, see MySQL with Docker.

The Tables

The store has 25 customers, 16 products in four categories, 60 orders, and 99 order items. Here are all the products and the first rows of the other tables, so you can see their structure; the full data is in the download.

customers

First 5 of 25 rows.

products

orders

First 5 of 60 rows.

order_items

First 5 of 99 rows.

About the results below: they come from running each query in SQLite. SQLite shows whole-dollar amounts like 220 without decimals and drops trailing zeros (1682.5); PostgreSQL and MySQL show the same values as 220.00 and 1682.50.

How to Approach Any SQL Interview Question

Before the questions, here’s a simple process that works for all of them — and that interviewers love to see you follow out loud:

1. Restate the question in your own words, and ask about anything unclear. Should cancelled orders count? What should happen with ties?
2. Identify the tables you need and how they connect.
3. Work out the “grain” of the answer: one row per what? Per customer? Per category?
4. Build the query in steps: start with the joins, check the rows, then add filters, grouping, and sorting.
5. Check edge cases: missing values (NULL), duplicates from joins, ties, and empty results.
6. Sanity-check the result against the data. If a number looks too high, it probably is.

Easy Questions

Easy 1: Filter and Sort

Question: List the customers who live in El Centro, from the earliest to the most recent signup.

SELECT name, signup_date
FROM customers
WHERE city = 'El Centro'
ORDER BY signup_date;

Explanation: WHERE keeps only the rows that match a condition, and ORDER BY sorts the result — ascending (oldest first) by default; add DESC for newest first. Text values go in single quotes.

What the interviewer is checking: that you know the basic shape of a query and the order its clauses are written in: SELECT → FROM → WHERE → ORDER BY. A good habit to mention: select only the columns you need instead of SELECT *.

Easy 2: Count by Group

Question: How many orders are there in each status?

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC, status;

Explanation: GROUP BY status puts the orders into one group per status, and COUNT(*) counts the rows in each group. AS order_count gives the calculated column a readable name, which you can then use in ORDER BY.

What the interviewer is checking: that you understand aggregation. A natural follow-up in a data science interview is “What’s the cancellation rate?” — 6 cancelled out of 60 orders, or 10%. You can calculate it directly:

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_rate_pct
FROM orders;

Notice the 100.0: in many databases, dividing two integers gives an integer, so 6 / 60 would be 0. Multiplying by a decimal first avoids that trap.

Easy 3: Top N

Question: What are the three most expensive products?

SELECT product_name, category, price
FROM products
ORDER BY price DESC
LIMIT 3;

Explanation: sort from highest to lowest price with ORDER BY price DESC, then keep the first three rows with LIMIT 3.

What the interviewer is checking: “top N” questions are extremely common. Two things worth mentioning: LIMIT is used by SQLite, PostgreSQL, and MySQL, while SQL Server uses SELECT TOP 3 instead. And what about ties? If another product also cost $150, LIMIT 3 would pick one of the two arbitrarily. Asking the interviewer how ties should be handled shows exactly the kind of thinking they’re looking for — and it’s the key to the hard question below.

Medium Questions

Medium 1: Join, Aggregate, and Filter Groups

Question: Which customers have spent more than $800 on completed orders? Show how many completed orders each one placed and their total spending, highest first.

SELECT
    c.name,
    COUNT(DISTINCT o.order_id) AS completed_orders,
    SUM(oi.quantity * p.price) AS total_spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products p     ON p.product_id  = oi.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name
HAVING SUM(oi.quantity * p.price) > 800
ORDER BY total_spent DESC;

Explanation, step by step:

1. Joins: spending lives across three tables — the customer is in customers, the order status in orders, the quantity in order_items, and the price in products. The joins connect them through their ID columns. (See SQL JOINs Explained for a refresher.)
2. WHERE keeps only completed orders, before grouping.
3. GROUP BY produces one row per customer. Grouping by customer_id as well as name is a good habit: two different customers could share a name.
4. SUM(oi.quantity * p.price) calculates spending line by line, then adds it up.
5. HAVING filters after grouping, on the aggregated total. You can’t use WHERE here, because the total doesn’t exist until the rows are grouped.

You can check a total by hand. Grace Wilson has two completed orders: order 122 is 2 × $30 + 4 × $85 + $28 = $428, and order 129 is 4 × $150 + $35 + 2 × $60 = $755. That’s $1,183.

The trap — counting after a join: notice the COUNT(DISTINCT o.order_id). Joining to order_items creates one row per item, not per order, so a plain COUNT counts items instead. Here’s what happens without DISTINCT (top three shown):

SELECT c.name, COUNT(o.order_id) AS completed_orders
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name
ORDER BY completed_orders DESC
LIMIT 3;

Maria placed 8 completed orders, but this query says 14, because those orders contain 14 items in total. This “join fan-out” is one of the most common mistakes in real analysis — and one of the most common interview traps. Whenever you count after a join, ask yourself: what does one row represent now?

What the interviewer is checking: multi-table joins, WHERE vs. HAVING, and whether you notice that joins can multiply rows.

Medium 2: Find What’s Missing

Question: Which customers have never placed an order?

SELECT c.customer_id, c.name, c.city, c.signup_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Six customers have never ordered, and four of them signed up in the last two months of the data. In a data science interview, that’s worth a follow-up comment: are new customers being converted into buyers?

Explanation: a LEFT JOIN keeps every customer, even those with no matching orders. For those customers, the order columns are filled with NULL. WHERE o.order_id IS NULL then keeps only the customers with no match. This pattern is called an anti-join.

Two details matter here:

• Use IS NULL, never = NULL. Nothing equals NULL — not even another NULL — so = NULL always returns no rows.
• Check a column that can’t be null when there is a match, like the table’s primary key (o.order_id).

The same pattern answers “which products have never been sold?”:

SELECT p.product_name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL;

Alternative with NOT EXISTS: many interviewers like to see a second approach. NOT EXISTS reads almost like the question itself — “customers for whom no order exists”:

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

(For more on subqueries like this one, see Mastering SQL Subqueries.)

What the interviewer is checking: that you know the difference between INNER JOIN and LEFT JOIN, how NULL behaves, and that there’s often more than one correct answer. Be careful with the similar-looking NOT IN (SELECT customer_id FROM orders): if the subquery ever returns a NULL, NOT IN returns no rows at all — a classic bug that NOT EXISTS avoids.

Hard Question

Hard: Top Product in Each Category, Including Ties

Question: For each product category, find the product that brought in the most revenue from completed orders. If two products tie for first place, show both.

This is a “top N per group” question — one of the most common hard SQL interview questions. The trick is that you need to rank products within each category, which is exactly what window functions are for.

Step 1: Calculate revenue per product. Always build hard queries in steps, checking each one:

SELECT
    p.category,
    p.product_name,
    SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN orders o   ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY p.category, p.product_name
ORDER BY p.category, revenue DESC;

Now you can see the answer — and the catch: in Stationery, Planner 2026 and Notebook Set are tied at $180.

Step 2: Rank within each category and keep the top.

WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(oi.quantity * p.price) AS revenue
    FROM order_items oi
    JOIN orders o   ON o.order_id   = oi.order_id
    JOIN products p ON p.product_id = oi.product_id
    WHERE o.status = 'completed'
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT
        category,
        product_name,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
    FROM product_revenue
)
SELECT category, product_name, revenue
FROM ranked
WHERE revenue_rank = 1
ORDER BY category, product_name;

Explanation:

• WITH ... AS creates common table expressions (CTEs): named, temporary results that make a complex query readable, one step at a time. product_revenue is Step 1; ranked adds the ranking.
• RANK() OVER (PARTITION BY category ORDER BY revenue DESC) numbers the products within each category from highest to lowest revenue. PARTITION BY restarts the ranking for each category — it’s like GROUP BY, but it keeps every row instead of collapsing them.
• Why the filter is in the outer query: you can’t use a window function in a WHERE clause, because WHERE runs before window functions are calculated. That’s why the ranking happens in one step, and the filter (WHERE revenue_rank = 1) in the next.

The trap — choosing the wrong ranking function: many candidates reach for ROW_NUMBER(). Here’s what happens if you do:

WITH product_revenue AS (
    SELECT p.category, p.product_name, SUM(oi.quantity * p.price) AS revenue
    FROM order_items oi
    JOIN orders o   ON o.order_id   = oi.order_id
    JOIN products p ON p.product_id = oi.product_id
    WHERE o.status = 'completed'
    GROUP BY p.category, p.product_name
)
SELECT category, product_name, revenue
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM product_revenue
) t
WHERE rn = 1
ORDER BY category;

ROW_NUMBER() always gives unique numbers, so one of the tied Stationery products silently disappears — and which one you get isn’t guaranteed. The three ranking functions differ only in how they handle ties:

Use RANK() or DENSE_RANK() when ties should be included, and ROW_NUMBER() when you need exactly one row per group (and add a tie-breaker to the ORDER BY so the result is predictable). For more practice with these functions, see SQL Window Functions: Running Totals & Rankings.

What the interviewer is checking: CTEs, window functions, the order in which SQL runs its clauses, and — most of all — whether you asked about ties before writing the query.

Common SQL Interview Traps

• Counting after a join. Joins can multiply rows. Use COUNT(DISTINCT ...) or aggregate before joining.
• Using WHERE instead of HAVING. WHERE filters rows before grouping; HAVING filters groups after.
• Comparing to NULL with =. Use IS NULL and IS NOT NULL.
• NOT IN with NULL values. It returns no rows if the subquery contains a NULL. Prefer NOT EXISTS.
• Integer division. 6 / 60 can be 0. Multiply by 100.0 or cast to a decimal first.
• Ignoring ties. Ask how ties should be handled, and choose RANK(), DENSE_RANK(), or ROW_NUMBER() on purpose.
• Filtering a LEFT JOIN in the wrong place. A condition on the right-hand table in the WHERE clause (other than IS NULL) silently turns a LEFT JOIN into an INNER JOIN. Put it in the ON clause instead.
• Forgetting the business rules. Should cancelled or pending orders count? Say your assumption out loud.

Tips for SQL and Data Science Interviews

• Talk through your thinking. Interviewers care as much about your process as your final query. Explain which tables you need and why.
• Ask clarifying questions first. Ties, NULLs, date ranges, and which statuses count are all fair game — and asking about them is a strength, not a weakness.
• Start simple, then build. Get a working query for the core of the question, check it, then add the details.
• Check your answer against the data. Pick one row and calculate it by hand, like the $1,183 for Grace above.
• Connect results to the business. In a data science interview, follow a number with what it means: “A 10% cancellation rate is worth investigating — are cancellations concentrated in one product or city?”
• Know the follow-ups. Be ready to talk about performance (indexes and avoiding unnecessary work — see Why Your SQL Query Is Slow) and data quality (see Cleaning Messy Data with Pure SQL).

Practice on Your Own

Here are more questions you can answer with the same dataset. Try them before looking anything up:

1. Easy: What’s the average price of the products in each category?
2. Easy: How many customers signed up in each city?
3. Medium: What’s the total revenue from completed orders in each month?
4. Medium: Which customers have placed both a completed and a cancelled order?
5. Hard: For each customer, show each completed order with a running total of their spending over time. (Hint: SUM(...) OVER (PARTITION BY ... ORDER BY ...).)
6. Hard: Which customers’ second completed order was bigger than their first? (Hint: ROW_NUMBER() or LAG().)

If you also work in Python, try answering the same questions with Pandas — loading the CSV files and using groupby() and merge() — and compare the two approaches. See Data Cleaning with Python Pandas.

Conclusion

SQL interview questions follow a handful of patterns: filtering and sorting, grouping and counting, top N, joining and aggregating, finding what’s missing, and ranking within groups. The six questions in this guide cover all of them, on one dataset you can download and explore.

The difference between a good answer and a great one usually isn’t the syntax — it’s noticing that a join multiplied your rows, that NULL needs IS NULL, or that two products tied for first place. Practice until those checks become habits, explain your thinking as you go, and you’ll walk into your next interview ready for whatever question comes up.