How to approach SQL interview questions

An SQL interview question is rarely hard because of the SQL. It is hard because the question leaves out what the answer depends on: which rows count, what one row of the result stands for, what a NULL means, how ties are broken and which engine runs the query. A good approach is a short routine you follow every time. Restate the question as one sentence that names the grain of the result (“one row per city, with its share of late deliveries”), ask about the details that change the answer, sketch a few sample rows and the output you expect, build the query in common table expression (CTE) steps that you run one at a time, then test the edge cases aloud and say what would make the query fast. Each step is a named query in a WITH clause. This lesson walks the routine through one original problem, on which three plausible first drafts return three different answers.

Every SQLite example on this page runs on your own device: the page loads SQLite 3.49.1 as WebAssembly, packaged by sql.js 1.14.2. The section on engines adds the same queries recorded on PostgreSQL 18.6 and MySQL 8.4.11, where five of them answer differently.

The routine in five steps

The first three steps take a couple of minutes and need no SQL at all, yet they decide what the query has to do. Restating the question in one sentence makes the grain explicit, and the grain decides the GROUP BY. Asking about the data turns vague words such as “last week” and “late” into conditions you can write. Sketching sample rows with the output you expect gives you something to test against later, and it often exposes a misunderstanding before any code exists, because the interviewer can correct a table faster than a query.

Steps 4 and 5 turn those decisions into code you can check piece by piece. Each CTE is one step with a name, so when a number looks wrong you run the steps one by one and find the step where the rows stop matching your sketch. The last step is where you show the work: you run the edge cases, compare the result with the sketch and mention the index you would add. When a test disagrees with the sketch, go back to the questions, because the disagreement usually means that something was not asked.

Questions that change the answer

Ask the questions whose answers change the rows or the numbers, and skip the rest. Six cover most problems:

Offer choices rather than open questions. “Should a cancelled order count as on time, as late, or not at all?” gets a quick decision; “What about cancelled orders?” starts a discussion. When the interviewer says “you decide”, choose, say the choice aloud and write it as a comment in the query.

A worked problem: late deliveries per city

The interviewer says: “For each city, what share of last week’s deliveries were late? Show the worst cities first.” The table deliveries has one row per order, with the city, the promised time, the delivery time and a status. The questions settle six details:

• The grain: one row per city.
• Which rows count: delivered orders only. A cancelled order or one still on the way is not a delivery, so it is neither late nor on time.
• Late: delivered after the promised time. An order delivered at exactly the promised minute is on time.
• Last week: by promised time, from Monday 2026-03-02 00:00 UTC up to, but not including, the next Monday 2026-03-09 00:00. A period like this, with its first instant inside and its closing boundary outside, is a half-open interval.
• Small cities: a city needs at least three deliveries in the week, or its share means little.
• The output: the share as a percentage with one decimal, the highest first, ties by city name.

The sample has fifteen orders in four cities, each chosen to test one of those answers. Worked out by hand, the expected result has three rows:

Pune’s cancelled order is not counted. Kochi’s order promised at 2026-03-09 00:00 belongs to the next week, and Indore’s order promised on the Sunday before belongs to the week before. Jaipur has only two deliveries, so it is left out. Indore stays in with 0.0: a city with no late deliveries is part of the answer, not a missing row.

Build it in steps you can run

-- Fifteen deliveries in four cities. Times are UTC, written 'YYYY-MM-DD HH:MM'.
-- delivered_at is NULL while an order is on the way or after it was cancelled.
CREATE TABLE deliveries (
  id           INTEGER PRIMARY KEY,
  city         TEXT NOT NULL,
  promised_at  TEXT NOT NULL,
  delivered_at TEXT,
  status       TEXT NOT NULL CHECK (status IN ('delivered', 'on_the_way', 'cancelled'))
);
INSERT INTO deliveries VALUES
  ( 1, 'Pune',   '2026-03-02 10:00', '2026-03-02 09:50', 'delivered'),
  ( 2, 'Pune',   '2026-03-03 13:00', '2026-03-03 13:25', 'delivered'),
  ( 3, 'Pune',   '2026-03-05 19:30', '2026-03-05 19:30', 'delivered'),
  ( 4, 'Pune',   '2026-03-07 20:00', '2026-03-07 20:40', 'delivered'),
  ( 5, 'Pune',   '2026-03-08 21:00', NULL,               'cancelled'),
  ( 6, 'Kochi',  '2026-03-02 12:00', '2026-03-02 12:05', 'delivered'),
  ( 7, 'Kochi',  '2026-03-04 18:00', '2026-03-04 17:40', 'delivered'),
  ( 8, 'Kochi',  '2026-03-06 09:00', '2026-03-06 08:55', 'delivered'),
  ( 9, 'Kochi',  '2026-03-09 00:00', '2026-03-09 00:20', 'delivered'),
  (10, 'Jaipur', '2026-03-03 11:00', '2026-03-03 11:30', 'delivered'),
  (11, 'Jaipur', '2026-03-04 11:00', '2026-03-04 10:45', 'delivered'),
  (12, 'Indore', '2026-03-01 23:30', '2026-03-02 00:10', 'delivered'),
  (13, 'Indore', '2026-03-02 08:00', '2026-03-02 07:45', 'delivered'),
  (14, 'Indore', '2026-03-05 08:00', '2026-03-05 07:58', 'delivered'),
  (15, 'Indore', '2026-03-08 23:00', '2026-03-08 22:30', 'delivered');

-- Step 1: the deliveries that count, promised in the week that starts on Monday 2026-03-02
-- (a half-open interval: from Monday 00:00 up to, but not including, the next Monday 00:00).
WITH due_last_week AS (
  SELECT id, city, promised_at, delivered_at
  FROM deliveries
  WHERE status = 'delivered'
    AND promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
)
SELECT * FROM due_last_week ORDER BY city, promised_at;

-- Step 2: one row per city, the grain the question asked for.
WITH due_last_week AS (
  SELECT id, city, promised_at, delivered_at
  FROM deliveries
  WHERE status = 'delivered'
    AND promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
),
per_city AS (
  SELECT city,
         count(*) AS delivered,
         sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late
  FROM due_last_week
  GROUP BY city
)
SELECT * FROM per_city ORDER BY city;

-- Step 3: the answer. 100.0 makes the division exact; cities need at least three deliveries.
WITH due_last_week AS (
  SELECT id, city, promised_at, delivered_at
  FROM deliveries
  WHERE status = 'delivered'
    AND promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
),
per_city AS (
  SELECT city,
         count(*) AS delivered,
         sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late
  FROM due_last_week
  GROUP BY city
)
SELECT city, delivered, late, round(100.0 * late / delivered, 1) AS late_pct
FROM per_city
WHERE delivered >= 3
ORDER BY late_pct DESC, city;

┌────┬────────┬──────────────────┬──────────────────┐
│ id │  city  │   promised_at    │   delivered_at   │
├────┼────────┼──────────────────┼──────────────────┤
│ 13 │ Indore │ 2026-03-02 08:00 │ 2026-03-02 07:45 │
│ 14 │ Indore │ 2026-03-05 08:00 │ 2026-03-05 07:58 │
│ 15 │ Indore │ 2026-03-08 23:00 │ 2026-03-08 22:30 │
│ 10 │ Jaipur │ 2026-03-03 11:00 │ 2026-03-03 11:30 │
│ 11 │ Jaipur │ 2026-03-04 11:00 │ 2026-03-04 10:45 │
│ 6  │ Kochi  │ 2026-03-02 12:00 │ 2026-03-02 12:05 │
│ 7  │ Kochi  │ 2026-03-04 18:00 │ 2026-03-04 17:40 │
│ 8  │ Kochi  │ 2026-03-06 09:00 │ 2026-03-06 08:55 │
│ 1  │ Pune   │ 2026-03-02 10:00 │ 2026-03-02 09:50 │
│ 2  │ Pune   │ 2026-03-03 13:00 │ 2026-03-03 13:25 │
│ 3  │ Pune   │ 2026-03-05 19:30 │ 2026-03-05 19:30 │
│ 4  │ Pune   │ 2026-03-07 20:00 │ 2026-03-07 20:40 │
└────┴────────┴──────────────────┴──────────────────┘
┌────────┬───────────┬──────┐
│  city  │ delivered │ late │
├────────┼───────────┼──────┤
│ Indore │ 3         │ 0    │
│ Jaipur │ 2         │ 1    │
│ Kochi  │ 3         │ 1    │
│ Pune   │ 4         │ 2    │
└────────┴───────────┴──────┘
┌────────┬───────────┬──────┬──────────┐
│  city  │ delivered │ late │ late_pct │
├────────┼───────────┼──────┼──────────┤
│ Pune   │ 4         │ 2    │ 50.0     │
│ Kochi  │ 3         │ 1    │ 33.3     │
│ Indore │ 3         │ 0    │ 0.0      │
└────────┴───────────┴──────┴──────────┘

The first result is step 1 alone: twelve deliveries promised in the week, with the cancelled order and the two orders outside the week gone. The second adds step 2, one row per city with its deliveries and late deliveries, and it still shows Jaipur with two. The third is the whole query, and it matches the sketch row for row. Writing 100.0 * late makes the division exact; the drafts below show what happens without it.

Each CTE reads the one before it, so the query reads top to bottom as the steps of the routine: choose the rows, reduce them to the grain, compute the measure. In an interview, run each step as soon as it is written, and say what you expect to see first. The final WHERE delivered >= 3 runs on the grouped rows, so it could also be a HAVING clause in a query without CTEs; as a filter on a named step, it reads as plainly as the requirement it implements.

Three first drafts, three different answers

-- The same fifteen deliveries as the worked problem.
CREATE TABLE deliveries (
  id           INTEGER PRIMARY KEY,
  city         TEXT NOT NULL,
  promised_at  TEXT NOT NULL,
  delivered_at TEXT,
  status       TEXT NOT NULL CHECK (status IN ('delivered', 'on_the_way', 'cancelled'))
);
INSERT INTO deliveries VALUES
  ( 1, 'Pune',   '2026-03-02 10:00', '2026-03-02 09:50', 'delivered'),
  ( 2, 'Pune',   '2026-03-03 13:00', '2026-03-03 13:25', 'delivered'),
  ( 3, 'Pune',   '2026-03-05 19:30', '2026-03-05 19:30', 'delivered'),
  ( 4, 'Pune',   '2026-03-07 20:00', '2026-03-07 20:40', 'delivered'),
  ( 5, 'Pune',   '2026-03-08 21:00', NULL,               'cancelled'),
  ( 6, 'Kochi',  '2026-03-02 12:00', '2026-03-02 12:05', 'delivered'),
  ( 7, 'Kochi',  '2026-03-04 18:00', '2026-03-04 17:40', 'delivered'),
  ( 8, 'Kochi',  '2026-03-06 09:00', '2026-03-06 08:55', 'delivered'),
  ( 9, 'Kochi',  '2026-03-09 00:00', '2026-03-09 00:20', 'delivered'),
  (10, 'Jaipur', '2026-03-03 11:00', '2026-03-03 11:30', 'delivered'),
  (11, 'Jaipur', '2026-03-04 11:00', '2026-03-04 10:45', 'delivered'),
  (12, 'Indore', '2026-03-01 23:30', '2026-03-02 00:10', 'delivered'),
  (13, 'Indore', '2026-03-02 08:00', '2026-03-02 07:45', 'delivered'),
  (14, 'Indore', '2026-03-05 08:00', '2026-03-05 07:58', 'delivered'),
  (15, 'Indore', '2026-03-08 23:00', '2026-03-08 22:30', 'delivered');

-- Draft A counts every order of the week, the cancelled one too.
SELECT city, count(*) AS orders,
       sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late,
       round(100.0 * sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) / count(*), 1) AS late_pct
FROM deliveries
WHERE promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
GROUP BY city
HAVING count(*) >= 3
ORDER BY late_pct DESC, city;

-- Draft B divides two integers, so the share is cut to a whole number before round() sees it.
SELECT city, count(*) AS delivered,
       sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late,
       round(100 * sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) / count(*), 1) AS late_pct
FROM deliveries
WHERE status = 'delivered'
  AND promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
GROUP BY city
HAVING count(*) >= 3
ORDER BY late_pct DESC, city;

-- Draft C uses BETWEEN, which includes both ends, so an order due at the next Monday 00:00 joins the week.
SELECT city, count(*) AS delivered,
       sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late,
       round(100.0 * sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) / count(*), 1) AS late_pct
FROM deliveries
WHERE status = 'delivered'
  AND promised_at BETWEEN '2026-03-02 00:00' AND '2026-03-09 00:00'
GROUP BY city
HAVING count(*) >= 3
ORDER BY late_pct DESC, city;

┌────────┬────────┬──────┬──────────┐
│  city  │ orders │ late │ late_pct │
├────────┼────────┼──────┼──────────┤
│ Pune   │ 5      │ 2    │ 40.0     │
│ Kochi  │ 3      │ 1    │ 33.3     │
│ Indore │ 3      │ 0    │ 0.0      │
└────────┴────────┴──────┴──────────┘
┌────────┬───────────┬──────┬──────────┐
│  city  │ delivered │ late │ late_pct │
├────────┼───────────┼──────┼──────────┤
│ Pune   │ 4         │ 2    │ 50.0     │
│ Kochi  │ 3         │ 1    │ 33.0     │
│ Indore │ 3         │ 0    │ 0.0      │
└────────┴───────────┴──────┴──────────┘
┌────────┬───────────┬──────┬──────────┐
│  city  │ delivered │ late │ late_pct │
├────────┼───────────┼──────┼──────────┤
│ Kochi  │ 4         │ 2    │ 50.0     │
│ Pune   │ 4         │ 2    │ 50.0     │
│ Indore │ 3         │ 0    │ 0.0      │
└────────┴───────────┴──────┴──────────┘

Every draft runs without an error, and every one returns a plausible table. Draft A counts every order of the week, so Pune’s cancelled order joins the denominator and its share falls from 50.0 to 40.0. Draft B writes 100 * late / delivered: both operands are integers, SQLite and PostgreSQL divide them as integers, and Kochi’s 33.3 becomes 33.0 before round() ever sees the fraction. Draft C uses BETWEEN, which includes both of its ends, so the order promised at the next Monday 00:00 joins the week. Kochi then has two late deliveries out of four, ties Pune at 50.0 and moves to the top.

None of these mistakes shows up as an error, and two of them change only one number. That is why the sketch comes before the query: a table worked out by hand is the only thing in the room that can tell you a draft is wrong.

Test the answer aloud, and check it against the sketch

When the sketch is written down as rows, the database can compare it with the query for you. EXCEPT returns the rows of its first query that are not in its second, so running it in both directions returns every row that differs:

-- The same fifteen deliveries as the worked problem.
CREATE TABLE deliveries (
  id           INTEGER PRIMARY KEY,
  city         TEXT NOT NULL,
  promised_at  TEXT NOT NULL,
  delivered_at TEXT,
  status       TEXT NOT NULL CHECK (status IN ('delivered', 'on_the_way', 'cancelled'))
);
INSERT INTO deliveries VALUES
  ( 1, 'Pune',   '2026-03-02 10:00', '2026-03-02 09:50', 'delivered'),
  ( 2, 'Pune',   '2026-03-03 13:00', '2026-03-03 13:25', 'delivered'),
  ( 3, 'Pune',   '2026-03-05 19:30', '2026-03-05 19:30', 'delivered'),
  ( 4, 'Pune',   '2026-03-07 20:00', '2026-03-07 20:40', 'delivered'),
  ( 5, 'Pune',   '2026-03-08 21:00', NULL,               'cancelled'),
  ( 6, 'Kochi',  '2026-03-02 12:00', '2026-03-02 12:05', 'delivered'),
  ( 7, 'Kochi',  '2026-03-04 18:00', '2026-03-04 17:40', 'delivered'),
  ( 8, 'Kochi',  '2026-03-06 09:00', '2026-03-06 08:55', 'delivered'),
  ( 9, 'Kochi',  '2026-03-09 00:00', '2026-03-09 00:20', 'delivered'),
  (10, 'Jaipur', '2026-03-03 11:00', '2026-03-03 11:30', 'delivered'),
  (11, 'Jaipur', '2026-03-04 11:00', '2026-03-04 10:45', 'delivered'),
  (12, 'Indore', '2026-03-01 23:30', '2026-03-02 00:10', 'delivered'),
  (13, 'Indore', '2026-03-02 08:00', '2026-03-02 07:45', 'delivered'),
  (14, 'Indore', '2026-03-05 08:00', '2026-03-05 07:58', 'delivered'),
  (15, 'Indore', '2026-03-08 23:00', '2026-03-08 22:30', 'delivered');

-- The rows worked out by hand from the sample, before any query was run.
CREATE TABLE expected (city TEXT, delivered INTEGER, late INTEGER, late_pct REAL);
INSERT INTO expected VALUES ('Pune', 4, 2, 50.0), ('Kochi', 3, 1, 33.3), ('Indore', 3, 0, 0.0);

-- The final query of the worked problem, kept as a view so that it can be compared.
CREATE VIEW answer AS
WITH due_last_week AS (
  SELECT city, promised_at, delivered_at
  FROM deliveries
  WHERE status = 'delivered'
    AND promised_at >= '2026-03-02 00:00' AND promised_at < '2026-03-09 00:00'
),
per_city AS (
  SELECT city, count(*) AS delivered,
         sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late
  FROM due_last_week
  GROUP BY city
)
SELECT city, delivered, late, round(100.0 * late / delivered, 1) AS late_pct
FROM per_city
WHERE delivered >= 3;

-- Draft C of the previous example, kept the same way.
CREATE VIEW draft_c AS
SELECT city, count(*) AS delivered,
       sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) AS late,
       round(100.0 * sum(CASE WHEN delivered_at > promised_at THEN 1 ELSE 0 END) / count(*), 1) AS late_pct
FROM deliveries
WHERE status = 'delivered'
  AND promised_at BETWEEN '2026-03-02 00:00' AND '2026-03-09 00:00'
GROUP BY city
HAVING count(*) >= 3;

-- Rows in one result but not in the other, both ways round: 0 means they hold the same rows.
SELECT 'answer' AS query, count(*) AS differences
FROM (SELECT * FROM (SELECT * FROM expected EXCEPT SELECT * FROM answer)
      UNION ALL
      SELECT * FROM (SELECT * FROM answer EXCEPT SELECT * FROM expected))
UNION ALL
SELECT 'draft_c', count(*)
FROM (SELECT * FROM (SELECT * FROM expected EXCEPT SELECT * FROM draft_c)
      UNION ALL
      SELECT * FROM (SELECT * FROM draft_c EXCEPT SELECT * FROM expected));

-- What differs in draft C, row by row.
SELECT 'missing' AS problem, * FROM (SELECT * FROM expected EXCEPT SELECT * FROM draft_c)
UNION ALL
SELECT 'unexpected', * FROM (SELECT * FROM draft_c EXCEPT SELECT * FROM expected)
ORDER BY problem, city;

┌─────────┬─────────────┐
│  query  │ differences │
├─────────┼─────────────┤
│ answer  │ 0           │
│ draft_c │ 2           │
└─────────┴─────────────┘
┌────────────┬───────┬───────────┬──────┬──────────┐
│  problem   │ city  │ delivered │ late │ late_pct │
├────────────┼───────┼───────────┼──────┼──────────┤
│ missing    │ Kochi │ 3         │ 1    │ 33.3     │
│ unexpected │ Kochi │ 4         │ 2    │ 50.0     │
└────────────┴───────┴───────────┴──────┴──────────┘

The final query differs from the sketch in 0 rows, and draft C in 2. The second result says which: the sketch’s Kochi row is missing, and an unexpected Kochi row with four deliveries took its place. The check compares sets of rows, so it ignores their order and treats two copies of a row as one. When the order matters, compare row_number() OVER (ORDER BY …) as well; when duplicates matter, compare count(*) of both sides too.

In a live interview there is rarely time to type a table of expected rows, so say the edge cases aloud and run them instead: a city with no late delivery (it stays, with 0.0), an order delivered at exactly the promised time, an order on the boundary of the week, a NULL in every column the query reads, and a repeated row. In a take-home task, keep the expected rows and the check in the file you hand in.

Ask which engine: five answers that change

The same five short queries run here in SQLite, then in PostgreSQL and in MySQL:

-- Five short queries whose answers depend on the engine. The same text runs in
-- PostgreSQL and MySQL in the next two examples.
CREATE TABLE scores (name TEXT, city TEXT, points INTEGER);
INSERT INTO scores VALUES ('Asha', 'Pune', 7), ('Bilal', 'Pune', NULL), ('Chitra', 'Kochi', 2);

-- 1. Dividing two integers.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS dec_div;

-- 2. Where a NULL sorts.
SELECT name, points FROM scores ORDER BY points DESC;

-- 3. Joining strings with ||.
SELECT 'Pune' || '-' || 'Kochi' AS joined;

-- 4. Comparing text that differs only in case.
SELECT 'Pune' = 'pune' AS same_text;

-- 5. A column that is neither grouped nor inside an aggregate.
SELECT city, name, max(points) AS best FROM scores GROUP BY city ORDER BY city;

┌─────────┬─────────┐
│ int_div │ dec_div │
├─────────┼─────────┤
│ 3       │ 3.5     │
└─────────┴─────────┘
┌────────┬────────┐
│  name  │ points │
├────────┼────────┤
│ Asha   │ 7      │
│ Chitra │ 2      │
│ Bilal  │        │
└────────┴────────┘
┌────────────┐
│   joined   │
├────────────┤
│ Pune-Kochi │
└────────────┘
┌───────────┐
│ same_text │
├───────────┤
│ 0         │
└───────────┘
┌───────┬────────┬──────┐
│ city  │  name  │ best │
├───────┼────────┼──────┤
│ Kochi │ Chitra │ 2    │
│ Pune  │ Asha   │ 7    │
└───────┴────────┴──────┘

"""The five queries of engine_traps.sql, run in PostgreSQL 18.6."""

from pg_server import psql

psql(r"""
CREATE TABLE scores (name TEXT, city TEXT, points INTEGER);
INSERT INTO scores VALUES ('Asha', 'Pune', 7), ('Bilal', 'Pune', NULL), ('Chitra', 'Kochi', 2);

-- 1. Dividing two integers.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS dec_div;

-- 2. Where a NULL sorts.
SELECT name, points FROM scores ORDER BY points DESC;

-- 3. Joining strings with ||.
SELECT 'Pune' || '-' || 'Kochi' AS joined;

-- 4. Comparing text that differs only in case.
SELECT 'Pune' = 'pune' AS same_text;

-- 5. A column that is neither grouped nor inside an aggregate.
SELECT city, name, max(points) AS best FROM scores GROUP BY city ORDER BY city;
""")

 int_div |      dec_div       
---------+--------------------
       3 | 3.5000000000000000
(1 row)

  name  | points 
--------+--------
 Bilal  |       
 Asha   |      7
 Chitra |      2
(3 rows)

   joined   
------------
 Pune-Kochi
(1 row)

 same_text 
-----------
 f
(1 row)

ERROR:  column "scores.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT city, name, max(points) AS best FROM scores GROUP BY ...
                     ^

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port), sends the SQL to psql, then stops the server and deletes the folder. Every
run therefore starts from an empty database and prints the same text.

The settings keep the output the same on every run: no autovacuum (statistics change only when the SQL runs ANALYZE
or VACUUM), statistics taken from every row instead of a random sample (default_statistics_target = 10000 samples up
to 3,000,000 rows), no parallel workers, no JIT compilation, and WAL buffers large enough never to fill up during a
run. The cluster uses the C locale, UTF-8 and UTC.

PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6). The output is psql's own,
except that the "psql:<stdin>:<line>:" in front of each message is left out.
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys

BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

# Short names: a Unix socket path may not be longer than about 100 characters.
_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "pg")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "s")
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_running = False


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _running
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_sock)
    _quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    options = (
        f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC -c autovacuum=off"
        " -c default_statistics_target=10000 -c max_parallel_workers_per_gather=0 -c jit=off -c wal_buffers=64MB"
    )
    _quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _running = True
    atexit.register(_stop)


def _stop() -> None:
    global _running
    if _running:
        subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _running = False
    shutil.rmtree(_base, ignore_errors=True)


def psql(sql: str) -> None:
    """Sends a block of SQL to psql and prints what it printed, errors included, in order."""
    if not _running:
        _start()
    run = subprocess.run(
        [os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(WHERE.sub("", run.stdout))
    sys.stdout.flush()

"""The five queries of engine_traps.sql, run in MySQL 8.4.11."""

from mysql_server import mysql

mysql(r"""
CREATE TABLE scores (name TEXT, city TEXT, points INTEGER);
INSERT INTO scores VALUES ('Asha', 'Pune', 7), ('Bilal', 'Pune', NULL), ('Chitra', 'Kochi', 2);

-- 1. Dividing two integers.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS dec_div;

-- 2. Where a NULL sorts.
SELECT name, points FROM scores ORDER BY points DESC;

-- 3. Joining strings with ||.
SELECT 'Pune' || '-' || 'Kochi' AS joined;

-- 4. Comparing text that differs only in case.
SELECT 'Pune' = 'pune' AS same_text;

-- 5. A column that is neither grouped nor inside an aggregate.
SELECT city, name, max(points) AS best FROM scores GROUP BY city ORDER BY city;
""")

+---------+---------+
| int_div | dec_div |
+---------+---------+
|  3.5000 |  3.5000 |
+---------+---------+
+--------+--------+
| name   | points |
+--------+--------+
| Asha   |      7 |
| Chitra |      2 |
| Bilal  |   NULL |
+--------+--------+
+--------+
| joined |
+--------+
|      0 |
+--------+
+-----------+
| same_text |
+-----------+
|         1 |
+-----------+
ERROR 1055 (42000) at line 17: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.scores.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

"""Runs SQL on a private, throwaway MySQL 8.4.11 server and prints what the mysql client printed.

Each run makes a new data folder in its own temporary folder (mysqld --initialize-insecure), starts a server that
listens only on a Unix socket in that folder (--skip-networking: no TCP port), creates the database "shop", sends
the SQL to the mysql client, then shuts the server down and deletes the folder. Every run starts from an empty
database.

Two settings keep the plans the same on every run: InnoDB recomputes statistics only when the SQL runs ANALYZE TABLE
(innodb_stats_auto_recalc=OFF), and ANALYZE TABLE reads every page instead of a random sample of 20
(innodb_stats_persistent_sample_pages). EXPLAIN ANALYZE prints how long each step took, which differs from run to
run, so this file prints "actual time=…" in its place; everything else is the client's own output.

MySQL 8.4.11 comes from the folder where mise installs it (mise install mysql@8.4.11).
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys
import time

HOME = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "mysql", "8.4.11")
BIN = os.path.join(HOME, "bin")
TIMES = re.compile(r"actual time=[0-9.e+-]+\.\.[0-9.e+-]+")

_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "my")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "m.sock")
_server = None


def _client(*args: str, sql: str = "") -> subprocess.CompletedProcess:
    return subprocess.run(
        [os.path.join(BIN, "mysql"), "--no-defaults", f"--socket={_sock}", "-uroot", *args],
        input=sql, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )


def _start() -> None:
    global _server
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    server = [
        os.path.join(BIN, "mysqld"), "--no-defaults", f"--basedir={HOME}", f"--datadir={_data}",
        f"--socket={_sock}", "--skip-networking", "--mysqlx=OFF", f"--pid-file={os.path.join(_base, 'pid')}",
        f"--log-error={os.path.join(_base, 'error.log')}", "--skip-log-bin", "--performance-schema=OFF",
        "--innodb-stats-auto-recalc=OFF", "--innodb-stats-persistent-sample-pages=100000",
        "--default-time-zone=+00:00",
    ]
    subprocess.run(server[:4] + ["--initialize-insecure", f"--log-error={os.path.join(_base, 'init.log')}"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    _server = subprocess.Popen(server, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    atexit.register(_stop)
    for _ in range(600):
        if _client("-e", "CREATE DATABASE shop").returncode == 0:
            return
        time.sleep(0.1)
    raise RuntimeError("the MySQL server did not start")


def _stop() -> None:
    global _server
    if _server is not None:
        subprocess.run([os.path.join(BIN, "mysqladmin"), "--no-defaults", f"--socket={_sock}", "-uroot", "shutdown"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        try:
            _server.wait(timeout=60)
        except subprocess.TimeoutExpired:
            _server.kill()
        _server = None
    shutil.rmtree(_base, ignore_errors=True)


def mysql(sql: str) -> None:
    """Sends a block of SQL to the mysql client (tables as boxes, \\G for one row per block) and prints its output."""
    if _server is None:
        _start()
    run = _client("--table", "--force", "--comments", "shop", sql=sql.strip() + "\n")
    sys.stdout.write(TIMES.sub("actual time=…", run.stdout))
    sys.stdout.flush()

Each difference is documented behaviour. SQLite and PostgreSQL truncate the division of two integers towards zero, while MySQL’s / returns a decimal with, by default, four more decimal places than its first operand. PostgreSQL sorts NULL as larger than any value, so it comes first in descending order; SQLite and MySQL sort it as the smallest value. In MySQL || is a deprecated spelling of OR unless the PIPES_AS_CONCAT mode is on, so the two strings become numbers and the result is 0. MySQL’s default collation for utf8mb4 is case-insensitive, the ci in its name, while SQLite compares bytes and PostgreSQL’s default collation is deterministic. And a column that is neither grouped nor aggregated is an error in PostgreSQL and, with the default ONLY_FULL_GROUP_BY mode, in MySQL, while SQLite takes its value from one row of the group.

Ask which engine and version an assessment uses before you start, and when you cannot ask, write the forms that agree everywhere: 100.0 * before a division, concat() for strings (SQLite has it from 3.44.0), ORDER BY points IS NULL, points DESC to put NULLs last in all three, lower() on both sides of a comparison that must ignore case, and only grouped or aggregated columns in a grouped query.

Explain the trade-offs in a minute

The last minute of an answer is for what the query costs and what you would change. For the worked problem, three sentences cover it. The query reads one week of deliveries once and groups them into a few cities, so its cost grows with the week’s rows, not with the table. An index on (status, promised_at) lets the engine read only the delivered orders of the week, the equality column first and the range column second. With more time, you would test a week without deliveries and an order with a NULL promised time, which the schema here forbids.

Name the alternative you did not choose and why. PostgreSQL and SQLite could count the late deliveries with count(*) FILTER (WHERE delivered_at > promised_at), which reads well but does not run on MySQL, so the CASE form is the portable choice. Saying that shows the interviewer that you chose, rather than wrote the first thing that worked.

The forms an SQL round takes

The routine stays the same; what changes is how much of it you say aloud and how much you write down.

Your turn

-- Completion within 30 days, per course, for the enrolments of the first quarter.
-- This first draft has the shape of the answer, but some of its numbers are wrong.
SELECT course,
       count(*) AS enrolments,
       sum(CASE WHEN julianday(completed_on) - julianday(enrolled_on) <= 30 THEN 1 ELSE 0 END) AS completed_in_30,
       round(100 * sum(CASE WHEN julianday(completed_on) - julianday(enrolled_on) <= 30 THEN 1 ELSE 0 END) / count(*), 1) AS completion_pct
FROM enrolments
WHERE enrolled_on BETWEEN '2026-01-01' AND '2026-04-01'
GROUP BY course
HAVING count(*) >= 3
ORDER BY completion_pct DESC, course;

# Sample tests: each one builds a small enrolments table, runs your query on it and compares the rows it returns, in
# order.
tests:
  - name: counts day 30 as within 30 days and keeps one decimal place
    ordered: true
    setup: |
      CREATE TABLE enrolments (student_id INTEGER, course TEXT NOT NULL, enrolled_on TEXT NOT NULL, completed_on TEXT);
      INSERT INTO enrolments VALUES
        (1, 'SQL basics', '2026-01-01', '2026-01-16'),
        (2, 'SQL basics', '2026-01-10', '2026-02-20'),
        (3, 'SQL basics', '2026-02-01', '2026-03-03'),
        (4, 'Python',     '2026-01-15', NULL),
        (5, 'Python',     '2026-02-15', '2026-02-16'),
        (6, 'Python',     '2026-03-01', NULL);
    expected:
      columns: [course, enrolments, completed_in_30, completion_pct]
      rows:
        - ['SQL basics', 3, 2, 66.7]
        - ['Python', 3, 1, 33.3]
  - name: leaves out enrolments on the first day of the next quarter
    ordered: true
    setup: |
      CREATE TABLE enrolments (student_id INTEGER, course TEXT NOT NULL, enrolled_on TEXT NOT NULL, completed_on TEXT);
      INSERT INTO enrolments VALUES
        (1, 'Excel', '2026-03-30', '2026-04-02'),
        (2, 'Excel', '2026-03-31', NULL),
        (3, 'Excel', '2026-04-01', '2026-04-03'),
        (4, 'Excel', '2026-02-10', '2026-02-11'),
        (5, 'Git',   '2026-01-02', '2026-01-03'),
        (6, 'Git',   '2026-04-01', '2026-04-02'),
        (7, 'Git',   '2026-01-03', NULL);
    expected:
      columns: [course, enrolments, completed_in_30, completion_pct]
      rows:
        - ['Excel', 3, 2, 66.7]
  - name: breaks ties by course and reports a course nobody finished in time
    ordered: true
    setup: |
      CREATE TABLE enrolments (student_id INTEGER, course TEXT NOT NULL, enrolled_on TEXT NOT NULL, completed_on TEXT);
      INSERT INTO enrolments VALUES
        (1, 'Linux',  '2026-01-05', NULL),
        (2, 'Linux',  '2026-01-06', NULL),
        (3, 'Linux',  '2026-01-07', '2026-03-01'),
        (4, 'Docker', '2026-02-01', '2026-02-02'),
        (5, 'Docker', '2026-02-02', '2026-02-03'),
        (6, 'Docker', '2026-02-03', NULL),
        (7, 'Bash',   '2026-03-01', '2026-03-02'),
        (8, 'Bash',   '2026-03-02', '2026-03-05'),
        (9, 'Bash',   '2026-03-03', NULL);
    expected:
      columns: [course, enrolments, completed_in_30, completion_pct]
      rows:
        - ['Bash', 3, 2, 66.7]
        - ['Docker', 3, 2, 66.7]
        - ['Linux', 3, 0, 0.0]

Interview questions

Fresher · What should you ask before you write the query in an SQL interview?

Ask about whatever would change the result. First the grain: what does one row of the answer stand for, one per city, per customer or per day? Then the edge cases in the data: what a NULL means in each column you use, whether rows can repeat, and what should happen when values tie at a boundary such as a top 3. For anything with time, ask which time zone the timestamps use and where each period starts and ends, and treat periods as half-open: from the start up to, but not including, the end. Finally ask which engine and version run the query, because integer division, the position of NULLs in a sort and text comparison differ between engines. Restate the answers in one sentence before you write any SQL, so that the interviewer can correct you early.

Key takeaways

• Restate the question as one sentence that names the grain, then ask only the questions whose answers change the rows: NULLs, ties, duplicates, time boundaries and the engine.
• Sketch sample rows and the expected output before the query; it is the only check that catches a draft that runs without an error and returns the wrong number.
• Build the query as named CTE steps and run each one, so a wrong number points at one step.
• Compare a result with the expected rows using EXCEPT in both directions; add a count or a row number when duplicates or order matter.
• Integer division, NULL sorting, ||, case in comparisons and ungrouped columns differ between SQLite, PostgreSQL and MySQL: ask which engine runs the query, or write the forms that agree in all three.