Skip to content

SQL Interview Questions for Data Analytics

51 SQL questions with answers for data analytics roles — 12 easy, 32 medium, 7 hard.

About SQL

SQL is tested in nearly every data analyst interview, often as a live coding round. Expect joins, GROUP BY and HAVING, subqueries and CTEs, window functions like ROW_NUMBER and LAG, NULL handling, date functions and query performance basics. Correct results matter, so interviewers watch how you check row counts, duplicates and edge cases.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is the difference between WHERE and HAVING?

SQLEasy
Save to ▾
Show answer
WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use aggregates like SUM() or COUNT().
02

INNER JOIN vs LEFT JOIN — with an example.

SQLEasy
Save to ▾
Show answer
INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table and NULLs where the right table has no match — e.g. all customers, including those with no orders.
03

Find the second highest salary in SQL.

SQLEasy
Save to ▾
Show answer
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp); -- or: DENSE_RANK() OVER (ORDER BY salary DESC) = 2
04

What is a primary key vs a foreign key?

SQLEasy
Save to ▾
Show answer
A primary key uniquely identifies each row in its table; a foreign key references another table's primary key to enforce relationships.
05

What is the difference between DELETE, TRUNCATE and DROP?

SQLEasy
Save to ▾
Show answer
DELETE removes chosen rows and can be rolled back; TRUNCATE empties the table quickly; DROP removes the table structure itself.
06

Explain the types of SQL joins.

SQLEasy
Save to ▾
Show answer
INNER returns matching rows, LEFT keeps all left rows, RIGHT keeps all right rows, FULL keeps all rows from both, and CROSS returns every combination.
07

What do COALESCE and NULLIF do in SQL?

SQLEasy
Save to ▾
Show answer
COALESCE returns the first non-null value from its arguments, for example COALESCE(phone, alt_phone, 'Not given') fills missing phone numbers. NULLIF(a, b) returns NULL when a equals b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(orders, 0) returns NULL instead of an error when orders is zero. Together they handle most null-related reporting problems cleanly.
08

What is the difference between a fact table and a dimension table?

SQLEasy
Save to ▾
Show answer
A fact table stores measurable events at a defined grain, such as one row per order line, with numeric measures like quantity and amount plus foreign keys. Dimension tables describe the context of those events: customer, product, store and date, with descriptive attributes used for filtering and grouping. Facts are long and grow quickly; dimensions are wider and smaller. Together they form star or snowflake schemas.
09

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

SQLEasy
Save to ▾
Show answer
COUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. For example, in an orders table, COUNT(*) gives number of orders, COUNT(coupon_code) gives orders that used a coupon, and COUNT(DISTINCT customer_id) gives number of unique buyers. Mixing these up is a common source of wrong KPIs.
10

Write SQL to count orders by week and by day of week.

SQLEasy
Save to ▾
Show answer
-- orders per week (PostgreSQL style) SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(*) AS orders FROM orders GROUP BY 1 ORDER BY 1; -- orders by day of week (0 = Sunday) SELECT EXTRACT(DOW FROM order_date) AS dow, COUNT(*) AS orders FROM orders GROUP BY 1 ORDER BY 1;
11

What is the difference between a primary key and a unique constraint?

SQLEasy
Save to ▾
Show answer
Both prevent duplicate values. A table can have only one primary key, it cannot contain NULLs, and it is the main identifier other tables reference through foreign keys. A table can have several unique constraints, for example on email and on PAN number, and most databases allow NULLs in a unique column. Use the primary key for the row identity and unique constraints for other business rules.
12

A report filters WHERE order_date = '2026-03-31' and misses almost all orders from that day. Why?

SQLEasy
Save to ▾
Show answer
order_date is probably a DATETIME or TIMESTAMP column, so the filter matches only rows at exactly midnight, 2026-03-31 00:00:00. Use a half-open range instead: WHERE order_date >= '2026-03-31' AND order_date < '2026-04-01'. This keeps the index usable, unlike wrapping the column in CAST or DATE(), and it correctly includes orders at 23:59:59.500. Also check the time zone of the stored timestamps.
13

RANK vs DENSE_RANK vs ROW_NUMBER?

SQLMedium
Save to ▾
Show answer
ROW_NUMBER gives a unique sequence. RANK gives ties the same rank and skips the next (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
14

How do you calculate a running total?

SQLMedium
Save to ▾
Show answer
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds up values cumulatively.
15

Difference between UNION and UNION ALL?

SQLMedium
Save to ▾
Show answer
UNION removes duplicate rows and sorts internally; UNION ALL keeps every row and is faster.
16

What is a window function?

SQLMedium
Save to ▾
Show answer
A function that calculates over a set of rows related to the current row without collapsing them, such as ROW_NUMBER, RANK, LAG or SUM() OVER (PARTITION BY ...).
17

Explain GROUP BY with an example.

SQLMedium
Save to ▾
Show answer
It groups rows sharing values so aggregates run per group, for example SELECT region, SUM(sales) FROM orders GROUP BY region.
18

What is a self join and when is it used?

SQLMedium
Save to ▾
Show answer
Joining a table to itself with aliases, useful for hierarchies such as employees and managers or comparing rows within the same table.
19

What are indexes and when can they hurt?

SQLMedium
Save to ▾
Show answer
Indexes speed up lookups by keeping sorted pointers. They slow writes and use space, so index columns used in filters and joins, not every column.
20

Write SQL for a running total of sales by date.

SQLMedium
Save to ▾
Show answer
SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM sales;
21

What is normalisation?

SQLMedium
Save to ▾
Show answer
Organising tables to reduce redundancy and anomalies by splitting data into related tables, commonly up to third normal form.
22

What is the difference between a clustered and a non-clustered index?

SQLMedium
Save to ▾
Show answer
A clustered index defines the physical order of rows (one per table); a non-clustered index is a separate structure with pointers to the rows.
23

What is a CTE and when would you use one?

SQLMedium
Save to ▾
Show answer
A Common Table Expression (WITH name AS (...)) is a named temporary result used inside one query. Use it to break complex logic into readable steps or for recursive queries.
24

What is a CTE and why use it?

SQLMedium
Save to ▾
Show answer
A Common Table Expression is a named temporary result defined with WITH that makes complex queries readable and allows recursion.
25

What does CASE WHEN do?

SQLMedium
Save to ▾
Show answer
It adds conditional logic inside queries, for example bucketing ages or flagging values, returning different results per condition.
26

What is the difference between a view and a materialised view?

SQLMedium
Save to ▾
Show answer
A view stores a query and runs it each time; a materialised view stores the result physically and must be refreshed, making reads faster.
27

How do you calculate a rolling average?

SQLMedium
Save to ▾
Show answer
df['sales'].rolling(window=7).mean() gives a 7-period moving average, useful to smooth daily noise.
28

How are NULLs handled in SQL?

SQLMedium
Save to ▾
Show answer
NULL means unknown, so comparisons with = fail; use IS NULL, COALESCE for defaults and remember most aggregates ignore NULLs except COUNT(*).
29

Difference between a CTE, a subquery and a temp table?

SQLMedium
Save to ▾
Show answer
A subquery is inline; a CTE is a named, readable block for one statement; a temp table is physically stored for the session and can be indexed and reused.
30

How do you find duplicate rows in a table?

SQLMedium
Save to ▾
Show answer
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; use ROW_NUMBER() partitioned by the key to delete extras.
31

How would you find customers who never placed an order?

SQLMedium
Save to ▾
Show answer
LEFT JOIN customers to orders and keep rows where order_id IS NULL, or use NOT EXISTS, which also handles NULLs safely.
32

What is the logical order in which SQL executes a SELECT query?

SQLMedium
Save to ▾
Show answer
Although written as SELECT first, a query is logically processed as FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. This explains common errors: you cannot use a SELECT alias in WHERE because SELECT runs later, but you can use it in ORDER BY. It also explains why aggregates belong in HAVING, not WHERE, and why window functions cannot be filtered in the same query level.
33

Write SQL using LAG to compare each month's revenue with the previous month.

SQLMedium
Save to ▾
Show answer
WITH monthly AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct FROM monthly ORDER BY month;
34

Write SQL to show each category's percentage share of total revenue.

SQLMedium
Save to ▾
Show answer
SELECT category, SUM(amount) AS revenue, ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct_share FROM sales GROUP BY category ORDER BY revenue DESC; -- SUM(SUM(amount)) OVER () is the grand total across all groups
35

Your NOT IN query unexpectedly returns zero rows. Why might that happen, and how do EXISTS and IN differ?

SQLMedium
Save to ▾
Show answer
IN checks whether a value is in a list or subquery result; EXISTS checks whether a subquery returns at least one row and stops at the first match. Results are the same in most cases, and modern optimisers often run them similarly. The big difference is NOT IN with NULLs: if the subquery returns any NULL, NOT IN returns no rows. NOT EXISTS behaves correctly, so prefer it for anti-joins.
36

What is a correlated subquery?

SQLMedium
Save to ▾
Show answer
A correlated subquery refers to a column from the outer query, so it is logically evaluated once per outer row. Example: find employees earning above their department average with WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id). It is readable but can be slow on large tables. The same result is often faster with a window function such as AVG(salary) OVER (PARTITION BY dept_id).
37

After joining orders to payments, your revenue total doubles. What went wrong?

SQLMedium
Save to ▾
Show answer
This is a join fan-out: an order has more than one matching payment row, for example split payments or retries, so each order line is repeated and sums are inflated. Check by counting rows per key on both sides. Fix it by aggregating payments to one row per order before joining, joining at the correct grain, or using the right key. Always compare row counts and totals before and after a join.
38

How do you pivot rows into columns in SQL, for example sales by quarter as columns?

SQLMedium
Save to ▾
Show answer
SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2, SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3, SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4 FROM sales GROUP BY region; -- conditional aggregation works in every database; some also offer PIVOT
39

Tell me about a time a query you wrote produced wrong results.

SQLMedium
Save to ▾
Show answer
Situation: my weekly active users number jumped 30 percent overnight. Task: find out whether it was real before the report went out. Action: I traced the query and found a new join to a device table created duplicate users. I changed the count to COUNT(DISTINCT user_id), added a row-count check, and informed my manager before publishing. Result: the corrected number went out on time, and I added that check to every report.
40

What is a transaction and what do the ACID properties mean?

SQLMedium
Save to ▾
Show answer
A transaction is a group of SQL statements that succeed or fail together, such as debiting one account and crediting another. ACID: Atomicity means all or nothing; Consistency means rules and constraints stay valid; Isolation means concurrent transactions do not see each other's partial work; Durability means committed data survives crashes. Analysts mostly read data, but knowing ACID explains why reports should read committed data.
41

What is the difference between a data warehouse, a data lake and a lakehouse?

SQLMedium
Save to ▾
Show answer
A data warehouse stores cleaned, structured data in modelled tables optimised for SQL reporting. A data lake stores raw data of any type, such as logs, JSON and images, cheaply in files, with structure applied when read. A lakehouse combines them: open table formats on lake storage add transactions, schemas and fast SQL on top of files. Analysts typically query the warehouse or the curated layer of a lakehouse.
42

Write SQL to find the median order value overall and per city.

SQLMedium
Save to ▾
Show answer
-- Works in PostgreSQL, Snowflake, SQL Server (with OVER for SQL Server) SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_value, AVG(order_value) AS mean_value FROM orders GROUP BY city ORDER BY median_value DESC;
43

Many reporting queries in your team use SELECT DISTINCT to remove duplicate rows. What is the risk?

SQLMedium
Save to ▾
Show answer
SELECT DISTINCT often hides a real problem, such as a join on the wrong key or a one-to-many join that multiplies rows. The output looks clean, but sums computed before the DISTINCT, or legitimate repeated rows, can be wrong, and DISTINCT adds a costly sort on large tables. Instead, find why duplicates appear, fix the join or aggregate the child table first, and add a uniqueness test on the key.
44

Write SQL to find the number of days between each customer's consecutive orders and their average gap.

SQLMedium
Save to ▾
Show answer
WITH gaps AS ( SELECT customer_id, order_date, order_date - LAG(order_date) OVER ( PARTITION BY customer_id ORDER BY order_date) AS days_since_prev FROM orders ) SELECT customer_id, COUNT(*) AS orders, AVG(days_since_prev) AS avg_gap_days FROM gaps GROUP BY customer_id HAVING COUNT(*) > 1; -- order_date is DATE (PostgreSQL); use DATEDIFF in other databases
45

How would you calculate retention using SQL?

SQLHard
Save to ▾
Show answer
Find each user's first activity date as a cohort, join to later activity, and compute the share of each cohort active in day, week or month N.
46

How do you optimise a slow SQL query?

SQLHard
Save to ▾
Show answer
Read the execution plan, add or fix indexes, select only needed columns, filter early, avoid functions on indexed columns, and replace correlated subqueries with joins.
47

Find customers who ordered in January but not in February.

SQLHard
Save to ▾
Show answer
SELECT DISTINCT customer_id FROM orders WHERE month = 'Jan' EXCEPT SELECT customer_id FROM orders WHERE month = 'Feb';
48

Write SQL to find the top 3 products by revenue within each category.

SQLHard
Save to ▾
Show answer
WITH ranked AS ( SELECT category, product, SUM(amount) AS revenue, DENSE_RANK() OVER ( PARTITION BY category ORDER BY SUM(amount) DESC ) AS rnk FROM sales GROUP BY category, product ) SELECT category, product, revenue FROM ranked WHERE rnk <= 3 ORDER BY category, rnk;
49

What is a query execution plan and how do you read it?

SQLHard
Save to ▾
Show answer
An execution plan shows how the database will run a query: which indexes it uses, join methods such as nested loop or hash join, scan types and estimated rows. You get it with EXPLAIN, or EXPLAIN ANALYZE for actual timings. Look for full table scans on big tables, large gaps between estimated and actual rows, and expensive sorts. These point to missing indexes, stale statistics or non-sargable filters like functions on columns.
50

What is a slowly changing dimension? Explain Type 1 and Type 2.

SQLHard
Save to ▾
Show answer
A slowly changing dimension handles attributes that change over time, like a customer's city. Type 1 overwrites the old value, so history is lost and past sales appear under the new city. Type 2 adds a new row with a new surrogate key plus valid_from, valid_to and is_current columns, so old sales stay linked to the old city. Type 2 preserves accurate history but makes the table larger and joins more careful.
51

Write SQL to find users who logged in on at least 3 consecutive days.

SQLHard
Save to ▾
Show answer
WITH d AS ( SELECT DISTINCT user_id, CAST(login_time AS DATE) AS dt FROM logins ), g AS ( SELECT user_id, dt, dt - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS INT) AS grp FROM d ) SELECT DISTINCT user_id FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; -- gaps and islands: consecutive dates share the same grp value

Frequently asked questions

How important is SQL in Data Analytics interviews?

SQL is tested in nearly every data analyst interview, often as a live coding round. Expect joins, GROUP BY and HAVING, subqueries and CTEs, window functions like ROW_NUMBER and LAG, NULL handling, date functions and query performance basics. Correct results matter, so interviewers watch how you check row counts, duplicates and edge cases.

How should I use the SQL sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.