SQL Interview Questions for Data Analysts: Answers and Tested SQL, from Basics to Window Functions

What sample schema do these SQL questions use?

Every query in this article runs on two small tables: customers (6 rows) and orders (10 rows). We ran all of them in SQLite and copied the outputs. The SQL is standard and runs in PostgreSQL too, except where we point out a dialect difference.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id),
  order_date TEXT, amount INTEGER, status TEXT);

INSERT INTO customers VALUES
 (1,'Aarav','Mumbai','2026-01-05'), (2,'Diya','Delhi','2026-01-20'),
 (3,'Kabir','Bengaluru','2026-02-11'), (4,'Meera','Mumbai','2026-02-25'),
 (5,'Rohan','Pune','2026-03-02'), (6,'Sana','Delhi','2026-03-15');

INSERT INTO orders VALUES
 (101,1,'2026-01-10',1200,'delivered'), (102,1,'2026-02-14',800,'delivered'),
 (103,2,'2026-01-25',2500,'delivered'), (104,3,'2026-02-12',450,'cancelled'),
 (105,3,'2026-03-01',3000,'delivered'), (106,4,'2026-03-05',1500,'delivered'),
 (107,1,'2026-03-20',2200,'delivered'), (108,2,'2026-03-22',700,'returned'),
 (109,5,'2026-03-25',1800,'delivered'), (110,4,'2026-03-28',1500,'delivered');

Useful facts to keep in mind: Sana has no orders, Meera has two orders of the same amount (₹1,500), there is one cancelled and one returned order, and dates are stored as 'YYYY-MM-DD' text. In PostgreSQL you would normally use a DATE column.

Topic	What interviewers check	Questions below
Basics	Filtering, NULLs, execution order, DISTINCT, UNION	1–7
Joins	Choosing the right join, row counts, anti-joins	8–10
GROUP BY and HAVING	Aggregation at the right grain, conditional counts	11–13
Subqueries and CTEs	Breaking a problem into steps	14–16
Window functions	Ranking, running totals, previous-row comparisons	17–20
Scenarios	Turning a business question into correct SQL	S1–S10

Basic SQL interview questions

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation, so it can use SUM, COUNT and so on. "Delivered orders only" belongs in WHERE; "customers with total spend above ₹3,000" belongs in HAVING.

2. In what order does SQL process a query?

Logically: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT (including window functions) → DISTINCT → ORDER BY → LIMIT. This is why a column alias defined in SELECT usually cannot be used in WHERE, and why you cannot filter on a window function without a subquery or CTE.

3. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. On our data, SELECT COUNT(*), COUNT(DISTINCT status) FROM orders; returns 10 and 3.

The difference matters after a LEFT JOIN:

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

This gives Sana 0 orders. With COUNT(*) she would wrongly get 1, because the LEFT JOIN still produces one row for her (with NULL order columns).

4. How do you handle NULL values?

NULL means "unknown", so col = NULL is never true. Use IS NULL / IS NOT NULL. Replace NULLs for display with COALESCE(col, 0). Aggregates such as SUM and AVG ignore NULLs, which can change an average if you expected missing values to count as zero.

5. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows; UNION ALL keeps them and is faster. Selecting the cities of customers 1 and 4, then of customers 1 and 2, gives Mumbai, Mumbai, Mumbai, Delhi with UNION ALL but only Mumbai and Delhi with UNION. Use UNION ALL unless you actually need de-duplication.

6. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes selected rows (it can take a WHERE clause). TRUNCATE removes all rows quickly but keeps the table. DROP removes the table itself, structure included. Exact rollback behaviour depends on the database, so say which one you have used.

7. What are primary keys and foreign keys?

A primary key uniquely identifies each row (customers.customer_id). A foreign key in another table points to it (orders.customer_id), so every order should belong to a real customer. For analysts, the practical point is knowing each table's grain: one row per customer, or one row per order.

SQL join interview questions

8. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only matching rows; LEFT JOIN keeps all rows from the left table and fills unmatched right-side columns with NULL. Joining customers to orders, the INNER JOIN returns 10 rows and Sana disappears; the LEFT JOIN returns 11 rows, with Sana's order columns NULL. Our SQL joins explained guide shows every join type with full result tables.

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

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

Output: 6, Sana. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) gives the same result. Avoid NOT IN on a column that can contain NULLs: if the subquery returns any NULL, NOT IN returns no rows.

10. Why might a join return more rows than you expected?

A join returns one row per matching pair. If the key is not unique on either side, rows multiply, and sums get inflated. Always check the grain of both tables, compare row counts before and after the join, and aggregate to one row per key before joining two one-to-many tables.

GROUP BY and HAVING interview questions

11. Show delivered revenue and order count by city.

SELECT c.city, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
ORDER BY revenue DESC;

city	orders	revenue
Mumbai	5	7200
Bengaluru	1	3000
Delhi	1	2500
Pune	1	1800

12. Which customers have spent more than ₹3,000 in total (all statuses)?

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 3000;

Output: customer 1 (4200), customer 2 (3200) and customer 3 (3450). A good follow-up is to ask whether cancelled and returned orders should count. If not, add WHERE status = 'delivered', and customer 1 is the only one left.

13. How do you count orders in different bands in one query?

Use CASE WHEN inside the query:

SELECT CASE WHEN amount >= 2000 THEN 'High'
            WHEN amount >= 1000 THEN 'Medium'
            ELSE 'Low' END AS band,
       COUNT(*) AS orders
FROM orders
GROUP BY band
ORDER BY orders DESC;

Output: Medium 4, High 3, Low 3. Grouping by the alias band works in PostgreSQL, MySQL and SQLite. In SQL Server, repeat the CASE expression in GROUP BY.

Subquery and CTE interview questions

14. Find orders larger than the average order amount.

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY amount DESC;

The average is 1565, so the output is orders 105 (3000), 103 (2500), 107 (2200) and 109 (1800).

15. What is a correlated subquery?

A subquery that refers to the outer row and runs once per outer row. This one finds orders above that customer's own average:

SELECT o.order_id, o.customer_id, o.amount
FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount)
                  FROM orders o2
                  WHERE o2.customer_id = o.customer_id);

Output: 103, 105 and 107. Correlated subqueries are easy to read but can be slow on big tables; a CTE or window function often does the same job faster.

16. What is a CTE and why use one?

A CTE (WITH name AS (...)) is a named, temporary result you can refer to in the rest of the query. It makes multi-step logic readable and lets you reuse a step. Example: customers whose delivered revenue is above the average customer's delivered revenue.

WITH cust_rev AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT c.name, cr.revenue
FROM cust_rev cr
JOIN customers c ON c.customer_id = cr.customer_id
WHERE cr.revenue > (SELECT AVG(revenue) FROM cust_rev)
ORDER BY cr.revenue DESC;

The average customer revenue is 2900, so the output is Aarav 4200, Kabir 3000 and Meera 3000.

Window function interview questions

17. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

SELECT order_id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM orders;

order_id	amount	row_num	rnk	dense_rnk
105	3000	1	1	1
103	2500	2	2	2
107	2200	3	3	3
109	1800	4	4	4
106	1500	5	5	5
110	1500	6	5	5

(First six rows shown.) At the tie, ROW_NUMBER still gives unique numbers, RANK gives both 5 and then skips to 7, and DENSE_RANK gives both 5 and then continues with 6. Which of the tied rows gets 5 or 6 in ROW_NUMBER is not guaranteed unless you add a tie-breaker such as order_id.

18. What does PARTITION BY do?

It restarts the window calculation for each group without collapsing rows, unlike GROUP BY. SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) gives each customer's own running total. For Aarav: 1200, 2000, 4200.

19. Write a running total of delivered revenue by date.

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status = 'delivered'
ORDER BY order_date;

The running total rises 1200, 3700, 4500, 7500, 9000, 11200, 13000, 14500. If two rows share a date, the default window frame includes both on that date. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if you need strict row-by-row totals.

20. How do LAG and LEAD work?

LAG(col) returns the value from the previous row in the window and LEAD(col) from the next one. LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) gives each order's previous order date for the same customer. For Diya, order 108 (22 March) shows 25 January, and her first order shows NULL. This is the basis of "days between orders" and retention questions.

10 SQL scenario questions on the same schema

These are closer to what a take-home or live round looks like. Try each one before reading the answer.

S1. Who is the top customer by delivered revenue?

SELECT c.name, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 1;

Output: Aarav, 4200. Mention that LIMIT 1 hides ties; use RANK() if ties matter. In practice, group by customer_id as well, because names are not unique.

S2. Find the second-highest order amount.

SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);

Output: 2500. The window version generalises to "Nth highest": SELECT DISTINCT amount FROM (SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS dr FROM orders) t WHERE dr = 2;

S3. Show each customer's first order.

WITH ranked AS (
  SELECT customer_id, order_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

customer_id	order_id	order_date	amount
1	101	2026-01-10	1200
2	103	2026-01-25	2500
3	104	2026-02-12	450
4	106	2026-03-05	1500
5	109	2026-03-25	1800

S4. Calculate monthly delivered revenue and month-on-month growth.

WITH monthly AS (
  SELECT SUBSTR(order_date, 1, 7) AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY SUBSTR(order_date, 1, 7)
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

month	revenue	prev_revenue	growth_pct
2026-01	3700	NULL	NULL
2026-02	800	3700	-78.4
2026-03	10000	800	1150.0

In PostgreSQL with a DATE column, use DATE_TRUNC('month', order_date) instead of SUBSTR. Multiplying by 100.0 avoids integer division.

S5. Which city has the highest cancellation or return rate?

SELECT c.city,
       COUNT(*) AS orders,
       SUM(CASE WHEN o.status IN ('cancelled','returned') THEN 1 ELSE 0 END) AS bad_orders,
       ROUND(100.0 * SUM(CASE WHEN o.status IN ('cancelled','returned') THEN 1 ELSE 0 END)
             / COUNT(*), 1) AS bad_pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY bad_pct DESC, c.city;

Output: Bengaluru 50.0 and Delhi 50.0 (1 of 2 orders each), then Mumbai 0.0 and Pune 0.0. A strong answer also points out that 2 orders is far too small a sample to draw conclusions from.

S6. Who is the top customer in each city?

WITH cust AS (
  SELECT c.city, c.name, SUM(o.amount) AS revenue
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'delivered'
  GROUP BY c.city, c.name
), ranked AS (
  SELECT city, name, revenue,
         RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS rnk
  FROM cust
)
SELECT city, name, revenue FROM ranked WHERE rnk = 1 ORDER BY city;

Output: Bengaluru Kabir 3000, Delhi Diya 2500, Mumbai Aarav 4200, Pune Rohan 1800. "Top N per group" is one of the most common analyst SQL questions.

S7. What share of delivered revenue does each city contribute?

SELECT c.city, SUM(o.amount) AS revenue,
       ROUND(100.0 * SUM(o.amount) / SUM(SUM(o.amount)) OVER (), 1) AS pct_of_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
ORDER BY revenue DESC;

Output: Mumbai 49.7, Bengaluru 20.7, Delhi 17.2, Pune 12.4. SUM(SUM(amount)) OVER () is a window over the grouped result, giving the grand total (14500) on every row.

S8. Which customers bought in more than one month?

SELECT customer_id, COUNT(DISTINCT SUBSTR(order_date, 1, 7)) AS active_months
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(DISTINCT SUBSTR(order_date, 1, 7)) > 1;

Output: customer 1 with 3 active months. This is the simplest form of a repeat-purchase or retention question.

S9. Find possible duplicate orders (same customer, same amount).

SELECT customer_id, amount, COUNT(*) AS times
FROM orders
GROUP BY customer_id, amount
HAVING COUNT(*) > 1;

Output: customer 4, amount 1500, twice. Then say what you would check next: the dates (5 and 28 March) suggest two genuine purchases rather than a duplicate load.

S10. How many days did each customer take to place a first order after signing up?

SELECT c.name, c.signup_date, MIN(o.order_date) AS first_order,
       CAST(julianday(MIN(o.order_date)) - julianday(c.signup_date) AS INTEGER)
         AS days_to_first_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.signup_date
ORDER BY c.customer_id;

Output: Aarav 5, Diya 5, Kabir 1, Meera 8, Rohan 23. julianday is SQLite; in PostgreSQL with DATE columns, MIN(o.order_date) - c.signup_date returns the days directly. Sana has no orders, so the INNER JOIN leaves her out. Say so, and offer a LEFT JOIN version if the business wants her counted.

How should you prepare for a SQL interview round?

• Talk through the grain first. Before writing, say what one row of the output represents.
• State assumptions. Should cancelled orders count? How should ties be handled? Interviewers often care more about this than about syntax.
• Check your result. Do a quick sanity check on row counts or totals, as in S7 where the shares add up to 100.
• Practise timed. Online tests are usually timed, so solve problems on a clock on platforms such as HackerRank, LeetCode or DataLemur.
• Know your dialect. Date functions differ most between PostgreSQL, MySQL, SQL Server and BigQuery.

SQL is usually one round of several. Our broader data analytics interview questions guide covers the reasoning and case rounds, and the Excel interview questions and Power BI interview questions guides cover the other tools analysts are usually tested on. If you need a study plan first, see how to learn SQL in 30 days.

Frequently Asked Questions

What SQL questions are asked in a data analyst interview?

Most rounds cover filtering and NULLs, joins, GROUP BY with HAVING, subqueries and CTEs, and window functions such as ROW_NUMBER, RANK and LAG. They usually end with business scenarios such as top customer per city, month-on-month growth, or customers who never ordered.

Are window functions asked in fresher data analyst interviews?

Often, yes. ROW_NUMBER, RANK, DENSE_RANK, running totals with SUM OVER, and LAG for previous-period comparisons come up frequently, even for entry-level roles. Learn them after you are comfortable with joins and GROUP BY.

Which SQL dialect should I use in an interview?

Use the one the company mentions, or the one you know best if they do not specify. Core syntax is the same across PostgreSQL, MySQL and SQL Server. Date functions differ most, so say which dialect you are writing.

How do I find the second highest salary or amount in SQL?

Select the MAX of values below the overall MAX, or use DENSE_RANK in a subquery and keep rank 2. The DENSE_RANK version works for any Nth value and handles ties.

How many SQL questions should I practise before an interview?

There is no fixed number. A practical target is to solve problems across all six areas in this guide until you can write top-N-per-group, running-total and month-on-month queries from memory, under time pressure.

Sources and methodology

All queries were run in SQLite 3.54 on the sample schema shown, and the outputs are copied from those runs. Names in the sample data are made up.

• PostgreSQL documentation, Window functions tutorial and Date/time functions (DATE_TRUNC), checked September 2026.
• SQLite, Window functions and Date and time functions (julianday), checked September 2026.
• MySQL 8.4 Reference Manual, JOIN clause, checked September 2026.
• Practice platforms mentioned: HackerRank SQL, LeetCode, DataLemur. Free and paid tiers change; check each site.

The topic list and preparation tips are ISS editorial guidance based on common analyst interview formats, not survey data.

Next steps

SQL basics and advanced SQL (joins, CTEs, window functions and cohort queries) are Weeks 3 and 4 of the Data & Business Intelligence program. The program's career support includes mock interviews and resume and portfolio reviews. ISS does not guarantee placement. The free Data Analyst Starter Kit on this page also includes a bank of interview questions.

Applying is free, and you pay only after accepting an offer. Apply here.