SQL interview questions: joins, window functions, NULLs, indexes and transactions

The SQL interview questions that come up most are about JOIN types, WHERE versus HAVING, GROUP BY, window functions such as ROW_NUMBER and RANK, the second highest salary, top N per group, NULL behaviour, indexes, EXPLAIN and transaction isolation. A strong answer gives the rule, a short query and the trade-off. Every query below ran on PostgreSQL 16 and, where the feature exists, on SQLite.

A note on dialects first. We wrote standard SQL that runs unchanged on PostgreSQL 16 and SQLite 3.53, and we point out where MySQL, SQL Server or Oracle differ. If the interviewer names an engine, ask which version. SQLite only gained window functions in 3.25 and RIGHT and FULL joins in 3.39, MySQL gained window functions in 8.0 and still has no FULL OUTER JOIN, and older installs are still around.

The example schema used in every answer

Two small tables, deliberately awkward: a department with nobody in it (Legal), an employee with no department (Fay), and a salary tie (Ben and Cara at 110). Those three details are what make join, ranking and NULL questions interesting, and interviewers build their sample data the same way. Salaries are in thousands to keep the numbers short.

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments (id),
  salary  INTEGER NOT NULL  -- in thousands
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Legal');
INSERT INTO employees VALUES
  (1, 'Ana', 1, 120), (2, 'Ben', 1, 110), (3, 'Cara', 1, 110),
  (4, 'Gus', 1, 100), (5, 'Dev', 2, 90),  (6, 'Eli', 2, 70),
  (7, 'Fay', NULL, 80);

When you are handed a schema in an interview, ask two questions before you write anything: can a department have zero employees, and can dept_id be NULL? Those two answers settle half of the follow-ups below before anyone asks them, and asking shows you think about data, not just syntax.

What are the different types of JOIN in SQL?

An INNER JOIN returns only rows that match on both sides. A LEFT JOIN returns every row from the left table and fills the right side with NULL where nothing matches. RIGHT JOIN is the mirror image, and most people rewrite it as a LEFT JOIN with the tables swapped because it reads more easily. A FULL OUTER JOIN keeps unmatched rows from both sides. A CROSS JOIN returns every combination, so our 3 departments and 7 employees give 21 rows.

-- INNER: only matching pairs (6 rows, Fay and Legal drop out)
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

-- LEFT: every department, NULL where nobody matches (Legal appears once)
SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
ORDER BY d.name, e.name;

-- FULL OUTER: both unmatched sides (Fay with no dept, Legal with no one)
SELECT d.name AS dept, e.name
FROM departments d
FULL OUTER JOIN employees e ON e.dept_id = d.id
WHERE d.id IS NULL OR e.id IS NULL;

On this data the inner join returns six rows. Fay has no department and Legal has no employees, so both drop out. The LEFT JOIN returns seven rows, with Legal appearing once next to a NULL. The FULL OUTER JOIN, filtered to unmatched rows, returns exactly the two orphans. On MySQL, which lacks FULL OUTER JOIN, the usual workaround is a LEFT JOIN combined with a RIGHT JOIN through UNION.

Follow-up: why did my LEFT JOIN lose rows?

This is the most common join bug in interviews and in production. A condition on the right-hand table placed in WHERE runs after the join. For an unmatched row the right-hand columns are NULL, NULL > 100 is not true, and the row is thrown away. The LEFT JOIN has quietly become an inner join. Move the condition into ON and the outer rows survive.

-- Filter in WHERE: Sales and Legal vanish, the LEFT JOIN is now an inner join
SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 100
ORDER BY d.name, e.name;

-- Filter in ON: every department stays, only the matches are filtered
SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id AND e.salary > 100
ORDER BY d.name, e.name;

The first query returns three Engineering rows. The second returns five: the same three plus Legal and Sales with a NULL, because those departments still exist even though nobody in them earns over 100. The rule to say out loud: conditions in ON decide which rows match, and conditions in WHERE decide which rows survive. For an inner join both placements give the same result, which is why the bug only shows up with outer joins.

Follow-up: what are self joins, semi joins and anti joins?

A self join is a table joined to itself under two aliases. You use it for hierarchies, such as employee to manager, or to compare rows in the same table, such as pairs of colleagues in one department with e1.id < e2.id so each pair appears once. A semi join returns rows that have at least one match in another table without duplicating them, written with EXISTS or IN. An anti join returns rows with no match, written with NOT EXISTS or with a LEFT JOIN and a WHERE right_table.id IS NULL check. The NULL section below explains why NOT IN is the wrong tool for anti joins.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation. That is why WHERE cannot reference COUNT or SUM and HAVING can. If a condition does not involve an aggregate, put it in WHERE: fewer rows reach the grouping step, and the intent is clearer to the reader.

SELECT dept_id,
       COUNT(*)              AS headcount,
       ROUND(AVG(salary), 1) AS avg_salary
FROM employees
WHERE salary >= 80          -- filters rows before grouping
GROUP BY dept_id
HAVING COUNT(*) >= 2        -- filters groups after aggregation
ORDER BY dept_id;

The result is one row: department 1 with four people and an average of 110.0. Walk through it step by step. WHERE removes Eli, who earns 70, so Sales enters the grouping with only Dev. GROUP BY then forms three groups: department 1, department 2, and a NULL group holding Fay. HAVING keeps only groups with at least two rows. Without the WHERE clause Sales would have two rows and pass, so the order of these steps changes the answer, and interviewers like candidates who can say why.

Follow-up: in what order does SQL evaluate a query?

Logically, the order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET. That one sequence explains several rules. You cannot use a SELECT alias in WHERE, because the alias does not exist yet. You can use it in ORDER BY, because that step runs later. Window functions are computed during SELECT, so you cannot filter on them in WHERE either. PostgreSQL also rejects a SELECT alias inside HAVING, while SQLite and MySQL accept it, so repeat the expression when you want portable SQL. The optimiser may execute things in a different physical order. This logical order is what defines the result.

How does GROUP BY work, and what are the common errors?

GROUP BY collapses rows that share the same values in the grouping columns into one row per group. Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate. Selecting name while grouping by dept_id is an error in PostgreSQL and SQL Server, because a department has no single name. SQLite, and MySQL with ONLY_FULL_GROUP_BY turned off, accept it and return a value from some row in the group, which is a bug waiting to happen. PostgreSQL does let you select other columns of a table when you group by that table's primary key, because they depend on it.

NULLs form their own group, which is why Fay appeared as a group of one above. Aggregates other than COUNT(*) skip NULLs: AVG over 10, NULL and 20 is 15, not 10. A related follow-up: list every department with its headcount, including empty ones. The answer is a LEFT JOIN from departments with COUNT(e.id), which gives Legal 0. COUNT(*) would give Legal 1, because the joined row exists even though its employee columns are all NULL. We checked both counts on both engines.

What are window functions, and how do ROW_NUMBER, RANK and DENSE_RANK differ?

A window function computes a value across a set of rows related to the current row without collapsing them. GROUP BY gives one row per group. A window function keeps every row and adds a column. The OVER clause defines the window: PARTITION BY splits rows into groups, ORDER BY sorts within each group, and an optional frame says which nearby rows count.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)       AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)       AS dense_rnk
FROM employees
WHERE dept_id = 1
ORDER BY row_num;

The three ranking functions differ only on ties. ROW_NUMBER gives every row a distinct number, so Ben and Cara get 2 and 3. RANK gives tied rows the same number and then leaves a gap, so Gus is 4. DENSE_RANK gives tied rows the same number with no gap, so Gus is 3. Notice the extra name in ROW_NUMBER's ORDER BY. Without a tie-breaker, which of Ben or Cara gets 2 is up to the engine and can change between runs. Saying that before you are asked is a strong signal.

How do you compute a running total?

Use SUM as a window function with an ORDER BY. The detail most candidates miss is the default frame. With ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every peer row that ties on the ORDER BY value. Two orders on the same day therefore get the same running total.

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  customer   TEXT NOT NULL,
  order_date DATE NOT NULL,
  amount     INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'acme', '2026-01-03', 50), (2, 'acme', '2026-01-10', 30),
  (3, 'acme', '2026-01-10', 25), (4, 'zen',  '2026-01-04', 20),
  (5, 'zen',  '2026-01-12', 40);

SELECT customer, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       SUM(amount) OVER (PARTITION BY customer ORDER BY order_date) AS default_frame
FROM orders
ORDER BY customer, order_date, id;

Look at acme's two orders on 10 January. With an explicit ROWS frame and a unique tie-breaker, the running total goes 50, 80, 105. With the default frame both of those rows show 105, because RANGE treats them as one step. For a true row-by-row total, write ROWS and include a unique column in the ORDER BY. Other window functions worth knowing: LAG and LEAD read the previous or next row, FIRST_VALUE and LAST_VALUE read the edges of the frame (LAST_VALUE falls into the same default-frame trap), and NTILE splits rows into roughly equal buckets.

How do you find the second highest salary?

This classic has three good answers, and the interviewer usually cares more about the trade-offs between them than about any single query. Our data has a tie at 110 just below the top salary of 120, so the correct second highest distinct salary is 110. All three queries below return it.

-- 1. Subquery: NULL when there is no second value
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. DENSE_RANK: change 2 to N for the Nth highest
SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) ranked
WHERE r = 2;

-- 3. LIMIT and OFFSET, wrapped so an empty result becomes NULL
SELECT (
  SELECT DISTINCT salary FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS second_highest;

The subquery version is the most portable, and it returns NULL when there is no second salary, which many graders expect. The DENSE_RANK version generalises: change 2 to N and you have the Nth highest. DENSE_RANK, not RANK or ROW_NUMBER, is what makes tied salaries count as one value. The LIMIT version is short, but on its own it returns zero rows rather than NULL when nothing sits at offset 1, so we wrapped it in a scalar subquery to turn an empty result into NULL.

Dialects matter here. LIMIT and OFFSET work in PostgreSQL, MySQL and SQLite. The SQL standard spelling, which PostgreSQL also accepts, is OFFSET 1 ROWS FETCH FIRST 1 ROW ONLY. SQL Server uses that form after an ORDER BY, or TOP, and Oracle supports FETCH FIRST from 12c on. The usual follow-up is what happens if two people share the highest salary. With DISTINCT or DENSE_RANK the answer is still correct. A plain ORDER BY salary DESC LIMIT 1 OFFSET 1 without DISTINCT would return the top salary again, which is exactly the bug the question is checking for.

How do you get the top N rows per group?

Number the rows inside each group with a window function in a subquery, then filter on that number in the outer query. You need the subquery, or a CTE, because WHERE runs before window functions are computed.

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY dept_id
           ORDER BY salary DESC, id
         ) AS rn
  FROM employees
  WHERE dept_id IS NOT NULL
) ranked
WHERE rn <= 2
ORDER BY dept_id, rn;

This returns Ana and Ben for Engineering and Dev and Eli for Sales. The real question is which function to use. ROW_NUMBER gives exactly N rows per group and breaks the tie with the id column. RANK would return Ana, Ben and Cara for Engineering, because Ben and Cara tie for second. Ask which one the business wants. Whether the top two earners includes a tied third person is a product decision, not a SQL one, and saying so shows judgment.

Alternatives worth naming: PostgreSQL's DISTINCT ON handles the N = 1 case neatly, and a LATERAL join with ORDER BY and LIMIT handles a small N per group and can use an index on (dept_id, salary). Some warehouses support a QUALIFY clause that filters window results directly. PostgreSQL 16 does not have QUALIFY, so do not reach for it unless the interviewer names an engine that does.

How do NULLs behave in SQL?

NULL means unknown, and a comparison with unknown gives unknown. SQL uses three-valued logic: a predicate is true, false or unknown, and WHERE keeps only rows where it is true. So NULL = NULL is not true, NULL <> 5 is not true, and the only reliable tests are IS NULL and IS NOT NULL. PostgreSQL and SQLite also support IS DISTINCT FROM, which treats two NULLs as equal. MySQL spells that comparison <=>.

SELECT CASE WHEN NULL = NULL THEN 'true' ELSE 'not true' END AS null_eq,
       CASE WHEN NULL IS NULL THEN 'true' ELSE 'not true' END AS null_is,
       COUNT(*)       AS all_rows,
       COUNT(dept_id) AS with_dept,
       COALESCE(MAX(CASE WHEN dept_id IS NULL THEN name END), 'none') AS no_dept
FROM employees;

-- Departments with nobody in them. NOT IN finds none,
-- because the subquery contains Fay's NULL.
SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

-- NOT EXISTS is NULL-safe and returns Legal.
SELECT d.name FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

The first query shows the basics. NULL = NULL falls through to not true, COUNT(*) counts all seven rows while COUNT(dept_id) counts six, and COALESCE turns a missing value into a default. The second query is the trap. We want departments with no employees, and NOT IN returns nothing at all. NOT IN expands to id <> 1 AND id <> 2 AND id <> NULL, and that last comparison is unknown for every row, so no row is ever true. NOT EXISTS checks whether a matching row exists instead of comparing values, so the NULL never enters a comparison and Legal comes back.

More NULL facts that appear as follow-ups. In ascending order, NULLs sort last in PostgreSQL and Oracle and first in SQLite, MySQL and SQL Server. PostgreSQL, Oracle and SQLite accept NULLS FIRST or NULLS LAST to make it explicit. A UNIQUE column accepts many NULLs in PostgreSQL, MySQL and SQLite, because two NULLs are not equal, while SQL Server allows only one. PostgreSQL 15 added NULLS NOT DISTINCT for unique constraints that should treat NULLs as equal. In PostgreSQL and SQLite, concatenating a string with NULL using || gives NULL. An empty string is not NULL, except in Oracle.

What is an index, and when does it not help?

An index is a separate, sorted structure that maps column values to rows. It is a B-tree by default in PostgreSQL, MySQL's InnoDB and SQLite. It turns a full scan into a search of roughly O(log n), and it can return rows already in order, which removes a sort. The cost is on writes: every INSERT, every DELETE and every UPDATE of an indexed column must maintain each index, and indexes take disk and memory.

A composite index on (account_id, created_at) is sorted by account_id first, then by created_at within each account. It serves a filter on account_id alone, and on account_id plus a range or sort on created_at. It cannot efficiently serve a filter on created_at alone. That is the leftmost-prefix rule, and it is the follow-up interviewers ask most often. We checked it on PostgreSQL 16 with a table of 500,000 rows. The plan output below is trimmed to the node names.

-- PostgreSQL 16. events has 500,000 rows.
CREATE INDEX idx_events_account_created ON events (account_id, created_at);
ANALYZE events;

EXPLAIN SELECT * FROM events
WHERE account_id = 42 ORDER BY created_at DESC LIMIT 10;
-- Limit
--   ->  Index Scan Backward using idx_events_account_created on events
--         Index Cond: (account_id = 42)

EXPLAIN SELECT * FROM events WHERE created_at > TIMESTAMP '2026-01-06';
-- Seq Scan on events  (created_at is not the leading column)

EXPLAIN SELECT * FROM events WHERE account_id + 0 = 42;
-- Parallel Seq Scan on events  (the expression masks the column)

The first query uses the index for both the filter and the ORDER BY. It reads the index backwards and stops after ten rows, so no sort step appears. The second filters on created_at, which is not the leading column, and the planner chose a sequential scan. The third wraps the indexed column in an expression, so the index on account_id no longer applies and the whole table is read.

Here is the full list of reasons an index does not help, which is worth having ready:

• The filter is not on the leading column of a composite index.
• The column is wrapped in a function or expression, such as lower(email) or a cast to date. Fix it with an expression index, or rewrite the predicate as a range on the raw column.
• A LIKE pattern starts with a wildcard, as in '%son'. A B-tree can only seek on a known prefix.
• The predicate matches a large share of the table. Reading most rows through an index is slower than one sequential pass. With an extra index on kind, our planner used it for 'error', about a tenth of the rows, and ignored it for 'view', about nine tenths.
• The comparison needs a type conversion on the column side, such as a text column compared with a number in MySQL.
• The table is tiny, or the planner's statistics are stale. Run ANALYZE.
• An OR across different columns, which may need separate indexes combined by a bitmap scan, or a rewrite as UNION.

Two refinements are worth a sentence each. A covering index holds every column a query needs, so PostgreSQL can answer with an Index Only Scan and skip the table; since PostgreSQL 11 you can add non-key columns with INCLUDE. A partial index, such as one built with WHERE kind = 'error', stays small and fast when queries always target the same subset.

How do you read EXPLAIN output?

EXPLAIN shows the plan the optimiser chose without running the query. EXPLAIN ANALYZE runs it and adds actual row counts and timings beside the estimates. Because ANALYZE really executes the statement, wrap an UPDATE or DELETE in BEGIN and ROLLBACK before you analyse it. SQLite uses EXPLAIN QUERY PLAN for the readable version, and MySQL supports EXPLAIN ANALYZE from 8.0.18.

Read the plan tree from the most indented node outward, because inner nodes feed their parents. Know the common node names. Seq Scan reads the whole table. Index Scan walks the index and fetches each matching row. Index Only Scan answers from the index alone. Bitmap Heap Scan collects matching row locations first and then reads them in physical order, which suits medium selectivity. For joins, Nested Loop suits a small outer side with an index on the inner side, Hash Join suits large unsorted inputs joined on equality, and Merge Join suits inputs already sorted on the join key.

The most useful habit is comparing estimated rows with actual rows. In our first run the planner estimated 250 rows for one account when the real count was 100, and a later ANALYZE estimated 100, because statistics come from a sample. A gap of a few times is normal. A gap of a hundred times or more usually means stale statistics or correlated columns, and it is how the planner ends up choosing the wrong join type. Cost figures are in arbitrary planner units, so compare them between plans for the same query, never as milliseconds.

What are transactions, ACID and isolation levels?

A transaction groups statements so they succeed or fail together. ACID names the four properties. Atomicity means all or nothing. Consistency means constraints hold before and after. Isolation means concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level. Durability means committed data survives a crash.

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 100), (2, 0);

BEGIN;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- violates CHECK
ROLLBACK;

SELECT id, balance FROM accounts ORDER BY id;

The second UPDATE breaks the CHECK constraint, and the ROLLBACK undoes the first, so the balances stay at 100 and 0 on both engines. There is a dialect difference worth knowing here. In PostgreSQL an error aborts the whole transaction, and every later statement fails until you roll back. In SQLite and MySQL a constraint error undoes only the failing statement and the transaction stays open, so code that commits after an error keeps the first UPDATE. Always roll back explicitly in your error handling.

The SQL standard defines four isolation levels by the anomalies each one rules out:

• Read uncommitted: may see other transactions' uncommitted changes, called dirty reads.
• Read committed: sees only committed data, but reading the same row twice can return different values, a non-repeatable read. This is PostgreSQL's default.
• Repeatable read: rows you have read do not change under you, but the standard still allows new matching rows to appear, called phantoms. This is the default for MySQL's InnoDB.
• Serializable: the outcome must equal some order of running the transactions one at a time.

Two engine details separate strong answers. PostgreSQL implements repeatable read as snapshot isolation, so it also prevents phantoms, but it still allows write skew. In write skew, two transactions each check a condition, such as at least one doctor being on call, and each change a different row, so together they break the rule neither broke alone. PostgreSQL's serializable level detects this and aborts one transaction with a serialization failure, SQLSTATE 40001, which the application must catch and retry. Also, PostgreSQL accepts read uncommitted but treats it as read committed, so dirty reads never happen there.

-- PostgreSQL and MySQL: lock the row for a read-modify-write
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- the application checks the balance here
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
COMMIT;

The other classic follow-up is the lost update. Two sessions read a balance of 100, both subtract 30 in application code, and both write 70. The fixes, in rough order of preference: express the change as one atomic statement, as in balance = balance - 30; lock the row with SELECT ... FOR UPDATE before a read-modify-write, as above; or use optimistic concurrency with a version column and a WHERE version = n check on the update, retrying when no row changes. SQLite has no FOR UPDATE, because it allows only one writer at a time.

What is normalization, and when would you denormalize?

Normalization organises tables so that each fact is stored once, which prevents update anomalies. Picture an orders table that repeats the customer's email on every row. Change the email and you must update many rows, and if you miss one the data contradicts itself. Delete a customer's last order and the email is gone entirely.

• First normal form: each column holds one atomic value, with no repeating groups or comma-separated lists.
• Second normal form: in a table with a composite key, every non-key column depends on the whole key, not part of it.
• Third normal form: non-key columns depend only on the key, not on other non-key columns. A zip code determining a city is the textbook violation.
• Boyce-Codd normal form: a stricter third normal form in which every determinant is a candidate key.

A good one-line summary is that every non-key column depends on the key, the whole key and nothing but the key. Then give the trade-off. Normalized schemas are easier to keep correct and cheaper to write to, but reads may need more joins. Teams denormalize on purpose for read-heavy paths: a stored order total, a counter column, a reporting table or a materialized view. When you propose a denormalized copy, say how it stays in sync, whether through a trigger, the same transaction as the write, or a scheduled rebuild, and what happens when it drifts.

Worked example: talking through a practice problem

Problem, invented for practice: for each customer, show every order with the previous order's amount and the change from it. Here is how a strong candidate might narrate it, then the query.

Example narration (invented): "I need a value from the previous row for the same customer, so this is a window function rather than a self join. I'll partition by customer and order by date. Acme has two orders on one date, so I'll add id as a tie-breaker to make the order deterministic. The first order for each customer has no previous row, so LAG gives NULL there, and I'll leave it as NULL instead of zero, because zero would claim a change that never happened."

SELECT customer, order_date, amount,
       LAG(amount) OVER w          AS prev_amount,
       amount - LAG(amount) OVER w AS change
FROM orders
WINDOW w AS (PARTITION BY customer ORDER BY order_date, id)
ORDER BY customer, order_date, id;

-- Warm-up: customer and date pairs with more than one order
SELECT customer, order_date, COUNT(*) AS n
FROM orders
GROUP BY customer, order_date
HAVING COUNT(*) > 1;

The output gives acme's orders a change of NULL, -20 and -5, and zen's NULL then 20. Notice what the narration did. It named the technique and the reason for it, raised the tie before it became a bug, and decided the edge case explicitly. Interviewers grade that reasoning as much as the final query. The second query, finding duplicate customer and date pairs with GROUP BY and HAVING COUNT(*) > 1, is another warm-up that turns up constantly.

SQL practice questions to try on your own

Work these against the schema above, out loud, with a timer of about ten minutes each. Write the query, predict its output, then run it on PostgreSQL or SQLite and compare.

1. Headcount including empty departments.. List every department with its number of employees, showing Legal as 0. Hint: a LEFT JOIN and COUNT of a column.
2. Above the department average.. Return employees who earn more than the average of their own department. Write it once with a correlated subquery and once with AVG as a window function.
3. Third highest distinct salary.. Generalise the second-highest query. What does each version return when there are only two distinct salaries?
4. Employees without a department, two ways.. Use IS NULL, then rewrite it as an anti join from employees to departments with NOT EXISTS.
5. Share of total.. For each order, show its share of that customer's total, using SUM(amount) OVER (PARTITION BY customer).
6. Index design.. For WHERE account_id = ? AND created_at >= ? ORDER BY created_at, which index would you create, and why does the column order matter?

Common mistakes in SQL interviews

• Putting a right-table condition in WHERE after a LEFT JOIN and losing the outer rows.
• Using NOT IN with a subquery that can return NULL.
• Writing = NULL instead of IS NULL.
• Using ROW_NUMBER without a tie-breaker, or RANK when the question needs exactly N rows.
• Selecting columns that are neither grouped nor aggregated and trusting an engine that allows it.
• Forgetting that COUNT(*) counts the NULL-filled row a LEFT JOIN produces.
• Saying add an index without naming the columns, their order and the write cost.
• Not asking which engine, then using syntax it lacks, such as QUALIFY in PostgreSQL or FULL OUTER JOIN in MySQL.

A practice loop for SQL rounds

1. Clarify the data.. Before typing, ask about NULLs, duplicates, ties and the engine. Write the answers as a comment above your query.
2. Name the shape first.. Say something like: this is top N per group, so a window function in a subquery. Naming the pattern buys thinking time and shows structure.
3. Build in layers.. Write the inner query, predict or check its rows, then wrap it. Debugging one layer at a time is faster than debugging a finished query.
4. Check the edge cases.. Ties, an empty group, a NULL key, a single row. Say which ones your query handles and how.
5. Mention scale briefly.. Name the index you would want and what EXPLAIN should show. One or two sentences is enough unless they ask for more.

Record yourself solving two problems a day for a week, then listen back for the moments you went quiet. The post on narrating a live coding problem under a timer has a structure for the talking part, and the two-week technical interview plan shows where SQL fits beside algorithms and system design.

How HIDEA AI fits SQL interview practice

HIDEA AI is a desktop app for macOS and Windows with four separate surfaces: Verbal, Live coding, System design and Project. SQL is one of the 18 languages the Live coding surface supports. You capture the problem from your screen, and the response leads with a plain-English approach before the query, so you can check the reasoning and not only the syntax. You keep typing in the interviewer's editor, and you speak or type every answer yourself.

Concept questions such as isolation levels or normalization are verbal. Mock interviews are verbal-only, run for 20 or 30 minutes, and are on Pro and Pro+, which makes them a good way to rehearse explaining write skew or the leftmost-prefix rule out loud. The Report tab scores a saved session afterwards. Fifteen session minutes a day is free, with no credit card, and the pricing page has the plan details.

If you are weighing any tool for a live round, check the employer's rules first. Is using an AI interview copilot cheating covers how to think it through.

Where to go next

• Practise the talking part: narrate a live coding problem under a timer.
• Read how to use an interview copilot in a live coding round.
• See the use cases for how the four surfaces differ, or download the app.
• Other language guides: Golang interview questions and Python interview questions.