Skip to content
RED SAGSkills

SQL interview questions and answers

SQL interview questions test whether you can turn a business question into a correct query. Most mistakes come from joins, NULLs, grouping and window functions, so that is where interviewers focus.

Below are 60 SQL questions with the correct answer and a short explanation, grouped from fresher to experienced level. Try each one before you open the answer.

Take the SQL MCQ testPractise 331+ SQL questions for points

What SQL interviews usually check

  • ✓SELECT, WHERE, GROUP BY and HAVING
  • ✓INNER, LEFT and self joins, and duplicate rows
  • ✓NULL handling, CASE and aggregate functions
  • ✓Window functions: ROW_NUMBER, RANK, LAG and running totals
  • ✓Subqueries, CTEs and query performance

SQL interview questions for freshers easy

1. Stopping SQL injection

This login code builds SQL by joining strings with user input. What is the correct way to prevent SQL injection?

const sql = "SELECT * FROM users WHERE email = '" + email + "'"
await db.query(sql)
  1. A.Remove all single quotes from the email before building the query
  2. B.Validate the email on the frontend with a regex
  3. C.Run the query inside a database transaction
  4. D.Use a parameterized query, e.g. db.query("SELECT * FROM users WHERE email = $1", [email])
Show answer

Answer: D. Use a parameterized query, e.g. db.query("SELECT * FROM users WHERE email = $1", [email])

Parameterized queries (prepared statements) send the SQL and the data separately, so the database never treats user input as SQL code. Removing quotes or frontend validation can be bypassed, and transactions have nothing to do with injection.

2. Rows come back in a new order

A report query has no ORDER BY. It always returned rows by id, but after a database upgrade the order changed. What is true?

  1. A.The upgrade broke the table's primary key
  2. B.Rows are always returned in insert order
  3. C.The order only changes when rows are deleted
  4. D.Without ORDER BY, row order is never guaranteed
Show answer

Answer: D. Without ORDER BY, row order is never guaranteed

SQL tables are unordered sets, and the database may return rows in any order depending on plans, parallel scans or storage. If order matters, say so with ORDER BY. This also matters for pagination with LIMIT.

3. INNER JOIN vs LEFT JOIN

You want every customer listed, including customers who have never placed an order. Which join do you use from customers to orders?

  1. A.INNER JOIN
  2. B.LEFT JOIN
  3. C.CROSS JOIN
  4. D.A self join on customers
Show answer

Answer: B. LEFT JOIN

A LEFT JOIN keeps all rows from the left table (customers) and fills order columns with NULL when there is no match. An INNER JOIN would drop customers without orders, and a CROSS JOIN returns every combination of rows.

4. Row order without ORDER BY

A report always showed rows in insert order, but after a VACUUM and an index change the order changed. What is true?

  1. A.The database is corrupted and must be restored
  2. B.Without ORDER BY, SQL does not guarantee any row order
  3. C.Rows are always returned by primary key unless VACUUM runs
  4. D.Indexes control the default display order of a table
Show answer

Answer: B. Without ORDER BY, SQL does not guarantee any row order

Relational tables are unordered sets, so the order without ORDER BY depends on the plan, physical layout and parallel workers. It can change at any time. Any query whose result order matters, including pagination, must use ORDER BY.

5. NOT IN returns no rows

This query returns zero rows, though many customers have no blocked entry. The blocked table has one row where customer_id is NULL. Why?

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM blocked);
  1. A.NOT IN cannot be used with subqueries
  2. B.NULL rows are deleted by the query
  3. C.NOT IN with a NULL in the list is never true
  4. D.NOT IN only works on indexed columns
Show answer

Answer: C. NOT IN with a NULL in the list is never true

x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and the last part is unknown, so no row passes. Use NOT EXISTS, which handles NULLs as most people expect, or filter NULLs out of the subquery.

6. Filtering groups in SQL

You want customers who placed more than 5 orders. After GROUP BY customer_id, how do you filter the groups?

  1. A.WHERE COUNT(*) > 5 before GROUP BY
  2. B.ORDER BY COUNT(*) > 5
  3. C.LIMIT 5 after GROUP BY
  4. D.HAVING COUNT(*) > 5 after GROUP BY
Show answer

Answer: D. HAVING COUNT(*) > 5 after GROUP BY

WHERE filters individual rows before grouping, so it cannot use aggregates like COUNT(*). HAVING filters groups after aggregation. A common pattern is WHERE for row conditions, then GROUP BY, then HAVING for group conditions.

7. COUNT of a column with NULLs

A table has 10 rows. The column phone is NULL in 2 of them. What does SELECT COUNT(phone) FROM t return?

  1. A.10
  2. B.8
  3. C.2
  4. D.NULL
Show answer

Answer: B. 8

COUNT(column) counts only non-NULL values, so it returns 8. COUNT(*) counts all rows and would return 10. Mixing these up is a frequent source of wrong completion rates.

8. WHERE vs HAVING

You want to list only those cities whose total sales are above 1,00,000. Which clause should filter on the total?

SELECT city, SUM(amount) AS total
FROM orders
GROUP BY city
-- filter here?
  1. A.WHERE SUM(amount) > 100000
  2. B.HAVING SUM(amount) > 100000
  3. C.ORDER BY SUM(amount) > 100000
  4. D.LIMIT SUM(amount) > 100000
Show answer

Answer: B. HAVING SUM(amount) > 100000

WHERE filters individual rows before grouping, so it cannot use aggregate functions like SUM. HAVING filters groups after GROUP BY, so it is the correct place to filter on an aggregated total.

9. Finding NULL values

Which query correctly returns rows where phone is missing (NULL)?

  1. A.SELECT * FROM users WHERE phone = NULL
  2. B.SELECT * FROM users WHERE phone = ''
  3. C.SELECT * FROM users WHERE phone IS NULL
  4. D.SELECT * FROM users WHERE phone == NULL
Show answer

Answer: C. SELECT * FROM users WHERE phone IS NULL

In SQL, NULL means unknown, so any comparison with = NULL gives UNKNOWN, not TRUE, and returns no rows. You must use IS NULL. An empty string is a real value and is different from NULL.

10. Matching text with LIKE

Which of these product codes match WHERE code LIKE 'A_1%' ?

Codes: 'AB1', 'A1', 'AX123', 'BA1X'
  1. A.'AB1' and 'AX123'
  2. B.Only 'AX123'
  3. C.'A1' and 'AB1'
  4. D.All four
Show answer

Answer: A. 'AB1' and 'AX123'

In LIKE, the underscore matches exactly one character and % matches zero or more characters. The pattern needs A, then any one character, then 1, then anything. AB1 and AX123 fit; A1 is too short and BA1X does not start with A.

11. Top seller with ORDER BY and LIMIT

Sales per rep are: Ravi 500, Sara 900, Omar 700. What does this query return?

SELECT rep FROM sales
ORDER BY amount DESC
LIMIT 1;
  1. A.Ravi
  2. B.Omar
  3. C.Sara
  4. D.All three reps
Show answer

Answer: C. Sara

ORDER BY amount DESC sorts from highest to lowest, so Sara (900) is first. LIMIT 1 keeps only that first row. Without DESC the default is ascending and the query would return Ravi.

12. A running total column

Daily sales are in B2:B31. Which formula in C2, copied down, gives a running total?

  1. A.=SUM(B2:B31)
  2. B.=SUM($B$2:$B$31)
  3. C.=SUM($B$2:B2)
  4. D.=B2+B3
Show answer

Answer: C. =SUM($B$2:B2)

Anchoring the start ($B$2) while leaving the end relative (B2) makes the range grow as you copy down: B2:B2, then B2:B3, and so on. A fully fixed range would repeat the grand total in every row.

13. What LEAD returns

Which expression puts the date of each customer's next order on the current row?

  1. A.LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
  2. B.MAX(order_date) OVER (PARTITION BY customer_id)
  3. C.FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
  4. D.LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
Show answer

Answer: D. LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)

LEAD reads a value from a following row and LAG from a preceding row. Partitioning by customer stops the next order from coming from a different customer. MAX gives the latest order overall, not the next one.

14. Team size on every employee row

What does COUNT(*) OVER (PARTITION BY team_id) show on each employee row?

  1. A.The number of employees in that employee's team
  2. B.The position of the employee within the team
  3. C.The number of teams in the company
  4. D.The total number of employees
Show answer

Answer: A. The number of employees in that employee's team

With PARTITION BY and no ORDER BY, the frame is the whole partition. COUNT(*) therefore returns the team's headcount on every row of that team. ROW_NUMBER would give the position instead.

15. ORDER BY inside and outside OVER

A query uses ROW_NUMBER() OVER (ORDER BY sales DESC) but has no ORDER BY at the end. What is true about the output order?

  1. A.Rows always come out sorted by sales DESC
  2. B.Rows always come out in rn order
  3. C.The output order is not guaranteed
  4. D.Rows come out in insert order
Show answer

Answer: C. The output order is not guaranteed

ORDER BY inside OVER only decides how the window function numbers or accumulates rows. The order of the final result is set only by the query's own ORDER BY. Add ORDER BY rn if you need sorted output.

16. Computing PERCENT_RANK

Five students are ordered by marks with no ties. What is PERCENT_RANK() for the student in 3rd place?

  1. A.0.6
  2. B.0.4
  3. C.0.75
  4. D.0.5
Show answer

Answer: D. 0.5

PERCENT_RANK is (rank - 1) / (rows - 1). For 3rd place out of 5 that is (3 - 1) / (5 - 1) = 0.5. The first row is always 0 and the last row is 1.

17. Highest DENSE_RANK value

A partition has 12 rows but only 5 distinct prices. What is the largest DENSE_RANK() OVER (ORDER BY price) in it?

  1. A.12
  2. B.6
  3. C.5
  4. D.7
Show answer

Answer: C. 5

DENSE_RANK gives one number per distinct value with no gaps, so the top number equals the count of distinct values, 5. RANK would end at a number up to 12 depending on ties, and ROW_NUMBER always ends at 12.

18. Reusing a window alias

Why does this fail in most databases?

SELECT amount,
       SUM(amount) OVER () AS total,
       amount / total AS share
FROM sales;
  1. A.Division is not allowed next to a window function
  2. B.SUM needs a PARTITION BY clause to run at all
  3. C.Only one window function is allowed in a query
  4. D.An alias cannot be used in the same SELECT list that defines it
Show answer

Answer: D. An alias cannot be used in the same SELECT list that defines it

All expressions in one SELECT list are evaluated together, so total does not exist yet when share is computed. Repeat the expression (amount / SUM(amount) OVER ()) or compute total in a CTE and divide in the outer query.

19. Removing duplicate order ids

A table has repeated rows for the same order_id with different load_time values. Which call keeps one row per order_id?

  1. A.df.distinct()
  2. B.df.dropna(subset=["order_id"])
  3. C.df.dropDuplicates(["order_id"])
  4. D.df.groupBy("order_id").count()
Show answer

Answer: C. df.dropDuplicates(["order_id"])

dropDuplicates with a column list removes rows that repeat on those columns only. distinct() compares every column, so rows that differ in load_time are all kept. Note that which duplicate survives is not guaranteed; use row_number over a window when you need a specific one.

20. Filtering rows in SQL

Which query returns all orders with an amount greater than 500?

  1. A.SELECT amount > 500 FROM orders;
  2. B.SELECT * FROM orders GROUP BY amount > 500;
  3. C.SELECT * WHERE amount > 500 FROM orders;
  4. D.SELECT * FROM orders WHERE amount > 500;
Show answer

Answer: D. SELECT * FROM orders WHERE amount > 500;

WHERE filters rows before they are returned, and it comes after FROM. Selecting 'amount > 500' returns a true or false column for every row instead of filtering. The GROUP BY version groups rather than filters, and the last option has the clauses in the wrong order.

SQL intermediate interview questions medium

21. Isolation levels and dirty reads

Transaction A updates a balance but has not committed yet. Transaction B must never see that uncommitted value. What is the lowest standard isolation level that guarantees this?

  1. A.Read Uncommitted
  2. B.Serializable only
  3. C.Read Committed
  4. D.Repeatable Read only
Show answer

Answer: C. Read Committed

A dirty read is reading data another transaction has not committed. Read Committed is the lowest SQL isolation level that prevents dirty reads, and it is the default in PostgreSQL. Higher levels also prevent it, but they are not the minimum required.

22. CTE acts as an optimisation fence

On PostgreSQL 16, a query uses WITH x AS MATERIALIZED (SELECT ...) and the planner cannot push the outer WHERE into it. What does removing MATERIALIZED do?

  1. A.Forces the CTE to run twice
  2. B.Lets the planner inline the CTE when it is used once
  3. C.Turns the CTE into a temporary table on disk
  4. D.Makes the CTE run in a separate transaction
Show answer

Answer: B. Lets the planner inline the CTE when it is used once

Since PostgreSQL 12, a side-effect-free CTE referenced once is inlined by default, so outer filters can be pushed in and indexes used. MATERIALIZED forces it to be computed once as a separate step, which can be useful or harmful. NOT MATERIALIZED forces inlining.

23. Latest order per customer

Which approach returns only the most recent order for each customer?

  1. A.SELECT * FROM orders ORDER BY order_date DESC LIMIT 1;
  2. B.Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) and keep rows where it equals 1
  3. C.SELECT DISTINCT customer_id, order_date FROM orders;
  4. D.SELECT customer_id, MAX(order_id) FROM orders;
Show answer

Answer: B. Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) and keep rows where it equals 1

The window function numbers each customer orders from newest to oldest, and filtering on row number 1 keeps the latest one with all its columns. LIMIT 1 returns only one row overall, and MAX(order_id) assumes IDs follow date order and lacks GROUP BY.

24. NDCG for a short ranking

A search model returns two results with relevance 0 then 3. Using gain = relevance and discount 1/log₂(position + 1), what is NDCG@2?

  1. A.About 0.50
  2. B.About 1.00
  3. C.About 0.63
  4. D.About 0.33
Show answer

Answer: C. About 0.63

DCG = 0/1 + 3/log₂3 ≈ 3/1.585 ≈ 1.89. The ideal order (3 then 0) gives IDCG = 3. NDCG = 1.89/3 ≈ 0.63. Tracking NDCG on labelled queries shows ranking quality drops that click rate alone may hide.

25. Revenue doubled after a join

You join orders to order_items and then SUM(orders.total) by month. The revenue is much higher than finance reports. What is the most likely cause?

SELECT DATE_TRUNC('month', o.created_at) AS m,
       SUM(o.total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY 1
  1. A.DATE_TRUNC rounds dates into the wrong month
  2. B.An INNER JOIN always removes rows, so revenue should be lower
  3. C.Each order repeats once per item, so its total is summed many times (join fan-out)
  4. D.SUM ignores NULLs, which raises the total
Show answer

Answer: C. Each order repeats once per item, so its total is summed many times (join fan-out)

A one-to-many join repeats the order row for every item, so an order with 3 items has its total counted 3 times. Aggregate at the correct grain first (sum order totals without the join, or sum item amounts), or pre-aggregate items in a CTE before joining.

26. Share of paid orders in one query

Order statuses are: paid, paid, refunded, paid, cancelled. What does this query return?

SELECT AVG(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END) AS paid_rate
FROM orders;
  1. A.0.6
  2. B.3
  3. C.0.75
  4. D.1.0
Show answer

Answer: A. 0.6

The CASE turns each row into 1.0 for paid and 0 for anything else, giving 1, 1, 0, 1, 0. The average of those is 3 / 5 = 0.6. This conditional aggregation pattern lets you compute many rates in one pass without subqueries.

27. Grouping orders by month

The orders table covers January 2025 to March 2026. You want one row per calendar month. Which GROUP BY expression is correct in PostgreSQL?

  1. A.EXTRACT(MONTH FROM order_ts)
  2. B.DATE_TRUNC('month', order_ts)
  3. C.TO_CHAR(order_ts, 'Mon')
  4. D.EXTRACT(DAY FROM order_ts)
Show answer

Answer: B. DATE_TRUNC('month', order_ts)

DATE_TRUNC('month', ...) cuts each timestamp down to the first day of its month while keeping the year, so January 2025 and January 2026 stay separate. EXTRACT(MONTH ...) and TO_CHAR with 'Mon' return just the month, which silently merges the same month from different years.

28. SUM over zero matching rows

No orders exist for the region 'Fujairah'. What does this query return, and how do you make it show 0?

SELECT SUM(amount) FROM orders WHERE region = 'Fujairah';
  1. A.It returns 0, so nothing needs to change
  2. B.It returns no rows; add GROUP BY region
  3. C.It raises an error; add a TRY block
  4. D.It returns one row with NULL; wrap it as COALESCE(SUM(amount), 0)
Show answer

Answer: D. It returns one row with NULL; wrap it as COALESCE(SUM(amount), 0)

An aggregate without GROUP BY always returns one row, and SUM over no rows (or only NULLs) is NULL, not 0. COALESCE(SUM(amount), 0) turns that NULL into 0. COUNT(*) is the exception: it returns 0 on no rows.

29. Weighted average price

Quantities are in B2:B4 (100, 300, 600) and unit prices in C2:C4 (₹50, ₹40, ₹30). What is the weighted average price per unit?

  1. A.₹40
  2. B.₹33
  3. C.₹35
  4. D.₹38
Show answer

Answer: C. ₹35

SUMPRODUCT gives 100×50 + 300×40 + 600×30 = 5,000 + 12,000 + 18,000 = 35,000. Divided by 1,000 units, the weighted average is ₹35. A simple AVERAGE of the prices gives ₹40, which ignores that most units were bought at ₹30.

30. Month labels sort in the wrong order

A helper column uses =TEXT(A2,"mmm-yy") to label months, and a pivot on it lists Apr-26, Aug-26, Dec-26, Feb-26 and so on. Why?

  1. A.TEXT always converts dates to the US format
  2. B.Pivot tables cannot sort by any month name at all
  3. C.TEXT returns text, which sorts A to Z, not by date
  4. D.The format code "mmm" means minutes, not months
Show answer

Answer: C. TEXT returns text, which sorts A to Z, not by date

TEXT turns a date into a label, and labels sort alphabetically. Keep a real date such as =EOMONTH(A2,-1)+1 (the first of the month) and format it as mmm-yy, or group the date field in the pivot. Then sorting follows the calendar.

31. Working days in two weeks

Monday 2-Mar-2026 to Friday 13-Mar-2026, with one public holiday on a Wednesday in that period listed in H2. What does =NETWORKDAYS(A2,B2,H2) return?

  1. A.9
  2. B.10
  3. C.12
  4. D.8
Show answer

Answer: A. 9

NETWORKDAYS counts Monday to Friday, including the start and end dates, and subtracts listed holidays. Two full weeks give 10 working days, minus 1 holiday = 9. Weekends are Saturday and Sunday unless you use NETWORKDAYS.INTL.

32. Customers listed many times

You want the list of customers who have placed at least one order. The JOIN version repeats a customer once per order. Which query returns each such customer once, without DISTINCT?

  1. A.LEFT JOIN orders o ON o.customer_id = c.id AND o.id IS NOT NULL
  2. B.CROSS JOIN orders o WHERE o.customer_id = c.id
  3. C.WHERE c.id = (SELECT customer_id FROM orders)
  4. D.WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
Show answer

Answer: D. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

EXISTS is a semi-join: it only checks whether a matching order exists, so each customer row appears at most once. The LEFT JOIN keeps every customer, even those with no orders, and still repeats buyers. The = (subquery) version fails when the subquery returns more than one row.

33. Each region's share of total sales

The regional_sales table has one row per region. What does pct hold?

  1. A.Each region's percentage of the grand total
  2. B.Each region's running total in percent
  3. C.The percentage change from the previous region
  4. D.Always 100.0, since every row sums to itself
Show answer

Answer: A. Each region's percentage of the grand total

SUM(sales) OVER () with an empty window is the total over all rows, repeated on each row. Dividing each region's sales by it gives its share. Using 100.0 instead of 100 avoids integer division in databases that would otherwise round down to 0.

34. Counting ticket status changes

A ticket history table has one row per update, often repeating the same status. How do you count real status changes per ticket?

  1. A.Count the distinct statuses each ticket has ever had
  2. B.Count all history rows for each ticket and subtract 1
  3. C.Count rows where status differs from LAG(status) in time order
  4. D.Count rows where status differs from MAX(status) for the ticket
Show answer

Answer: C. Count rows where status differs from LAG(status) in time order

Comparing each row with LAG(status) shows when the status actually changed. COUNT(DISTINCT status) misses a status that returns, such as Open, Closed, Open again. COUNT(*) - 1 counts updates that did not change the status.

35. Joining facts to the right SCD2 version

Facts store customer_id and order_ts, not the surrogate key. How do you join each order to the customer version valid at the time?

  1. A.customer_id only, taking any matching row
  2. B.customer_id, and valid_from <= order_ts < valid_to
  3. C.customer_id, and is_current = true
  4. D.customer_id, and valid_from = order_ts
Show answer

Answer: B. customer_id, and valid_from <= order_ts < valid_to

An as-of join matches the business key and puts the fact time inside the version's validity window. Using half-open ranges [valid_from, valid_to) avoids an order matching two versions at the boundary. is_current would show today's values for old orders.

36. Matching orders with payments

You want to match each order event with its payment event, which usually arrives within 10 minutes. Both are streams keyed by order_id. What does a KStream-KStream join need?

  1. A.A GlobalKTable for the payments
  2. B.Compaction on both topics
  3. C.The same number of consumers as partitions
  4. D.A join window, such as 10 minutes
Show answer

Answer: D. A join window, such as 10 minutes

Two unbounded streams cannot be joined over all time, because state would grow forever. A join window says how far apart in time two records can be and still match. Both sides are buffered in window stores for that period, plus any grace.

37. Counting by local day

Events are stored in UTC. You need daily orders by India Standard Time. What should the query do?

  1. A.Take the UTC date and add 5.5 to the count
  2. B.Count by UTC and relabel the chart as IST
  3. C.Only count orders placed after 05:30
  4. D.Convert timestamps to IST before taking the date
Show answer

Answer: D. Convert timestamps to IST before taking the date

Convert each timestamp to Asia/Kolkata, then take the date, for example DATE(created_at AT TIME ZONE 'Asia/Kolkata') in Postgres-style SQL. Otherwise late-evening orders land on the wrong day.

38. Days between orders with LAG

Why is gap_days NULL for some rows in this query?

SELECT user_id, order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY user_id ORDER BY order_date) AS gap_days
FROM orders;
  1. A.NULLs mean the order was cancelled
  2. B.PARTITION BY removes all repeat orders
  3. C.LAG only works for the last row in the table
  4. D.Each user's first order has no earlier order to compare
Show answer

Answer: D. Each user's first order has no earlier order to compare

LAG returns the previous row within each user's partition. The first order has none, so the result is NULL. Exclude these rows when computing the typical reorder gap, or report them as first orders.

39. Finding customers with no orders

You need a list of customers who have never placed an order. Which query is correct?

-- customers(id, name)
-- orders(id, customer_id, amount)
  1. A.SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL
  2. B.SELECT c.id, c.name FROM customers c INNER JOIN orders o ON o.customer_id = c.id WHERE o.amount = 0
  3. C.SELECT c.id, c.name FROM customers c INNER JOIN orders o ON o.customer_id = c.id
  4. D.SELECT c.id, c.name FROM customers c WHERE c.id IS NULL
Show answer

Answer: A. SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL

A LEFT JOIN keeps every customer, and customers with no matching order get NULL in the order columns. Filtering on NULL returns exactly those customers. An INNER JOIN only returns customers who do have orders.

40. SQL: active last month, not this month

The table `activity(customer_id, month)` has one row per customer per active month. Which query lists customers active in January 2026 but not in February 2026?

-- A
SELECT j.customer_id
FROM activity j
LEFT JOIN activity f
  ON f.customer_id = j.customer_id AND f.month = '2026-02'
WHERE j.month = '2026-01' AND f.customer_id IS NULL;

-- B
SELECT customer_id FROM activity
WHERE month = '2026-01' AND month <> '2026-02';

-- C
SELECT j.customer_id
FROM activity j
JOIN activity f ON f.customer_id = j.customer_id
WHERE j.month = '2026-01' AND f.month = '2026-02';

-- D
SELECT customer_id FROM activity
WHERE month IN ('2026-01', '2026-02');
  1. A.C
  2. B.D
  3. C.A
  4. D.B
Show answer

Answer: C. A

Query A keeps every January row and attaches a February row if one exists; keeping only rows where the February side is NULL leaves customers with no February activity. B checks one row at a time, so it simply returns January customers; C returns customers active in both months; D returns customers active in either.

SQL interview questions for experienced candidates hard

41. NOT IN with a NULL in the list

What does this query return?

-- customers: id 1, 2, 3
-- banned:    customer_id 2, NULL

SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM banned);
  1. A.1 and 3
  2. B.1, 2 and 3
  3. C.No rows
  4. D.An error, because the subquery contains NULL
Show answer

Answer: C. No rows

id NOT IN (2, NULL) means id <> 2 AND id <> NULL. Any comparison with NULL is unknown, so the condition is never true and no rows come back. Use NOT EXISTS with a correlated subquery, or filter out NULLs in the subquery, to get 1 and 3.

42. Better offline metric, worse A/B test

A new ranking model improves offline NDCG by 4% on logged click data, but the online A/B test shows lower engagement. Which explanation is most likely?

  1. A.The A/B test must have a bug, because offline metrics are always right
  2. B.NDCG cannot be computed on click data
  3. C.Logged data is biased by the old model (position and selection bias), so offline gains do not reflect real user behaviour
  4. D.The new model is too accurate for users
Show answer

Answer: C. Logged data is biased by the old model (position and selection bias), so offline gains do not reflect real user behaviour

Logged clicks only exist for items the old model showed, mostly in top positions. A new model is judged on this biased data, so offline gains can be misleading. Counterfactual evaluation (inverse propensity weighting), exploration traffic and trusting well-run online tests help close this gap.

43. PERCENTILE_CONT vs PERCENTILE_DISC

The values are 1, 2, 3 and 4. What do these two expressions return?

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x),
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY x)
FROM v;
  1. A.2 and 2
  2. B.2.5 and 2.5
  3. C.2 and 2.5
  4. D.2.5 and 2
Show answer

Answer: D. 2.5 and 2

PERCENTILE_CONT interpolates between values, so the median of 1, 2, 3, 4 is 2.5. PERCENTILE_DISC returns an actual value from the data: the first value whose cumulative share reaches 0.5, which is 2. Pick DISC when the answer must be a real observed value, such as a real price point.

44. Adding up daily active users

A manager adds the 30 daily active user counts for June and calls it June monthly active users. What is wrong?

  1. A.Nothing, it is the standard definition
  2. B.June has 30 days, so the sum should be divided by 31
  3. C.A user active on many days is counted many times; MAU must count distinct users across the whole month
  4. D.DAU should be averaged instead of summed to get MAU
Show answer

Answer: C. A user active on many days is counted many times; MAU must count distinct users across the whole month

Distinct counts are not additive: someone active on 20 days appears in 20 daily counts. MAU needs COUNT(DISTINCT user_id) over the month, which is always less than or equal to the sum of DAUs. The same trap applies to rolling up distinct customers from regions or weeks.

45. Financial year from a date

An Indian company's financial year runs April to March and is named by its ending year (FY2027 = Apr 2026 to Mar 2027). What does this return for 10-May-2026?

  1. A.2026
  2. B.2025
  3. C.2027
  4. D.#VALUE!
Show answer

Answer: C. 2027

MONTH(A2)>=4 is TRUE for May, and TRUE counts as 1 in arithmetic, so 2026 + 1 = 2027. For 15-Feb-2026 it would return 2026, since February is still in FY2026. The same trick works for any year that starts in a set month.

46. Monthly total from a date column

Dates are in A2:A5000 and amounts in C2:C5000. F2 holds 1-Mar-2026. Which formula totals March 2026 correctly?

  1. A.=SUMIFS(C:C,A:A,">="&F2,A:A,"<"&EDATE(F2,1))
  2. B.=SUMIFS(C:C,A:A,">="&F2,A:A,"<="&EOMONTH(F2,1))
  3. C.=SUMIFS(C:C,A:A,">="&F2,A:A,"<="&F2+31)
  4. D.=SUMIF(A:A,MONTH(F2),C:C)
Show answer

Answer: A. =SUMIFS(C:C,A:A,">="&F2,A:A,"<"&EDATE(F2,1))

Use a half-open range: on or after the 1st, and before the 1st of the next month, which EDATE gives. F2+31 with <= also picks up 1 April, EOMONTH(F2,1) runs to the end of April, and MONTH() returns 3, which never matches a date serial. This works even when dates carry times.

47. 7-day average over missing days

daily_sales has one row per day with sales, but days with no sales have no row. What is wrong with calling this a 7-day moving average?

  1. A.ROWS counts 7 rows, which can span more than 7 days
  2. B.AVG cannot be used as a window function
  3. C.ROWS BETWEEN only works with PARTITION BY
  4. D.The frame looks 6 rows ahead of the current row, not behind
Show answer

Answer: A. ROWS counts 7 rows, which can span more than 7 days

A ROWS frame counts physical rows, so when days are missing the 7 rows can cover 10 or more calendar days. Fill the gaps first by joining to a full calendar table with 0 sales, or use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW where supported. Then decide whether missing days should count as zero.

48. Month-1 retention for a cohort

In January, 400 users signed up. In February, 520 users were active: 140 from the January cohort and 380 who signed up in February. What is the January cohort's month-1 retention?

  1. A.27%
  2. B.35%
  3. C.130%
  4. D.73%
Show answer

Answer: B. 35%

Cohort retention divides users from that cohort still active by the cohort size: 140 ÷ 400 = 35%. New February sign-ups do not belong to the January cohort. 27% (140 ÷ 520) uses the wrong denominator, a common mistake in SQL when the join is not restricted to the cohort.

49. Streaks broken by repeat logins

A login streak query uses login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date). Users who log in twice on some days get wrong streaks. What fixes it?

  1. A.Reduce to one row per user per date before numbering
  2. B.Use RANK instead of ROW_NUMBER for the numbering
  3. C.Order by the login timestamp instead of the date
  4. D.Add login_date to the PARTITION BY of the window
Show answer

Answer: A. Reduce to one row per user per date before numbering

With two rows on one date, ROW_NUMBER still increases while the date does not, so the date minus row number shifts and the streak splits. Selecting DISTINCT user_id and login_date first makes one row per day. DENSE_RANK on the date also works, but RANK does not because it skips after ties.

50. Runs of the same machine status

Statuses by minute are UP, UP, DOWN, DOWN, UP. You compute ROW_NUMBER() OVER (ORDER BY ts) - ROW_NUMBER() OVER (PARTITION BY status ORDER BY ts). What does grouping by status and this difference give?

  1. A.Two groups: all UP rows and all DOWN rows
  2. B.Five groups, one per row
  3. C.Three runs: UP (2 rows), DOWN (2 rows), UP (1 row)
  4. D.Four groups, because the last UP is separate twice
Show answer

Answer: C. Three runs: UP (2 rows), DOWN (2 rows), UP (1 row)

The overall numbers are 1 to 5. Within UP the numbers are 1, 2, 3 and within DOWN 1, 2. Differences are UP 0, 0, DOWN 2, 2, last UP 5 - 3 = 2. Together with status, (UP,0), (DOWN,2) and (UP,2) are three runs of consecutive identical status.

51. PRECEDING with a descending order

What does this frame cover for the row dated 10 March, assuming one row per day?

SUM(sales) OVER (ORDER BY sale_date DESC
                 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
  1. A.10, 11 and 12 March
  2. B.8, 9 and 10 March
  3. C.9, 10 and 11 March
  4. D.Every date up to 10 March
Show answer

Answer: A. 10, 11 and 12 March

PRECEDING means earlier in the window's own order, not earlier in time. With DESC, the rows before 10 March in that order are 12 and 11 March. Mixing a DESC window with a "last 3 days" label is a common source of wrong numbers.

52. Users with a 3-day streak

After building islands with date - ROW_NUMBER() (one row per user per day), how do you find users who were active at least 3 days in a row?

  1. A.GROUP BY user and island key, keep COUNT(*) >= 3
  2. B.Keep rows where ROW_NUMBER() per user is >= 3
  3. C.Keep users with COUNT(DISTINCT date) >= 3
  4. D.Keep rows where LAG(date, 3) per user is not NULL
Show answer

Answer: A. GROUP BY user and island key, keep COUNT(*) >= 3

Each island key marks one run of consecutive days, so counting rows per island gives its length. ROW_NUMBER >= 3 and LAG(date, 3) only check that a user has at least three or four active days in total, not in a row. The same goes for COUNT(DISTINCT date).

53. One partition takes all the time

In Spark or BigQuery, a window partitioned by merchant_id is slow. One merchant has 40% of all rows. What is happening?

  1. A.Window functions always run on a single machine
  2. B.The big merchant’s rows are processed twice by mistake
  3. C.One worker handles that whole partition; others sit idle
  4. D.Partitioning by an id column disables the sort step
Show answer

Answer: C. One worker handles that whole partition; others sit idle

All rows of a window partition must be processed together on one worker. A skewed key gives that worker far more data, so the whole job waits on it and may spill to disk or fail. Options include partitioning on a finer key where the logic allows, or handling the heavy merchant separately.

54. What EXCLUDE TIES removes

In PostgreSQL, a frame ends with EXCLUDE TIES. Which rows does that take out of the frame?

  1. A.The current row together with all of its peers
  2. B.Other peers of the current row, keeping the row itself
  3. C.Only the current row, keeping all of its peers
  4. D.All rows with NULL in the ORDER BY column
Show answer

Answer: B. Other peers of the current row, keeping the row itself

Peers are rows with the same ORDER BY value. EXCLUDE TIES removes the other peers while keeping the current row. EXCLUDE GROUP removes the current row and its peers, and EXCLUDE CURRENT ROW removes only the current row.

55. Running total with tied dates

sales has (d, amt): (01-01, 10), (01-02, 20), (01-02, 30), (01-03, 40). What running total does the query show for the row (01-02, 20)?

SELECT d, amt, SUM(amt) OVER (ORDER BY d) AS running
FROM sales;
  1. A.30
  2. B.10
  3. C.60
  4. D.100
Show answer

Answer: C. 60

With ORDER BY and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes all peer rows with the same d, so both 01-02 rows get 10 + 20 + 30 = 60. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, plus a unique tiebreaker, for a true row-by-row total.

56. Which day does an event belong to

Events are stored with UTC timestamps and partitioned by UTC date. The business in Dubai (UTC+4) reports by local date. An order at 2026-10-14 22:30 UTC falls on which local business date?

  1. A.2026-10-14
  2. B.2026-10-15
  3. C.2026-10-13
  4. D.It depends on the warehouse region
Show answer

Answer: B. 2026-10-15

22:30 UTC plus 4 hours is 02:30 on 2026-10-15 in Dubai. A daily local report must therefore read two UTC partitions and convert time zones before grouping. Storing timestamps in UTC is right, but date boundaries for reporting must be applied in the business time zone.

57. GST totals off by a few rupees

The warehouse sums GST computed and rounded on each invoice line. Finance computes GST on each invoice total, then rounds. Over 1 lakh invoices the totals differ by ₹1,240, with no missing rows. What is the likely cause and fix?

  1. A.Rounding at different levels; use billing's rule
  2. B.Missing invoices; reload the whole month of data
  3. C.Duplicate lines; add a DISTINCT on the amount
  4. D.Time zones; convert all dates to IST
Show answer

Answer: A. Rounding at different levels; use billing's rule

Rounding each line to paise and then adding gives slightly different totals from rounding once per invoice, and small gaps add up across many invoices. Find the rule the billing system uses for the legal invoice and apply the same one, then reconcile exactly rather than with a loose tolerance.

58. A filter that breaks a left join

This query should give Day 7 retention for all January signups, but it returns 100%. Why?

SELECT COUNT(DISTINCT s.user_id) AS cohort,
       COUNT(DISTINCT a.user_id) AS retained
FROM signups s
LEFT JOIN activity a ON a.user_id = s.user_id
WHERE s.signup_month = '2026-01'
  AND a.activity_date = s.signup_date + 7;
  1. A.The WHERE test on a drops unmatched rows
  2. B.COUNT(DISTINCT) counts null values as users
  3. C.LEFT JOIN always returns 100% matches
  4. D.signup_month should be in GROUP BY
Show answer

Answer: A. The WHERE test on a drops unmatched rows

A condition on the right table in WHERE throws away rows where a is null, so non-returners vanish from the cohort count too. Move the date condition into the ON clause so the left join keeps everyone.

59. Retention above 100%?

A cohort retention query shows month-2 retention of 140% for a user cohort. Which bug is the most likely cause?

SELECT c.cohort_month, e.activity_month,
  COUNT(*) * 1.0 / MAX(c.cohort_size) AS retention
FROM events e
JOIN cohorts c ON c.user_id = e.user_id
GROUP BY c.cohort_month, e.activity_month;
  1. A.GROUP BY must include user_id
  2. B.The join should be a RIGHT JOIN instead
  3. C.COUNT(*) counts events, not distinct users
  4. D.MAX should be replaced by MIN
Show answer

Answer: C. COUNT(*) counts events, not distinct users

Each user can have many events in a month, so COUNT(*) counts events and can exceed the cohort size. Use COUNT(DISTINCT e.user_id) so each retained user counts once; user retention for a fixed cohort can never exceed 100%.

60. YoY growth query mixes companies

A financials table holds several companies over several years. This query computes year-on-year revenue growth. What is the bug?

SELECT company, fy, revenue,
       revenue * 1.0 / LAG(revenue) OVER (ORDER BY fy) - 1 AS yoy
FROM financials;
  1. A.LAG needs ORDER BY revenue
  2. B.LAG has no PARTITION BY company
  3. C.The 1.0 causes integer division
  4. D.Window functions cannot use division
Show answer

Answer: B. LAG has no PARTITION BY company

Without PARTITION BY company, LAG takes the previous row across all companies ordered by year, so growth compares different firms. Use LAG(revenue) OVER (PARTITION BY company ORDER BY fy) so each company is compared only with its own prior year.

Ready to test yourself?

Take a 15-question SQL MCQ test with a score at the end, or practise the full SQL question bank and earn points on the leaderboard.

Start the SQL testTake a mock interview