Speaking in a SQL round: ask, check, then make it fast

A SQL round is not only a test of syntax. The interviewer also listens to how you work. Do you ask what the data looks like? Do you check the answer? Can you say why the query is slow, and what you would change?

A weak answer starts typing at once, never tests the query, and says "add an index" when the interviewer asks about speed. A strong answer asks a few questions first and builds the query in steps. It checks the query on a few rows and explains it aloud. It answers speed questions with a query plan and one measured change.

At SDE-1 level you should write correct queries and handle empty values. At SDE-2 level you should also explain and fix a slow query. At Senior level you should talk about the cost of a query, and when a design change is better than a faster query.

How the round runs

A SQL round usually has the same four parts: clarify, write, test, and follow-up. In the follow-up the interviewer changes the question. The common changes are an index, "it has far more rows now", a change of table design, and an edge case such as ties.

The Tradeoff SQL mock follows this order. It lasts about 30 to 35 minutes. At SDE-2 and Senior levels it starts with an easier warm-up problem. The steps are: clarify, write and run, explain, change the query, and table design or index. Your report has five parts: correctness (checked by the real SQL runner, not by the AI), query quality, edge cases, performance thinking, and communication. Correctness has the largest weight.

What each level is asked, in short:

• SDE-1: filters, joins, grouping, and empty values.
• SDE-2: windows, top N per group, dates, and CTEs.
• Senior: gaps and islands, funnels, cohorts, medians, and the cost of the query.

Clarify first

Before you type, ask the questions that change the answer. Say them in a short list:

1. One row per what? Per customer, per order, per day?
2. Ties: if two rows tie for first, do both show?
3. Empty values: can this column be empty? What should an empty value mean?
4. Duplicates: can the same row appear twice?
5. Dates: are the first and last day included? Which day does the week start on?
6. Empty groups: should a customer with no orders show with 0?
7. Output: which columns, which names, which sort order?

Method: build, check, then test the edges

1. Write the shape. Say it in words: "group orders by restaurant, then rank".
2. Build in CTEs. One step for each idea (lesson 46). Name each by its meaning.
3. Check each step on the sample rows. Run it alone and look.
4. Work one tiny case by hand, with 3 or 4 rows, and compare with the query.
5. Check the row count after each join. A wrong count means a repeated or a lost row.
6. Try the edge rows: an empty table, one row, ties, empty values and duplicates.

The trap checklist

Each trap gives a wrong answer without an error. Read the list before you say "done".

1. A NULL in NOT IN, or in an aggregate (lessons 43, 46).
2. Whole-number division: 7 / 2 is 3 (lesson 42).
3. BETWEEN with a timestamp loses the last day (lesson 42).
4. A join repeats rows, so a sum counts twice (lesson 44).
5. A test in WHERE on the right table undoes a left join (lesson 44).
6. COUNT(*) against COUNT(column) after a left join (lesson 45).
7. A tie in ROW_NUMBER with no tie-break column (lesson 47).
8. LAST_VALUE with the default frame (lesson 47).
9. A column alias in WHERE: SQLite accepts it, other databases do not (lesson 42).
10. A selected column missing from GROUP BY (lesson 45).
11. Dates stored as text in a form that does not sort, such as 31-03-2026 (lesson 42 shows why the year comes first).
12. DISTINCT added to hide repeats that a wrong join made.

Explaining a query aloud

Read your query in the order the database works, not top to bottom: "First FROM and the join. Then WHERE. Then GROUP BY. Then the window. Then the final SELECT and the sort." Name each CTE by its meaning, for example daily_revenue and not t1. Say what each step does to the number of rows. Finish with the cost in plain words: "This reads the whole orders table once and sorts it."

Making it fast

The interviewer asks: "Will this work on a large table?" Do not guess. Ask the database for the query plan, the steps it will take (lesson 36). In SQLite the command is EXPLAIN QUERY PLAN in front of the query. Postgres and MySQL use EXPLAIN, and EXPLAIN ANALYZE to run it and measure.

Read two words:

• SCAN means the database reads every row of the table, or every entry of an index.
• SEARCH ... USING INDEX means it jumps straight to the matching rows with an index.

USING COVERING INDEX means the index holds every column the query needs, so the table is not read. USE TEMP B-TREE FOR ORDER BY means an extra sort.

What makes a query slow, and the fix:

A composite index puts the equality columns first and the range or sort column last (lesson 36). An index costs something: every write also updates it. Add one only for a query you need.

"It has 20 million rows now"

Do not jump to a new tool. Say: "I would measure the query first. I would change one thing, such as an index or the query shape. Then I would measure again." If the same report is read all day, a small summary table can answer it without reading all rows (lesson 45). Say the cost: it needs extra work on every write.

Changing data (not run here)

Some interviews end with "now delete the duplicates" or "fill a column". These statements change data, so the Tradeoff runner refuses them. We checked each one on a scratch copy. The rule: first write a SELECT that lists the rows, and look at them. Then change the data inside a transaction, a group of changes that all succeed or all fail, so you can undo it (lesson 37).

CREATE TABLE order_archive AS SELECT * FROM orders WHERE 0;
BEGIN;
INSERT INTO order_archive SELECT * FROM orders WHERE ordered_at < '2026-04-01';
COMMIT;

BEGIN;
UPDATE orders SET amount = (SELECT SUM(qty * price) FROM order_items WHERE order_id = orders.id) WHERE id <= 5;
COMMIT;

CREATE TABLE wishlist (id INTEGER PRIMARY KEY, customer_id INTEGER, item TEXT, saved_at TEXT);
INSERT INTO wishlist VALUES
  (1, 1, 'Butter Chicken', '2026-03-01 10:00:00'), (2, 1, 'Pani Puri', '2026-03-01 10:05:00'), (3, 1, 'Butter Chicken', '2026-03-02 09:00:00'),
  (4, 2, 'Vada Pav', '2026-03-02 11:00:00'), (5, 1, 'Butter Chicken', '2026-03-03 08:00:00'), (6, 2, 'Vada Pav', '2026-03-02 11:00:00');

The next statement removes the extra copies in a table of saved items. The inner query is the list from lesson 48 (rows 3, 5 and 6). It is a SELECT you can run alone first.

BEGIN;
DELETE FROM wishlist WHERE id IN (
  SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY customer_id, item ORDER BY saved_at, id) AS rn FROM wishlist) AS ranked WHERE rn > 1
);
COMMIT;

What the answers sound like

For the question "will this work on a large table?":

• SDE-1: "It reads every row. An index on the filter column would help."
• SDE-2: "The plan shows a scan. I would add an index on the restaurant and the time, with the restaurant first. It makes every write a little slower."
• Senior: "I would measure first. The function on the date column stops the index, so I would use a range. If the report is read all day, I would keep a small summary table. I would also say who keeps it correct."

A practice plan

The bank has 70 problems: 20 easy, 30 medium and 20 hard. Solve the easy ones. Then solve each again a day later, in 15 minutes, and say the clarify questions aloud. Do the same with the medium ones. For the hard ones, name the pattern first. Answer the follow-up questions aloud. Then try the mock.

In Postgres and MySQL

• Plans: Postgres and MySQL use EXPLAIN and EXPLAIN ANALYZE. The words differ (Seq Scan, Index Scan), but the idea is the same.
• Index on an expression: Postgres and SQLite support CREATE INDEX ... ON t (LOWER(email)). MySQL 8 supports it with extra brackets.
• Date functions differ between databases, so say which database you assume.
• Postgres has UPDATE ... FROM and DELETE ... USING for joins in changes; MySQL has UPDATE ... JOIN.

We test the ideas on a generated table big_orders with the same columns as orders. Assumption (made up to teach with): it has 1,000,000 rows, 500 restaurants, and orders over one year from 1 June 2025. The rows come from the statement below, which gives the same rows every time. All times below are measured on one computer with SQLite 3.50.4, as the median of 7 runs. Your numbers will differ. The ratios and the plans matter.

CREATE TABLE big_orders (
  id INTEGER PRIMARY KEY, customer_id INTEGER, restaurant_id INTEGER NOT NULL, amount INTEGER NOT NULL,
  status TEXT NOT NULL, coupon_off INTEGER, ordered_at TEXT NOT NULL
);
INSERT INTO big_orders
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000000)
SELECT i, i * 31 % 50000 + 1, i * 17 % 500 + 1, 100 + i * 13 % 500,
       CASE WHEN i % 13 = 0 THEN 'cancelled' ELSE 'delivered' END,
       CASE WHEN i % 5 = 0 THEN 50 END,
       datetime('2025-06-01', (i * 37 % 365) || ' days', (i * 7919 % 86400) || ' seconds')
FROM n;

Step 1: one restaurant, one month, no index. The plans below are from SQLite 3.36 or newer; older versions print SCAN TABLE.

SELECT id, ordered_at, amount FROM big_orders
WHERE restaurant_id = 42 AND ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'
ORDER BY ordered_at

Plan:
  SCAN big_orders
  USE TEMP B-TREE FOR ORDER BY

Result:
    id  ordered_at           amount
------  -------------------  ------
560973  2026-03-04 00:46:27     249
743473  2026-03-04 02:04:47     249
122973  2026-03-04 02:26:27     249
... 161 more rows

The plan says SCAN: the database read all 1,000,000 rows. It returned 164 rows in 116 ms. Check by hand: 1,000,000 rows / 500 restaurants = 2,000 orders for each restaurant. One month is about 2,000 / 12 = 167 of them. The result is close.

Step 2: add an index. The equality column restaurant_id goes first, and the range column ordered_at goes last.

CREATE INDEX idx_restaurant_time ON big_orders (restaurant_id, ordered_at);

SELECT id, ordered_at, amount FROM big_orders
WHERE restaurant_id = 42 AND ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'
ORDER BY ordered_at

Plan:
  SEARCH big_orders USING INDEX idx_restaurant_time (restaurant_id=? AND ordered_at>? AND ordered_at<?)

Result:
    id  ordered_at           amount
------  -------------------  ------
560973  2026-03-04 00:46:27     249
743473  2026-03-04 02:04:47     249
122973  2026-03-04 02:26:27     249
... 161 more rows

Now the plan says SEARCH ... USING INDEX, and the extra sort is gone, because the index is already in time order. The same 164 rows take 0.4 ms: about 300 times faster. Building the index took about 1.3 seconds, once.

Step 3: a function on the column. We count the orders of March in two ways, with an index on ordered_at alone.

CREATE INDEX idx_time ON big_orders (ordered_at);

SELECT COUNT(*) FROM big_orders WHERE strftime('%Y-%m', ordered_at) = '2026-03'

Plan:
  SCAN big_orders USING COVERING INDEX idx_time

Result:
COUNT(*)
--------
   84931

SELECT COUNT(*) FROM big_orders WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'

Plan:
  SEARCH big_orders USING COVERING INDEX idx_time (ordered_at>? AND ordered_at<?)

Result:
COUNT(*)
--------
   84931

Both give the same count, 84931 orders. The first plan says SCAN: the function hides the column, so the database computes strftime for every row (490 ms). The second says SEARCH: it jumps to March (7.5 ms), about 60 times faster. This answers the predict box: no, it cannot jump. Write a range.

Step 4: a deep page. Page 5,001 of a list sorted by id, 20 rows per page.

SELECT id, ordered_at FROM big_orders ORDER BY id LIMIT 20 OFFSET 100000

    id  ordered_at
------  -------------------
100001  2025-07-03 14:25:19
100002  2025-08-09 16:37:18
100003  2025-09-15 18:49:17
... 17 more rows

SELECT id, ordered_at FROM big_orders WHERE id > 100000 ORDER BY id LIMIT 20

    id  ordered_at
------  -------------------
100001  2025-07-03 14:25:19
100002  2025-08-09 16:37:18
100003  2025-09-15 18:49:17
... 17 more rows

OFFSET 100000 reads and drops 100,000 rows each time (6.2 ms). The keyset form starts after the last id the user saw (0.1 ms), about 60 times faster. The cost: you can step to the next page, but you cannot jump to page 5,001 by number.

Step 5: the cost on writes. We insert the same 100,000 rows into two copies of the table. One copy has no index. The other has the two indexes of steps 2 and 3.

CREATE TABLE copy_plain AS SELECT * FROM big_orders WHERE 0;
CREATE TABLE copy_indexed AS SELECT * FROM big_orders WHERE 0;
CREATE INDEX copy_idx_1 ON copy_indexed (restaurant_id, ordered_at);
CREATE INDEX copy_idx_2 ON copy_indexed (ordered_at);

INSERT INTO copy_plain SELECT * FROM big_orders WHERE id <= 100000

INSERT INTO copy_indexed SELECT * FROM big_orders WHERE id <= 100000

Without indexes the insert took 105 ms. With two indexes it took 2251 ms, about 20 times more. Reads got faster, and writes got slower. The gap is large here because this is a bulk insert, and the rows arrive in no order for the indexed columns. An insert of one row costs much less. The direction is the same, and it is the trade you name in the interview.

• It solves: wrong answers and slow answers, both found early, because you ask, check, and measure.
• It costs: time at the start of the round. Asking five questions takes two minutes, and it saves more than that later.
• I would change it when: the interviewer asks for speed only. Then I keep the clarify short and go straight to the plan.
• Say the cost of every index: faster reads, slower writes, more space.

"Add an index" is a complete answer to a slow query. Say which columns, in which order, and what it costs on writes. Then read the plan to see that the database uses it. A function on the column stops the index from being used.

"If the query returns the right rows on the example, it is correct." The example has few rows. A hidden test can have empty values, ties and duplicates. Try them on purpose.

"A tool built for scale will fix a slow query." First measure and fix the query and its indexes. A change of tool adds work and cost, and may not remove the cause.

1. In a query plan, what is the difference between SCAN and SEARCH ... USING INDEX?
2. A table has 2,000,000 rows. A query reads one restaurant that has 4,000 rows. About what share of the table does the index let you skip? A teammate adds 6 more indexes and writes become slower. Why?
3. The interviewer says: "The orders table now has 20 million rows and this report is slow. What do you do?" Give the first three steps.