SQL · Guides · October 29, 2026 · 11 min read

SQL interview questions for data analysts: 20 with answers

Data analyst SQL interviews test two things: whether you understand how SQL evaluates a query (concept questions), and whether you can turn a business question into a correct query under time pressure (live tasks). Below are 20 questions of both kinds in the style analysts are actually asked, with short model answers. Concept answers are what you would say out loud; task answers are queries you would write.

The query tasks use the airline database from the Nomto SQL course: airports, aircrafts, seats, flights (33,121 departures in July–September 2026), bookings, tickets, ticket_flights (one row per passenger per flight segment, with amount in US dollars), boarding_passes, plus a loyalty programme (ticket_members). The data snapshot was taken on 11 August 2026 at 18:00. Every query is PostgreSQL syntax and every result shown is the real output; you can run them yourself in the Nomto SQL room.

A tip that applies to every task: before writing, say out loud what one row of the result means and what one row of each table means. Most wrong answers in interviews come from a mismatch between the two.

Concept questions

1. In what order does SQL evaluate a SELECT query?

FROM and joins, WHERE, GROUP BY, aggregates, HAVING, window functions, SELECT (including aliases), DISTINCT, ORDER BY, LIMIT. That order explains why you cannot use an aggregate in WHERE, why you cannot filter a window function in WHERE, and why ORDER BY can use a column alias but WHERE cannot.

2. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after aggregation. WHERE status = 'Arrived' decides which flights are counted; HAVING COUNT(*) > 1000 decides which airports are shown. A detailed walk-through is in GROUP BY vs HAVING.

3. Explain INNER, LEFT and FULL OUTER JOIN

INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every left row and fills the right side with NULL when there is no match. FULL JOIN keeps unmatched rows from both sides. A good answer adds the consequence: in our data, aircrafts JOIN flights returns 8 models because the Airbus A320-200 has no flights; aircrafts LEFT JOIN flights returns all 9. More in SQL joins explained.

4. COUNT(*), COUNT(column) and COUNT(DISTINCT column): what is the difference?

COUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts different non-NULL values.

SELECT COUNT(*) AS all_rows,
       COUNT(actual_departure) AS departed,
       COUNT(DISTINCT flight_no) AS flight_numbers
FROM flights;

33,121 departures, of which 16,773 have an actual departure time, flown under 710 flight numbers.

5. How do NULLs behave in comparisons?

Any comparison with NULL returns unknown, not true, so WHERE x = NULL matches nothing:

SELECT COUNT(*) AS flights FROM flights WHERE actual_departure = NULL;   -- 0
SELECT COUNT(*) AS flights FROM flights WHERE actual_departure IS NULL;  -- 16348

Use IS NULL / IS NOT NULL. Aggregates other than COUNT(*) ignore NULLs, and COALESCE(x, 0) replaces them where a zero is meant.

6. Why is NOT IN with a subquery dangerous?

If the subquery returns even one NULL, x NOT IN (...) can never be true, and the query returns no rows. In our data, aircrafts WHERE aircraft_code NOT IN (SELECT aircraft_code FROM flights) correctly returns 1 model. Put a single NULL into the subquery’s output and the same query returns 0. Use NOT EXISTS, which has no such trap.

7. UNION vs UNION ALL

UNION removes duplicates, UNION ALL keeps every row and is cheaper because it skips the deduplication.

SELECT COUNT(*) FROM (
  SELECT departure_airport AS airport FROM flights
  UNION
  SELECT arrival_airport FROM flights
) u;

This returns 104 (distinct airports). With UNION ALL it returns 66,242, two rows per flight. Use UNION ALL unless you need deduplication.

8. ROW_NUMBER, RANK and DENSE_RANK

All number rows by an ordering. On ties, ROW_NUMBER still gives unique numbers (1, 2, 3, 4), RANK repeats and skips (1, 2, 2, 4), DENSE_RANK repeats without skipping (1, 2, 2, 3). Use ROW_NUMBER for “exactly N rows” and dedup, DENSE_RANK for “the Nth highest value”. Examples with real ties are in SQL window functions.

9. What is a primary key, a foreign key and a composite key?

A primary key uniquely identifies a row (flights.flight_id). A foreign key references another table’s key (flights.aircraft_code → aircrafts.aircraft_code). A composite key uses several columns together: in seats, the seat number 12A repeats across models, so the key is the pair (aircraft_code, seat_no). In interviews, follow up with what it means for joins: joining on half of a composite key multiplies rows.

10. Why does a join sometimes inflate a SUM, and how do you fix it?

When a row joins to several rows, it is repeated, and its measures are summed once per repeat. Bookings made on 2 August total $4,563,873 in bookings; after joining tickets (several per booking) the same SUM(total_amount) gives $7,569,035. Fixes: aggregate the “many” side before joining, or sum the measure at its own grain. SUM(DISTINCT ...) is not a fix: it merges different bookings with equal amounts.

Query tasks

11. Find the second-highest aircraft range without window functions

SELECT MAX(range) AS second_longest
FROM aircrafts
WHERE range < (SELECT MAX(range) FROM aircrafts);

The subquery finds the maximum (11,100 km); the outer query takes the maximum of everything below it. If the interviewer asks for the Nth value in general, switch to DENSE_RANK (next question).

12. The second-highest segment price in each class

WITH p AS (
  SELECT DISTINCT fare_conditions, amount FROM ticket_flights
), r AS (
  SELECT fare_conditions, amount,
         DENSE_RANK() OVER (PARTITION BY fare_conditions ORDER BY amount DESC) AS dr
  FROM p
)
SELECT fare_conditions, amount AS second_highest
FROM r
WHERE dr = 2
ORDER BY fare_conditions;

The top prices are $2,033, $476 and $745. DENSE_RANK is the right choice: many segments share the top price, and ROW_NUMBER would just return another copy of it as “second”. Deduplicating prices first keeps the window small.

13. Median price per class

SELECT fare_conditions,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
       ROUND(AVG(amount), 2) AS mean
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY fare_conditions;

In every class the mean is well above the median: a minority of long, expensive routes pulls the average up. Saying this unprompted is what turns a correct query into a good answer. PERCENTILE_CONT interpolates between the two middle values; PERCENTILE_DISC returns an actual value from the data.

14. Find duplicates

“Find bookings where the same passenger name appears on more than one ticket.”

SELECT passenger_name, book_ref, COUNT(*) AS tickets
FROM tickets
GROUP BY passenger_name, book_ref
HAVING COUNT(*) > 1
ORDER BY tickets DESC, book_ref
LIMIT 3;

There are 34 such pairs. The follow-up worth raising: these are not necessarily errors. Two people in one booking can share a common name; the passenger ID (unique on every ticket here) is the better identity check. Defining what counts as a duplicate is part of the answer.

15. Top N per group

“The two most frequent destinations from each Moscow airport.”

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY departure_airport
                            ORDER BY COUNT(*) DESC, arrival_airport) AS rn
  FROM flights
  WHERE departure_airport IN ('SVO', 'DME', 'VKO')
  GROUP BY departure_airport, arrival_airport
)
SELECT departure_airport, arrival_airport, flights, rn
FROM r
WHERE rn <= 2
ORDER BY departure_airport, rn;

Mention the tie at Vnukovo: BZK and LED both have 183 flights, and only the alphabetical tie-breaker decides the order. If both tied rows should count as first, use RANK.

16. Rows with no match (anti join)

“Which aircraft models have never flown?”

SELECT a.aircraft_code, a.model
FROM aircrafts a
WHERE NOT EXISTS (
  SELECT 1 FROM flights f WHERE f.aircraft_code = a.aircraft_code
);

The equivalent LEFT JOIN ... WHERE f.flight_id IS NULL is also accepted. Prefer either to NOT IN (question 6).

17. Pivot with conditional aggregation

“For each Moscow airport, show arrived, cancelled and scheduled flights as columns.”

SELECT departure_airport,
       COUNT(*) FILTER (WHERE status = 'Arrived')   AS arrived,
       COUNT(*) FILTER (WHERE status = 'Cancelled') AS cancelled,
       COUNT(*) FILTER (WHERE status = 'Scheduled') AS scheduled
FROM flights
WHERE departure_airport IN ('SVO', 'DME', 'VKO')
GROUP BY departure_airport
ORDER BY departure_airport;

If the interviewer’s database has no FILTER, use SUM(CASE WHEN status = 'Arrived' THEN 1 ELSE 0 END).

18. Running total and day-over-day change

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       SUM(bookings) OVER (ORDER BY day) AS running_total,
       LAG(bookings) OVER (ORDER BY day) AS prev_day,
       bookings - LAG(bookings) OVER (ORDER BY day) AS change
FROM daily
ORDER BY day
LIMIT 4;

Aggregate to days first, then apply windows on the daily rows. A good follow-up remark: if a day had zero bookings it would be missing from daily, and LAG would compare with two days earlier; a date series fixes that.

19. Percent of total

“What share of all flights does each status account for?”

SELECT status, COUNT(*) AS flights,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM flights
GROUP BY status
ORDER BY flights DESC
LIMIT 4;

SUM(COUNT(*)) OVER () is the grand total placed next to each group. The 100.0 avoids integer division.

20. Repeat behaviour within 30 days

“What share of loyalty members made another booking within 30 days of their first one?”

WITH mb AS (
  SELECT DISTINCT tm.member_id, b.book_ref, b.book_date
  FROM ticket_members tm
  JOIN tickets t  ON t.ticket_no = tm.ticket_no
  JOIN bookings b ON b.book_ref = t.book_ref
), firsts AS (
  SELECT member_id, MIN(book_date) AS first_booking
  FROM mb
  GROUP BY member_id
), flags AS (
  SELECT f.member_id,
         MAX(CASE WHEN mb.book_date > f.first_booking
                   AND mb.book_date <= f.first_booking + INTERVAL '30 days'
                  THEN 1 ELSE 0 END) AS repeat_30d
  FROM firsts f
  JOIN mb ON mb.member_id = f.member_id
  WHERE f.first_booking <= TIMESTAMP '2026-08-11 18:00:00' - INTERVAL '30 days'
  GROUP BY f.member_id
)
SELECT COUNT(*) AS members, SUM(repeat_30d) AS repeat_30d,
       ROUND(100.0 * SUM(repeat_30d) / COUNT(*), 1) AS pct
FROM flags;

The three points interviewers look for:

1. Grain. A member can hold several tickets in one booking, so DISTINCT reduces the data to one row per member per booking before anything is counted.
2. Maturity. Only members whose first booking was at least 30 days before the snapshot are included. A member who first booked on 5 August has not had 30 days to come back yet; counting them as “did not return” would understate the rate.
3. Definition. “First booking” here means first in the data, which starts on 17 June 2026. Say so; it is an assumption, not a fact about the customer.

How to answer in the interview

• Restate the question as a result table: “one row per airport, columns X and Y”. It catches misunderstandings before you write code.
• Name the grain of each table you join, and say where fan-out could happen.
• Handle ties, NULLs and incomplete periods explicitly. They are the edge cases interviewers plant on purpose.
• Sanity-check the number: compare with a total you know, or count rows before and after a join.
• Talk while you write. A partly correct query with clear reasoning usually scores better than a silent correct one.

For more hands-on tasks graded from easy to hard, see SQL practice problems with answers.

FAQ

What SQL topics are asked in data analyst interviews?

Most commonly: GROUP BY with HAVING, joins (including LEFT and anti joins), handling NULL, subqueries and CTEs, window functions (ranking, top N per group, running totals, LAG/LEAD), percent of total and period-over-period change. Senior roles add retention, funnels and data quality checks.

Which SQL dialect should I use in an interview?

Ask, but standard SQL close to PostgreSQL is a safe default. If you use a dialect-specific feature such as FILTER or QUALIFY, mention the portable alternative.

How do I find the Nth highest value in SQL?

Use DENSE_RANK() OVER (ORDER BY value DESC) in a subquery and filter = N; add PARTITION BY for “per group”. For the second-highest only, MAX(value) WHERE value < (SELECT MAX(value) ...) works without window functions.

How should I prepare for live SQL tasks?

Practise on a realistic multi-table database rather than single-table puzzles, time yourself, and always check your result against an independent number. Every task above can be run and checked in the Nomto SQL room.

Also in Português (Brasil), Tiếng Việt, Español, 简体中文