SQL revision notes

What a database, a table and SQL are

Must know

A relational database stores data in tables of rows and columns, and SQL (Structured Query Language) is the language you use to ask it questions and to change what it holds.

SELECT column, other_column
FROM table_name
WHERE condition;

Must-know points

• A table holds one kind of thing. A row is one of them; a column is one fact about each.
• SQL is declarative: you say what you want, and the database decides how to get it.
• The result of a query is itself a table. That is why a query can be put inside another query.
• SQL keywords are not case-sensitive (select works), but writing them in capitals makes a query easier to read.
• Every major database speaks SQL. The core is the same everywhere; small details such as dates and LIMIT differ, and these notes point them out.
• How databases scale — SQL versus NoSQL, replication, sharding — is in the System Design tab, Phase 3.

Common mistakes

• Thinking SQL runs top to bottom like Python. It doesn't: the database reads FROM first and SELECT near the end.
• Thinking a database is just a big spreadsheet. It also enforces rules, handles many users at once and survives crashes.
• Forgetting the semicolon between two statements. One statement on its own usually runs without it.

Interview answer

A relational database stores data in tables, where each row is one record and each column is one attribute, and the tables are linked through keys. SQL is the declarative language for querying and changing that data: I describe the result I want, and the database's optimiser decides how to get it. That separation is why the same query can become much faster just by adding an index.

Follow-up questions

Meet the practice database

Must know

Six small tables — departments, employees, customers, orders, products and order_items — that every example and exercise in this course uses.

Must-know points

• employees.dept_id points at departments.dept_id; employees.manager_id points at employees.emp_id — the same table.
• orders.customer_id points at customers.customer_id.
• order_items links orders and products: one row per product in an order.
• Dates are stored as text in YYYY-MM-DD form, so sorting them as text also sorts them by date.
• Amounts and prices are whole pounds, to keep the sums easy to check by hand.

Common mistakes

• Assuming every employee has a department, or every customer has an order. NULLs and missing rows are where most wrong answers come from.
• Assuming ids are in date order. Here they happen to be; in real data, always sort by the date column.
• Writing a query before knowing the grain of each table — what one row means.

Interview answer

Before writing any SQL in an interview, I look at the tables: what one row means in each, which columns are keys, and how the tables join. Then I ask about the awkward cases — can this be NULL, can there be duplicates, can a customer have no orders — because those decide whether I need a LEFT JOIN, a DISTINCT or a COALESCE. Two minutes of that saves a wrong answer.

Follow-up questions

Keys and relationships: primary keys, foreign keys, one-to-many, many-to-many

Must know

A primary key is a column whose value identifies exactly one row; a foreign key is a column that holds another table's primary key, and those links are how tables relate: one-to-many, many-to-many or one-to-one.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id)
);

Must-know points

• A primary key is unique and never NULL. A table has exactly one, though it may span several columns.
• A foreign key holds values of another table's primary key — or NULL, if the column allows it.
• One-to-many: the foreign key goes on the "many" side (orders.customer_id), never a list of ids on the "one" side.
• Many-to-many needs a link table with two foreign keys (order_items).
• A natural key is a real-world value (an email); a surrogate key is a meaningless counter (customer_id). Prefer surrogate keys as primary keys.

Common mistakes

• Putting the foreign key on the wrong side, such as a column of order ids stored inside customers.
• Using a value that can change, like an email or a phone number, as the primary key.
• Thinking a foreign key must always be filled in. It can be NULL unless it is also NOT NULL: employees.dept_id is NULL for Gus.

Interview answer

A primary key uniquely identifies each row: it is unique and never NULL, and I usually make it a meaningless surrogate id. A foreign key is a column that references another table's primary key; it defines the relationship and lets the database reject links to rows that don't exist. One-to-many puts the foreign key on the many side; many-to-many needs a junction table with two foreign keys, usually with the pair as its composite primary key.

Follow-up questions

Data types: numbers, text, dates and true/false

Should know

Every column has a data type — such as INTEGER, a decimal number, TEXT, a date or a boolean — which decides what values it can hold and how they compare, sort and calculate.

CAST(value AS INTEGER)
CAST(value AS REAL)
CAST(value AS TEXT)

Must-know points

• INTEGER ÷ INTEGER = INTEGER in SQLite, PostgreSQL and SQL Server. Use 100.0 or CAST to get decimals.
• Use DECIMAL or NUMERIC for money: REAL and FLOAT are approximate and can show 0.30000000000000004.
• Text values go in single quotes: 'London'. Double quotes are for names of tables or columns.
• Store dates in date columns (or YYYY-MM-DD text), never as '06/01/2024', which sorts wrongly.
• NULL means "no value" and can appear in any column that isn't NOT NULL.

Common mistakes

• Computing a ratio with two integers and getting 0.
• Storing numbers as text and getting '10' sorted before '9'.
• Using double quotes for text values. In PostgreSQL, "London" means a column called London.

Interview answer

Data types decide how values compare and calculate. The classic traps are integer division — 7 / 2 is 3 in most databases, so for percentages I multiply by 100.0 first — and storing numbers or dates as text, which makes them sort wrongly. For money I use DECIMAL rather than FLOAT, because floating point can't represent most decimals exactly.

Follow-up questions

SELECT and FROM: choosing columns

Must know

SELECT lists the columns (or calculations) you want in the result, and FROM names the table they come from.

SELECT column, expression AS new_name
FROM table_name;

Must-know points

• SELECT chooses and calculates columns; FROM says which table the rows come from.
• Separate items with commas — and no comma after the last one.
• AS gives a column an alias. Use one for every calculated column, so the result has a readable name.
• SELECT * is fine for looking at a table, but in real queries name the columns you need: it is clearer, and faster on wide tables.
• Without a WHERE, every row of the table is in the result.

Common mistakes

• A trailing comma before FROM: SELECT name, salary, FROM employees is a syntax error.
• Using double quotes for text. 'London' is text; "London" means a column called London in PostgreSQL.
• Using an alias in WHERE in the same query. WHERE runs before SELECT, so the alias doesn't exist yet (Phase 2 explains why).

Interview answer

SELECT defines the columns of the result — stored columns, expressions, or constants — and FROM names the source table. I alias every calculated column with AS so the output is readable, and I avoid SELECT star in production queries: naming columns is clearer, it reads less data, and the query doesn't silently change when someone adds a column.

Follow-up questions

WHERE: keeping only the rows you want

Must know

WHERE keeps only the rows for which a condition is true, using comparisons such as =, <>, <, > and the words AND, OR and NOT.

SELECT columns
FROM table_name
WHERE condition AND (other OR another);

Must-know points

• WHERE runs on each row, before any grouping, and keeps only rows where the condition is true.
• Comparisons: =, <> (or !=), <, <=, >, >=. One = sign, not two.
• AND needs both sides true; OR needs at least one; NOT flips the answer.
• AND binds tighter than OR. Put brackets around every OR that is mixed with AND.
• Text goes in single quotes and must match exactly: 'Sales' is not 'sales' in most databases.

Common mistakes

• Mixing AND and OR without brackets, and getting extra rows.
• Writing ==. SQL uses a single = for comparing.
• Writing WHERE dept_id = 2 OR 3. Each side of OR must be a full condition: dept_id = 2 OR dept_id = 3, or better, dept_id IN (2, 3).

Interview answer

WHERE filters rows before any grouping: the database evaluates the condition for each row and keeps only those where it is true — false and unknown are both dropped. I combine conditions with AND, OR and NOT, and because AND has higher precedence than OR, I always bracket OR conditions when mixing them, so the query says exactly what I mean.

Follow-up questions

IN, BETWEEN and LIKE: shorter conditions

Must know

IN checks a value against a list, BETWEEN checks a range (both ends included), and LIKE matches text against a pattern with % (any number of characters) and _ (exactly one).

WHERE column IN (value1, value2)
WHERE column BETWEEN low AND high
WHERE text_column LIKE 'pattern%'

Must-know points

• x IN (a, b) means x = a OR x = b.
• x BETWEEN a AND b means x >= a AND x <= b — both ends included, and a must be the smaller value.
• LIKE wildcards: % is any number of characters (including none); _ is exactly one character.
• NOT IN, NOT BETWEEN and NOT LIKE flip each test.
• A LIKE pattern that starts with % ('%son') can't use a normal index, so it is slow on big tables.

Common mistakes

• Using BETWEEN on timestamps: BETWEEN '2024-02-01' AND '2024-02-29' misses everything after midnight on the 29th. Use >= '2024-02-01' AND < '2024-03-01' instead.
• Writing BETWEEN with the bigger value first: BETWEEN 100 AND 50 returns nothing.
• Forgetting the % in LIKE: LIKE 'P' only matches the exact text P.

Interview answer

IN tests membership in a list and is shorthand for several ORs; BETWEEN tests an inclusive range; LIKE matches text patterns, where percent is any sequence of characters and underscore is exactly one. Two cautions: for timestamps I use a half-open range — greater-or-equal the start and less-than the next day — rather than BETWEEN, and a leading-wildcard LIKE can't use a B-tree index.

Follow-up questions

NULL: the missing value

Must know

NULL means "no value" or "unknown"; any comparison with it — even NULL = NULL — is unknown rather than true, so you test for it with IS NULL and replace it with COALESCE.

WHERE column IS NULL
WHERE column IS NOT NULL
COALESCE(column, default_value)

Must-know points

• NULL means unknown or missing. It is not 0 and not ''.
• Never write = NULL or <> NULL. Use IS NULL and IS NOT NULL.
• Any comparison with NULL is unknown, and WHERE drops unknown rows — including from <> and NOT IN.
• Arithmetic with NULL gives NULL: 10 + NULL is NULL.
• COALESCE(a, b, c) returns the first non-NULL value. NULLIF(a, b) returns NULL when a = b — handy to avoid dividing by zero.
• Aggregates (SUM, AVG, COUNT of a column) skip NULLs — Phase 2 covers this.

Common mistakes

• Writing WHERE manager_id = NULL and getting no rows, with no error.
• Forgetting that <> drops NULL rows: "everyone not in Sales" silently loses people with no department.
• Replacing NULL with 0 in an average, which changes the answer. Decide what the gap really means first.

Interview answer

NULL represents a missing or unknown value, and SQL uses three-valued logic: comparisons with NULL return unknown, and WHERE keeps only rows that are true. So I never compare with equals NULL — I use IS NULL or IS NOT NULL — and I watch for filters like not-equal or NOT IN silently dropping NULL rows. When I need a default I use COALESCE, but only after deciding whether the gap really means zero.

Follow-up questions

ORDER BY, LIMIT and OFFSET: sorting and top N

Must know

ORDER BY sorts the result by one or more columns (ASC for smallest first, DESC for largest first), and LIMIT keeps only the first n rows, with OFFSET to skip some first.

SELECT columns
FROM table_name
ORDER BY column1 DESC, column2 ASC
LIMIT n OFFSET skip;

Must-know points

• Without ORDER BY, row order is not guaranteed — even if it looks sorted today.
• ASC (default) is smallest first; DESC is largest first. Each column gets its own direction.
• Add a second sort column to break ties, so the order is the same every time.
• LIMIT cuts after sorting. It ignores ties.
• OFFSET skips rows; it is fine for small pages but slow for deep pages, because the skipped rows are still read.

Common mistakes

• Using LIMIT without ORDER BY and expecting the "top" rows.
• Writing ORDER BY salary, name DESC and expecting both to be descending. DESC applies only to the column right before it.
• Answering "top 3 salaries" with LIMIT 3 when there are ties.

Interview answer

ORDER BY sorts the final result, with ASC or DESC per column, and I always add a tiebreaker column so the order is deterministic. LIMIT and OFFSET then cut the sorted result. I point out two caveats: LIMIT ignores ties, so "top 3" may need RANK instead; and large OFFSETs are slow, so for deep pagination I use keyset pagination — WHERE id greater than the last id seen.

Follow-up questions

DISTINCT: removing duplicate rows

Must know

SELECT DISTINCT removes duplicate rows from the result, where two rows are duplicates only if every selected column is the same.

SELECT DISTINCT column1, column2
FROM table_name;

Must-know points

• DISTINCT applies to the whole selected row, never to just one column.
• Adding a unique column (like an id) to a DISTINCT query makes it do nothing.
• NULLs count as equal for DISTINCT: many NULLs become one.
• COUNT(DISTINCT column) counts the different values — Phase 2.
• DISTINCT has to compare every row, which costs time on big results.

Common mistakes

• Writing SELECT DISTINCT(name), email and thinking only name is distinct. The brackets do nothing; the whole row is compared.
• Using DISTINCT to hide duplicates caused by a wrong join. Fix the join instead (Phase 3).
• Using DISTINCT when you actually need a count per value. That is GROUP BY.

Interview answer

SELECT DISTINCT removes duplicate rows from the result, comparing all the selected columns together, with NULLs treated as equal. I use it for lists of unique values, but if I find myself adding DISTINCT to fix unexpected duplicates, I go back and check the join, because DISTINCT can hide a fan-out that also breaks sums and counts.

Follow-up questions

CASE WHEN: if-then logic inside a query

Must know

CASE WHEN condition THEN value … ELSE value END returns a value chosen by the first condition that is true, so a query can label, group or tidy values row by row.

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE other_value
END AS new_column

Must-know points

• CASE returns the THEN value of the first true WHEN, then stops.
• No ELSE means NULL for rows that match nothing.
• Always finish with END, and name the result with AS.
• CASE is an expression: it works in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates.
• Every THEN should return the same type — all text, or all numbers.

Common mistakes

• Putting the loose condition first (>= 50000 before >= 80000), so the strict one is never reached.
• Forgetting END, which gives a syntax error.
• Writing CASE status WHEN NULL THEN …. The short form uses =, so it never matches NULL. Use CASE WHEN status IS NULL THEN ….

Interview answer

CASE is SQL's conditional expression: it evaluates WHEN conditions in order and returns the first matching THEN value, or the ELSE value, or NULL if there is no ELSE. Because it is an expression it works anywhere — I use it to bucket values, to sort in a custom order, and most of all inside aggregates, like SUM of CASE WHEN status equals delivered THEN 1, to count several categories in a single pass.

Follow-up questions

Built-in functions for text, numbers and dates

Should know

Functions such as UPPER, LENGTH, SUBSTR, ROUND and the date functions transform a value inside a query — one value in, one value out, for each row.

UPPER(text)   LENGTH(text)   SUBSTR(text, start, length)
ROUND(number, places)
strftime('%Y-%m', date)   -- SQLite: the year and month

Must-know points

• A row function runs once per row and returns one value; an aggregate function squashes many rows into one.
• Text positions start at 1, not 0: SUBSTR('Dara', 1, 3) is 'Dar'.
• ROUND(x, 2) for display; divide by a decimal (12.0) to avoid integer division first.
• Date functions differ the most between databases. Know your target database's month-truncation and date-difference functions.
• Wrapping a column in a function inside WHERE (WHERE UPPER(name) = …) stops a normal index on that column being used.

Common mistakes

• Counting text positions from 0, as in Python.
• Rounding before summing, which adds up rounding errors. Sum first, round at the end.
• Comparing dates stored as '06/01/2024' text — they compare as text, not as dates.

Interview answer

Scalar functions transform one value per row: string functions like UPPER, SUBSTRING and TRIM, numeric ones like ROUND, and date functions for truncating to a month or finding the days between two dates. Date syntax is the least portable part of SQL, so in an interview I name the dialect I am assuming — usually PostgreSQL, with DATE_TRUNC and date subtraction — and I avoid wrapping indexed columns in functions inside WHERE, because that blocks the index.

Follow-up questions

Aggregate functions: COUNT, SUM, AVG, MIN and MAX

Must know

An aggregate function turns many rows into one value — COUNT counts, SUM adds, AVG averages, MIN and MAX find the smallest and largest — and every one except COUNT(*) skips NULLs.

SELECT COUNT(*), COUNT(column), COUNT(DISTINCT column),
       SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name;

Must-know points

• COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts different non-NULL values.
• SUM, AVG, MIN and MAX ignore NULLs.
• SUM of no rows (or only NULLs) is NULL, not 0. Wrap it: COALESCE(SUM(x), 0).
• Without GROUP BY, an aggregate query returns exactly one row — even from an empty table.
• MIN and MAX work on text and dates too.

Common mistakes

• Using COUNT(column) when you mean "how many rows", and getting a smaller number because of NULLs.
• Mixing a plain column with an aggregate without GROUP BY: SELECT name, MAX(salary) FROM employees is an error in most databases (SQLite allows it but the name is unreliable).
• Putting an aggregate in WHERE: WHERE SUM(amount) > 100 is an error. That is HAVING's job.

Interview answer

Aggregate functions collapse a set of rows into one value: COUNT, SUM, AVG, MIN and MAX. The detail interviewers probe is NULL handling — COUNT star counts rows, COUNT of a column skips NULLs, and SUM and AVG ignore NULLs, so an average is over the known values only. I also remember that SUM over no rows is NULL, so I COALESCE it to zero when the result feeds a report.

Follow-up questions

GROUP BY: one result row per group

Must know

GROUP BY splits the rows into groups that share the same value in the listed columns, and the aggregates then return one row per group.

SELECT group_column, COUNT(*), SUM(column)
FROM table_name
GROUP BY group_column;

Must-know points

• GROUP BY makes one result row per distinct combination of the grouping columns.
• Every column in SELECT must be in GROUP BY or inside an aggregate.
• All NULLs in a grouping column form one group.
• Groups with no rows don't appear at all. To show zeros (like HR with 0 people), start from the full list and LEFT JOIN (Phase 3).
• Grouped results come back in no particular order. Add ORDER BY.

Common mistakes

• Selecting a column that is neither grouped nor aggregated, like SELECT dept_id, name, COUNT(*) … GROUP BY dept_id.
• Expecting groups with zero rows to appear with a count of 0.
• Grouping by too many columns (such as the id), so every group has one row and nothing is summarised.

Interview answer

GROUP BY partitions the filtered rows into groups by the grouping columns, and aggregates are computed once per group, giving one output row per group. The rule is that every selected column must be grouped or aggregated. Two details I mention: NULLs form a single group, and groups with no rows simply don't appear — so to report zeros I drive the query from the complete dimension table with a LEFT JOIN.

Follow-up questions

HAVING: filtering groups, and how it differs from WHERE

Must know

HAVING filters groups after GROUP BY, using aggregates such as COUNT(*) > 1, while WHERE filters single rows before any grouping happens.

SELECT group_column, COUNT(*)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition;

Must-know points

• WHERE filters rows, before GROUP BY. HAVING filters groups, after GROUP BY.
• Aggregates can be used in HAVING but not in WHERE.
• A row-level condition belongs in WHERE: it is clearer and faster.
• HAVING can test an aggregate that isn't in SELECT, such as HAVING MAX(salary) > 60000.

Common mistakes

• Writing WHERE COUNT(*) > 1 — an error, because counts don't exist yet when WHERE runs.
• Putting a plain row condition in HAVING (HAVING status = 'delivered'): it either fails or does something unexpected.
• Using a SELECT alias in HAVING (HAVING headcount > 1). Some databases allow it; standard SQL and SQL Server don't. Repeat the aggregate.

Interview answer

WHERE and HAVING are both filters but run at different stages. WHERE filters individual rows before grouping, so it can't use aggregates. HAVING filters groups after GROUP BY, so it can. If a condition doesn't need an aggregate I put it in WHERE, because filtering early means less data to group.

Follow-up questions

Conditional aggregation: counting several things at once

Must know

Putting CASE inside an aggregate — SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) — counts or sums only the rows that match, so one query can produce several different totals, or turn rows into columns (a pivot).

SELECT group_column,
       SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS how_many,
       SUM(CASE WHEN condition THEN value END)    AS how_much
FROM table_name
GROUP BY group_column;

Must-know points

• SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts matching rows; COUNT(CASE WHEN cond THEN 1 END) does the same, because COUNT skips the NULLs.
• SUM(CASE WHEN cond THEN amount END) sums only matching amounts.
• One pass over the data gives many totals — faster and simpler than several queries.
• A pivot is conditional aggregation with one column per value.
• For percentages, multiply by 100.0 before dividing.

Common mistakes

• Writing COUNT(CASE WHEN cond THEN 1 ELSE 0 END). COUNT counts the 0s too, because 0 is not NULL. Use SUM, or drop the ELSE.
• Computing a percentage with whole numbers: 100 * 7 / 10 is fine but 7 / 10 * 100 is 0.
• Conditions that overlap when the columns should add up to the total.

Interview answer

Conditional aggregation puts a CASE expression inside an aggregate, like SUM of CASE WHEN status is delivered THEN 1 ELSE 0, so I can compute several metrics per group in a single pass — counts by status, revenue by month as columns, or a ratio. For ratios I multiply by 100.0 first to avoid integer division, and I make sure SUM rather than COUNT wraps a CASE that returns zero.

Follow-up questions

The order a query really runs in

Must know

A query is written SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, but the database works it out in a different order — FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT — and that order explains most "why doesn't this work?" errors.

-- written order        -- run order
SELECT    ...           -- 5
FROM      ...           -- 1
WHERE     ...           -- 2
GROUP BY  ...           -- 3
HAVING    ...           -- 4
ORDER BY  ...           -- 6
LIMIT     ...;          -- 7

Must-know points

• Run order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
• WHERE can't use SELECT aliases or aggregates; HAVING can use aggregates; ORDER BY can use aliases.
• Window functions are worked out at the SELECT step, so you can't filter on them in WHERE — wrap the query instead.
• To filter on a calculated column, repeat the expression in WHERE or wrap the query in a subquery or CTE.

Common mistakes

• Using a SELECT alias in WHERE and getting "no such column".
• Expecting LIMIT to run before ORDER BY ("take 3 rows, then sort them").
• Filtering on ROW_NUMBER() in WHERE — it doesn't exist yet at that step.

Interview answer

SQL is written SELECT first, but logically processed as FROM and JOINs, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That explains the common rules: WHERE can't reference SELECT aliases or aggregates because they don't exist yet, HAVING can use aggregates, and ORDER BY can use aliases. To filter on a computed column or a window function, I wrap the query in a CTE and filter outside.

Follow-up questions

Joins, and INNER JOIN

Must know

A JOIN combines rows from two tables wherever a condition (usually matching keys) is true, and INNER JOIN keeps only the rows that found a match on both sides.

SELECT a.column, b.column
FROM table_a AS a
INNER JOIN table_b AS b ON b.key = a.key;

Must-know points

• A join pairs rows from two tables where the ON condition is true; each pair is one result row.
• INNER JOIN (or just JOIN) keeps only matched rows — unmatched rows on either side vanish.
• NULL keys never match, so rows with a NULL key always drop out of an INNER JOIN.
• Table aliases (e, d) keep queries short. Always prefix columns with them once there is more than one table.
• One-to-many joins repeat the "one" side once per match. That is expected.

Common mistakes

• Forgetting the ON condition, which pairs every row with every row (a cross join): 8 × 4 = 32 rows.
• Leaving out the prefix on a column both tables have (dept_id), which gives an "ambiguous column" error.
• Using INNER JOIN when the question says "all customers" or "every department" — the ones with no match disappear.

Interview answer

A join combines rows from two tables based on a condition, normally a foreign key matching a primary key. INNER JOIN returns only rows that match on both sides, so rows with no partner — or a NULL key — are excluded. In a one-to-many join the one side repeats for each match, which is correct but means I have to be careful when summing columns from the one side.

Follow-up questions

LEFT JOIN: keep every row from one side

Must know

A LEFT JOIN keeps every row from the left (first) table, joins matching rows from the right table, and fills the right-hand columns with NULL where there is no match.

SELECT a.column, b.column
FROM table_a AS a
LEFT JOIN table_b AS b ON b.key = a.key;

Must-know points

• LEFT JOIN keeps every row of the left table; unmatched rows get NULLs on the right.
• Put the table you want to keep in full first ("every customer" → customers first).
• Count the right side with COUNT(right.column), not COUNT(*), to get 0 for no matches.
• Wrap sums of the right side in COALESCE(…, 0).
• RIGHT JOIN is LEFT JOIN with the tables swapped; prefer LEFT JOIN for readability.

Common mistakes

• Using COUNT(*) after a LEFT JOIN and getting 1 instead of 0 for rows with no match.
• Adding WHERE right_table.column = …, which throws the NULL rows away and turns the LEFT JOIN back into an INNER JOIN (see Join traps).
• Putting the tables the wrong way round, so the side you wanted to keep is on the right.

Interview answer

A LEFT JOIN returns every row from the left table and the matching rows from the right; where there is no match, the right-hand columns are NULL. I use it whenever the question says "all" or "every" — every customer, including those with no orders. After a LEFT JOIN I count a right-hand column rather than COUNT star, so unmatched rows give zero, and I COALESCE sums to zero.

Follow-up questions

Finding what is missing: anti-joins

Must know

An anti-join returns rows from one table that have no match in another — written as LEFT JOIN … WHERE right.key IS NULL, or as WHERE NOT EXISTS (…).

SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON b.key = a.key
WHERE b.key IS NULL;

Must-know points

• Anti-join = rows with no match: LEFT JOIN … WHERE right.pk IS NULL, or NOT EXISTS.
• Semi-join = rows with at least one match, each once: EXISTS or IN.
• In the IS NULL test, use a column that is never NULL in a real match — the right table's primary key or join key.
• NOT EXISTS is the safest anti-join: it handles NULLs correctly, and optimisers run it efficiently.
• Don't use NOT IN (subquery) if the subquery can return NULL.

Common mistakes

• Testing a nullable column (WHERE o.amount IS NULL), which also returns Lena because order 106 has no amount.
• Using INNER JOIN and wondering why the missing rows never appear.
• Using NOT IN (SELECT dept_id FROM employees): Gus's NULL dept_id makes it return no rows at all.

Interview answer

To find rows with no match I use an anti-join: either a LEFT JOIN with WHERE the right table's key IS NULL, or NOT EXISTS with a correlated subquery. I prefer NOT EXISTS because it states the intent directly and is NULL-safe, and I avoid NOT IN against a subquery, since a single NULL in that list makes the whole condition unknown and returns nothing.

Follow-up questions

Self joins: a table joined to itself

Must know

A self join joins a table to itself under two different aliases, so a row can be compared with another row of the same table — such as an employee with their manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

Must-know points

• A self join uses the same table twice with two aliases.
• Read the ON condition carefully: m.emp_id = e.manager_id means "m is e's manager".
• Use LEFT JOIN to keep rows whose pointer is NULL (the top of the tree).
• a.id < b.id gives each pair once; a.id <> b.id gives each pair twice.
• A self join reaches one level. For a whole chain of managers, use a recursive CTE (Phase 4).

Common mistakes

• Swapping the condition (e.emp_id = m.manager_id), which lists each person's reports instead of their manager.
• Forgetting prefixes, so name is ambiguous.
• Using INNER JOIN and losing the CEO.

Interview answer

A self join joins a table to itself with two aliases so I can compare rows within it — the classic case is employees and managers through a self-referencing manager_id. I join the manager alias's primary key to the employee alias's manager_id, use a LEFT JOIN if the top of the hierarchy must appear, and for pair comparisons I add id less-than id to avoid self-pairs and mirrored duplicates.

Follow-up questions

Joining three or more tables

Must know

You join more than two tables by adding one JOIN … ON per table, walking along the keys — orders to customers, orders to order_items, order_items to products — so each row can hold columns from all of them.

SELECT ...
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products p     ON p.product_id  = oi.product_id;

Must-know points

• One JOIN … ON per extra table; each ON links the new table to one already joined.
• Follow the keys in the diagram. Many-to-many goes through the link table.
• Know the grain after every join: joining a "many" table multiplies rows.
• One INNER JOIN in a chain can drop rows; switch to LEFT JOIN where rows must survive.
• Use clear aliases (c, o, oi, p) and line the ON conditions up so the chain is easy to read.

Common mistakes

• Joining two tables on the wrong pair of columns (oi.product_id = o.order_id) — it runs but returns nonsense.
• Mixing LEFT and INNER joins so that a later INNER JOIN removes the rows the LEFT JOIN kept.
• Summing an order-level column (o.amount) after joining item rows, which counts it once per item (see Join traps).

Interview answer

For multi-table queries I follow the foreign keys one hop at a time, adding a JOIN with its own ON condition per table, and I keep track of the grain after each join — joining a child table like order_items turns one row per order into one row per item. For many-to-many I go through the junction table, and I use LEFT JOINs wherever rows without a match must survive.

Follow-up questions

FULL OUTER JOIN and CROSS JOIN

Should know

A FULL OUTER JOIN keeps every row from both tables, with NULLs wherever either side has no match; a CROSS JOIN pairs every row of one table with every row of the other.

SELECT ...
FROM a FULL OUTER JOIN b ON b.key = a.key;

SELECT ...
FROM a CROSS JOIN b;

Must-know points

• FULL OUTER JOIN = matches + unmatched left rows + unmatched right rows.
• CROSS JOIN has no ON and returns rows(a) × rows(b).
• Grid pattern: CROSS JOIN the dimensions, LEFT JOIN the facts, COALESCE to 0.
• A join with a missing or always-true ON condition is an accidental cross join.

Common mistakes

• Running a CROSS JOIN between two large tables.
• Forgetting an ON condition and getting a cross join by accident — the row count explodes.
• Using WHERE on one side after a FULL OUTER JOIN, which removes the other side's unmatched rows.

Interview answer

A FULL OUTER JOIN returns matched rows plus the unmatched rows from both sides, with NULLs filling the gaps — useful for reconciling two sources. A CROSS JOIN is the Cartesian product, every row paired with every row; I mostly use it to build a complete grid such as every customer by every month, then LEFT JOIN the facts onto it so missing combinations show as zero instead of disappearing.

Follow-up questions

Join traps: double counting, and ON versus WHERE

Must know

The two classic join bugs are fan-out — joining a "many" table repeats the "one" side, so its numbers get summed several times — and putting a right-table filter in WHERE, which silently turns a LEFT JOIN into an INNER JOIN.

-- keep all left rows, filter which right rows match:
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'

-- aggregate the many side first, then join:
JOIN (SELECT order_id, COUNT(*) AS items FROM order_items GROUP BY order_id) i
  ON i.order_id = o.order_id

Must-know points

• After every join, ask: what does one row mean now? Did the row count grow?
• Never sum a "one"-side column after joining a "many" table. Sum first, or pre-aggregate the many side.
• Using DISTINCT to hide fan-out hides the bug, not fixes it — sums can still be wrong.
• With a LEFT JOIN, conditions on the right table go in ON; conditions on the left table go in WHERE.
• With an INNER JOIN, ON and WHERE give the same result.

Common mistakes

• SUM(order.amount) after joining order_items.
• COUNT(*) after a fan-out join, counting items instead of orders. Use COUNT(DISTINCT o.order_id).
• LEFT JOIN … WHERE right.col = x in an "all customers" report.

Interview answer

The two join bugs I always check for are fan-out and misplaced filters. Fan-out happens when a one-to-many join repeats the parent row, so summing a parent column double-counts — I fix it by aggregating the child table to the parent's grain before joining. And with a LEFT JOIN, a filter on the right table belongs in the ON clause; in WHERE it discards the NULL-extended rows and silently becomes an inner join.

Follow-up questions

Subqueries: a query inside a query

Must know

A subquery is a SELECT inside another query, in brackets: it can return one value (to compare with), a list (for IN), or a whole table (to select FROM).

WHERE column > (SELECT one_value FROM ...)
WHERE column IN (SELECT one_column FROM ...)
FROM (SELECT ... ) AS alias

Must-know points

• A subquery is a query in brackets, run to feed the outer query.
• Scalar subquery: exactly one value. If it returns more than one row, the query fails.
• IN subquery: one column, any number of rows.
• Derived table in FROM: give it an alias. Use it to aggregate an aggregate or to filter on a calculated column.
• Most subqueries can also be written as a join or a CTE; pick whichever reads most clearly.

Common mistakes

• A scalar subquery that returns several rows ("more than one row returned by a subquery").
• Forgetting the alias on a derived table (required in PostgreSQL before version 16, MySQL and SQL Server).
• Nesting subqueries three deep. Rewrite it with CTEs so it reads top to bottom.

Interview answer

A subquery is a nested SELECT whose result the outer query uses: as a single value for comparison, as a list for IN, or as a derived table in FROM. I use derived tables when I need to aggregate in two stages — like averaging per-customer totals — and once nesting gets deeper than one level I switch to CTEs so the logic reads top to bottom.

Follow-up questions

Correlated subqueries, EXISTS, and the NOT IN trap

Must know

A correlated subquery refers to a column of the outer row, so it is worked out once per outer row; EXISTS asks "does the subquery return any row at all?", which makes it the safe way to test for a match.

WHERE EXISTS (
  SELECT 1 FROM other o
  WHERE o.key = outer_table.key
)

Must-know points

• A correlated subquery uses outer-row columns; logically it runs once per outer row.
• EXISTS is true when the subquery returns at least one row; SELECT 1 inside is a convention.
• NOT EXISTS is the NULL-safe "no match" test. Prefer it to NOT IN.
• x NOT IN (… NULL …) is never true. x IN (… NULL …) still works for the values that match.
• Correlated subqueries are clear for "compare with my own group"; window functions (Phase 5) often do the same faster.

Common mistakes

• Using NOT IN against a column that can be NULL.
• Forgetting the correlation (WHERE o2.customer_id = o.customer_id), which compares every row with the overall maximum instead.
• Thinking EXISTS cares what the subquery selects. It only checks whether a row exists.

Interview answer

A correlated subquery references the outer query's row, so logically it is evaluated per row — useful for comparisons against a row's own group, like each customer's latest order. EXISTS tests whether a subquery returns any row, and NOT EXISTS is my default anti-join, because NOT IN returns nothing if the subquery yields a single NULL: x not in a list containing NULL can never be true.

Follow-up questions

CTEs: naming each step with WITH

Must know

A CTE (common table expression) is a named, temporary result written at the top of a query with WITH name AS (…), which later parts of the query can use like a table.

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ...
FROM step_two;

Must-know points

• WITH name AS (query) defines a CTE; separate several with commas, and use WITH only once.
• A CTE can use the CTEs defined above it, and the main query can use any of them, more than once.
• A CTE lasts for one statement only.
• Use CTEs to make the steps of a hard query readable — interviewers love seeing this.
• Build and test one CTE at a time.

Common mistakes

• Writing WITH before every CTE. It is WITH a AS (…), b AS (…).
• A comma after the last CTE, before the main SELECT.
• Expecting a CTE to exist in the next query. It is gone after the statement ends.

Interview answer

A CTE is a named subquery defined with WITH at the top of a statement, which the rest of the query can reference like a table, even multiple times. I use CTEs to break a hard problem into steps — filter, aggregate, rank, then select — which makes the query easy to read and to debug one step at a time. Performance is usually the same as the equivalent subquery.

Follow-up questions

Recursive CTEs: walking trees and filling in dates

Should know

A recursive CTE refers to itself: an anchor query gives the starting rows, and a recursive part, joined with UNION ALL, keeps adding rows from the previous round until no new rows appear.

WITH RECURSIVE tree AS (
  SELECT ... FROM t WHERE <start>            -- anchor
  UNION ALL
  SELECT ... FROM t JOIN tree ON <next step>  -- recursive part
)
SELECT * FROM tree;

Must-know points

• Shape: WITH RECURSIVE name AS (anchor UNION ALL recursive part).
• The recursive part joins the table to the CTE itself, using only the rows found in the previous round.
• There must be a stop rule — a WHERE that eventually returns no new rows.
• Carry extra columns (level, a path string) to know how deep each row is.
• Common uses: org charts, category or folder trees, bill of materials, and generating number or date series.

Common mistakes

• No stop rule, so it runs until the database gives up.
• Using UNION instead of UNION ALL by habit. UNION ALL is the normal choice; UNION removes duplicates and can stop cycles, but costs more.
• Joining the wrong way round (chart.manager_id = e.emp_id), which walks up the tree instead of down.

Interview answer

A recursive CTE has an anchor member that produces the starting rows and a recursive member, unioned with UNION ALL, that joins back to the CTE to produce the next level, repeating until no new rows appear. I use it for hierarchies of unknown depth, like an org chart with a level column, and for generating date series to fill gaps — always with a clear termination condition, and a depth limit if the data might contain cycles.

Follow-up questions

UNION, UNION ALL, INTERSECT and EXCEPT

Should know

Set operators stack the results of two queries: UNION ALL keeps every row, UNION removes duplicates, INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.

SELECT col_a, col_b FROM table_one
UNION ALL
SELECT col_x, col_y FROM table_two
ORDER BY col_a;

Must-know points

• Both queries need the same number of columns, in the same order, with compatible types.
• UNION ALL keeps duplicates and is faster; UNION removes them.
• Use UNION ALL unless you really need duplicates removed.
• INTERSECT and EXCEPT also remove duplicates from their result.
• One ORDER BY, at the end, sorts the combined result; column names come from the first query.

Common mistakes

• Using UNION when the two sets can't overlap — paying for duplicate removal you don't need.
• Putting ORDER BY on the first query instead of at the end.
• Lining columns up in a different order in the second query: they are matched by position, not by name.

Interview answer

Set operators combine two result sets vertically, matching columns by position. UNION ALL appends everything; UNION also removes duplicate rows, which costs a sort or hash, so I default to UNION ALL unless duplicates must go. INTERSECT returns rows present in both results and EXCEPT returns rows in the first but not the second — a neat alternative to an anti-join when comparing id lists.

Follow-up questions

What a window function is: OVER and PARTITION BY

Must know

A window function calculates a value for each row from a set of related rows (its "window"), defined by OVER (PARTITION BY … ORDER BY …), and — unlike GROUP BY — keeps every row in the result.

function(...) OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

Must-know points

• A window function keeps every row; GROUP BY collapses rows.
• PARTITION BY = which rows share a window. No PARTITION BY = one window of all rows.
• ORDER BY inside OVER = the order within each window (needed for ranks, LAG and running totals).
• Window functions run in the SELECT step: you can't use them in WHERE, GROUP BY or HAVING. Wrap in a CTE to filter.
• Any aggregate becomes a window function when you add OVER.

Common mistakes

• Writing WHERE ROW_NUMBER() OVER (…) = 1 — not allowed. Filter in an outer query.
• Adding GROUP BY when you wanted every row kept.
• Confusing the ORDER BY inside OVER (order within the window) with the query's ORDER BY (order of the output).

Interview answer

A window function computes a value over a set of rows related to the current row, defined by the OVER clause — PARTITION BY says which rows share the window and ORDER BY orders them — but unlike GROUP BY it doesn't collapse rows, so each row keeps its detail and gains the computed value. Because they are evaluated in the SELECT phase, I filter on them from an outer query or CTE.

Follow-up questions

Ranking: ROW_NUMBER, RANK and DENSE_RANK

Must know

ROW_NUMBER numbers rows 1, 2, 3, 4 with no ties; RANK gives ties the same number and then skips (1, 2, 2, 4); DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC, tiebreaker)
RANK()       OVER (PARTITION BY g ORDER BY x DESC)
DENSE_RANK() OVER (PARTITION BY g ORDER BY x DESC)

Must-know points

• ROW_NUMBER: unique 1, 2, 3, 4. Add a tiebreaker so it is the same every run.
• RANK: ties share a number, then a gap (1, 2, 2, 4).
• DENSE_RANK: ties share a number, no gap (1, 2, 2, 3). Use it for "Nth highest value".
• PARTITION BY restarts the numbering per group.
• Pattern for top N per group: rank in a CTE, then WHERE rank <= N outside.

Common mistakes

• Using ROW_NUMBER for "top 3 salaries" when ties should all be included — use RANK or DENSE_RANK.
• Using RANK for "the 2nd highest salary" — after a tie at 1st, there is no rank 2.
• Forgetting the tiebreaker in ROW_NUMBER, so results change from run to run.

Interview answer

All three number rows in the order given by the OVER clause's ORDER BY, optionally restarting per PARTITION BY. ROW_NUMBER gives unique sequential numbers, so I add a deterministic tiebreaker; RANK gives tied rows the same rank and leaves gaps; DENSE_RANK gives the same rank without gaps. For "Nth highest distinct value" I use DENSE_RANK, and for "exactly one row per group" I use ROW_NUMBER — always filtering in an outer query.

Follow-up questions

LAG and LEAD: comparing with the previous or next row

Must know

LAG(column) returns the value from the previous row and LEAD(column) the value from the next row, in the order set by OVER (… ORDER BY …), so a row can be compared with its neighbour.

LAG(column, offset, default)  OVER (PARTITION BY g ORDER BY d)
LEAD(column, offset, default) OVER (PARTITION BY g ORDER BY d)

Must-know points

• LAG = previous row's value; LEAD = next row's value, in the window's ORDER BY.
• The first row (LAG) or last row (LEAD) of each partition gets NULL, or the default you pass.
• LAG(x, n) looks n rows back — 12 for "same month last year" in monthly data.
• PARTITION BY keeps each group's comparisons inside the group.
• LAG looks at the previous row, not the previous date: fill missing dates first if gaps matter.

Common mistakes

• Forgetting PARTITION BY, so one customer's first order is compared with another customer's last.
• Assuming the previous row is the previous day or month when there are gaps.
• Dividing by LAG without guarding against NULL or zero.

Interview answer

LAG and LEAD return a value from a preceding or following row within the window, in the order given by the OVER clause's ORDER BY. I use them for period-over-period changes — revenue minus LAG of revenue — and gaps between events per user with PARTITION BY user. I watch out for the first row returning NULL and for missing periods, since LAG goes to the previous row, which isn't necessarily the previous calendar month.

Follow-up questions

Running totals and moving averages: window frames

Must know

With ORDER BY inside OVER, SUM and AVG become running totals; a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the window to nearby rows, giving a moving average.

SUM(x) OVER (ORDER BY d)                                   -- running total
AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)          -- running total, row by row

Must-know points

• Aggregate + OVER (ORDER BY …) = a running total (or running count, max…).
• The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — tied rows are included together.
• Write ROWS explicitly for row-by-row running totals and for moving averages.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW = the last n + 1 rows.
• Without ORDER BY in OVER, the frame is the whole partition, so you get the total, not a running total.

Common mistakes

• Getting the same running total on tied rows and not knowing why (the RANGE default).
• A "7-day" moving average with ROWS 6 PRECEDING when some days are missing — that is 7 rows, not 7 days.
• Forgetting PARTITION BY, so one customer's running total continues into the next customer's.

Interview answer

Adding ORDER BY to an aggregate's OVER clause makes it cumulative, which gives running totals; a frame clause like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW narrows it to a sliding window for moving averages. The subtle point is the default frame, RANGE up to the current row, which lumps tied ORDER BY values together — so I specify ROWS explicitly, with a unique tiebreaker, whenever ties are possible.

Follow-up questions

Share of total, NTILE and FIRST_VALUE

Should know

x / SUM(x) OVER () gives each row's share of the total; NTILE(n) splits ordered rows into n roughly equal buckets; FIRST_VALUE and LAST_VALUE pull a value from the first or last row of the window.

100.0 * x / SUM(x) OVER (PARTITION BY g)
NTILE(4) OVER (ORDER BY x DESC)
FIRST_VALUE(col) OVER (PARTITION BY g ORDER BY x DESC)

Must-know points

• SUM(x) OVER () = grand total on every row; SUM(x) OVER (PARTITION BY g) = group total.
• Share of total: 100.0 * x / SUM(x) OVER (…) — the 100.0 avoids integer division.
• NTILE(n) makes n buckets as equal in size as possible, in the window's order.
• FIRST_VALUE is safe with the default frame; LAST_VALUE needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• You can use a window function over an aggregate (SUM(SUM(x)) OVER ()) in a GROUP BY query, but a CTE is easier to read.

Common mistakes

• Integer division in a share: revenue / SUM(revenue) OVER () gives 0 for every row.
• Using LAST_VALUE with the default frame and getting the current row's value.
• Expecting NTILE to keep tied values in the same bucket.

Interview answer

For share-of-total I divide each row by SUM over an empty or partitioned window, multiplying by 100.0 to avoid integer division. NTILE buckets ordered rows into n near-equal groups for quartiles or deciles, and FIRST_VALUE picks the first row's value in each window — for LAST_VALUE I widen the frame to unbounded following, because the default frame stops at the current row.

Follow-up questions

CREATE TABLE and ALTER TABLE

Must know

CREATE TABLE defines a new table — its name, its columns, their types and rules — and ALTER TABLE changes an existing table, for example by adding a column.

CREATE TABLE table_name (
  id     INTEGER PRIMARY KEY,
  column TYPE NOT NULL,
  other  TYPE DEFAULT value
);
ALTER TABLE table_name ADD COLUMN new_column TYPE;

Must-know points

• CREATE TABLE: name, then each column with its type and rules, separated by commas.
• Give every table a primary key.
• DEFAULT fills a column when an INSERT leaves it out.
• ALTER TABLE ADD / RENAME / DROP COLUMN changes the structure of an existing table.
• CREATE, ALTER and DROP are DDL: they change the schema, not the data.

Common mistakes

• No primary key, so duplicate rows can't be told apart or removed cleanly.
• Adding a NOT NULL column without a DEFAULT to a table that already has rows — most databases refuse.
• Storing several values in one column ('Pen, Mug'). Each column should hold one value — see normalisation.

Interview answer

CREATE TABLE defines a table's columns, data types and constraints — a primary key, NOT NULL, defaults, foreign keys — and ALTER TABLE evolves it later, such as adding a column. I always give tables a primary key, choose types deliberately — DECIMAL for money, proper DATE types for dates — and treat schema changes on big tables carefully, because adding or changing columns can rewrite or lock the table.

Follow-up questions

Constraints: the rules that keep bad data out

Must know

Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.

email   TEXT NOT NULL UNIQUE,
price   INTEGER CHECK (price > 0),
dept_id INTEGER REFERENCES departments(dept_id) ON DELETE SET NULL

Must-know points

• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT are the six to know.
• PRIMARY KEY = UNIQUE + NOT NULL, one per table.
• Foreign key delete options: refuse (default), ON DELETE CASCADE, ON DELETE SET NULL.
• A failed constraint fails the whole statement; nothing is half-written.
• Put rules in the database, not only in the app — the database sees every write.

Common mistakes

• Relying on the app alone to prevent duplicates (that is how Tom signed up twice).
• Using ON DELETE CASCADE without thinking: deleting one customer silently deletes all their orders.
• Expecting UNIQUE to stop two NULLs. In most databases it doesn't — NULL isn't equal to NULL.

Interview answer

Constraints let the database enforce integrity on every write: NOT NULL for required values, UNIQUE and PRIMARY KEY for identity, FOREIGN KEY for valid references with a chosen delete behaviour, and CHECK for any rule on a row. I put these rules in the schema rather than only in application code, because the database sees every writer, and a violation fails the statement atomically instead of leaving bad data behind.

Follow-up questions

INSERT, UPDATE and DELETE

Must know

INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows — and both UPDATE and DELETE act on every row their WHERE matches, or on every row of the table if there is no WHERE.

INSERT INTO t (col1, col2) VALUES (v1, v2);
UPDATE t SET col1 = new_value WHERE condition;
DELETE FROM t WHERE condition;

Must-know points

• Always list the columns in INSERT; don't rely on the table's column order.
• UPDATE and DELETE without WHERE change every row.
• Run the WHERE as a SELECT first, and check how many rows it hits.
• INSERT INTO t (…) SELECT … copies rows from a query.
• An upsert inserts or updates in one atomic statement.

Common mistakes

• Forgetting WHERE on an UPDATE or DELETE.
• Deleting parent rows that child rows still reference — the foreign key refuses, or cascades if set to.
• Updating with a value computed from another table without checking the join returns one row per target row.

Interview answer

INSERT adds rows, ideally with an explicit column list; UPDATE changes columns in rows matched by WHERE, and DELETE removes matched rows. My safety habits are to run the WHERE as a SELECT first, wrap risky changes in a transaction so I can roll back, and use an upsert — insert on conflict do update — when a row may or may not exist, so it is a single atomic statement rather than a check-then-write race.

Follow-up questions

Transactions and ACID

Must know

A transaction groups several statements into one all-or-nothing unit — BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them — and ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.

BEGIN;
UPDATE ... ;
UPDATE ... ;
COMMIT;      -- or ROLLBACK;

Must-know points

• BEGIN … COMMIT makes several statements one all-or-nothing unit; ROLLBACK cancels it.
• Atomic: all or nothing. Consistent: constraints always hold. Isolated: concurrent transactions don't interfere. Durable: committed means saved.
• Isolation levels trade safety for speed: READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Anomalies the levels prevent: dirty reads, non-repeatable reads, phantom reads.
• Keep transactions short; long ones hold locks and block others.
• Locks, write skew and transactions that span several services (sagas) are in the System Design tab, Phases 3 and 4.

Common mistakes

• Running related changes without a transaction, so a failure halfway leaves the data inconsistent.
• Leaving a transaction open (forgetting COMMIT), which holds locks.
• Assuming the default isolation level prevents every race. Two transactions can still both read a balance and both write it.

Interview answer

A transaction groups statements into an atomic unit — BEGIN, changes, then COMMIT or ROLLBACK. ACID means atomicity, all or nothing; consistency, constraints hold before and after; isolation, concurrent transactions don't see each other's uncommitted work; and durability, committed changes survive crashes. Isolation is tunable — read committed, repeatable read, serializable — trading anomalies like non-repeatable or phantom reads against concurrency, so for something like a balance update I lock the row with SELECT FOR UPDATE or use serializable.

Follow-up questions

Normalisation: designing tables that don't contradict themselves

Must know

Normalisation is designing tables so each fact is stored in exactly one place — usually to third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.

Must-know points

• 1NF: one value per cell; no lists, no repeating column groups.
• 2NF: 1NF, and no column depends on just part of a composite key.
• 3NF: 2NF, and no column depends on another non-key column.
• Normalised data avoids update, insert and delete anomalies; the cost is more joins.
• Denormalise on purpose (reporting tables, caches) when reads matter more than keeping one copy.

Common mistakes

• Storing comma-separated lists in a column.
• Copying a value (like a price) into another table without deciding whether it should change when the source changes.
• Normalising so far that simple questions need eight joins. 3NF is usually the right stop.

Interview answer

Normalisation organises tables so each fact is stored once, which prevents update, insert and delete anomalies. First normal form means atomic values with no repeating groups; second, no partial dependency on part of a composite key; third, no transitive dependency through a non-key column — every column depends on the key, the whole key and nothing but the key. I normalise transactional systems to about 3NF and denormalise deliberately for analytics or read-heavy paths.

Follow-up questions

Views: saved queries you can use like tables

Should know

A view is a named, saved SELECT that you query like a table; it stores no data of its own, so it always shows the current data, while a materialised view stores the result and must be refreshed.

CREATE VIEW view_name AS
SELECT ...;

SELECT * FROM view_name WHERE ...;

Must-know points

• A view is a saved query, used like a table. It stores no rows.
• Views give one shared definition of a metric, and can hide sensitive columns.
• A view is as fast as its underlying query — it doesn't speed things up.
• A materialised view stores the result: fast reads, stale until refreshed.

Common mistakes

• Expecting a normal view to make a slow query fast.
• Stacking views on views until no one can tell what a query really does.
• Forgetting to refresh a materialised view and reporting old numbers.

Interview answer

A view is a stored query that behaves like a virtual table: it holds no data, so it is always current, and it is useful for sharing one definition of a metric or for restricting which columns people see. It doesn't make queries faster. When the query is expensive and slight staleness is fine, I use a materialised view, which stores the result and is refreshed on a schedule.

Follow-up questions

DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP

Should know

SQL commands fall into four families — DDL changes structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK) — and DELETE, TRUNCATE and DROP remove rows, all rows, or the whole table.

DELETE FROM t WHERE condition;   -- DML: chosen rows
TRUNCATE TABLE t;                -- DDL: all rows, fast
DROP TABLE t;                    -- DDL: the table itself

Must-know points

• DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE. DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT.
• DELETE: chosen rows, WHERE allowed, row by row, fires triggers, can be rolled back.
• TRUNCATE: every row, no WHERE, very fast, usually resets auto-numbering.
• DROP: the table and everything about it.
• TRUNCATE and DROP are refused if other tables' foreign keys point at the table (unless you CASCADE).

Common mistakes

• Using TRUNCATE when you meant to keep some rows — it has no WHERE.
• Assuming TRUNCATE can always be rolled back — not in MySQL or Oracle.
• Dropping a table that views or foreign keys still depend on.

Interview answer

SQL commands split into DDL for structure, DML for data, DCL for permissions and TCL for transactions. DELETE is DML: it removes rows matching a WHERE, logs each row, fires triggers and can be rolled back. TRUNCATE removes all rows at once — much faster, no WHERE, often resets identity counters, and in some databases can't be rolled back. DROP removes the table definition along with its data, indexes and permissions.

Follow-up questions

Indexes: how a B-tree makes lookups fast

Must know

An index is a separate, sorted copy of one or more columns — usually a B-tree — with pointers back to the rows, so the database can jump to matching rows instead of scanning the whole table.

CREATE INDEX index_name ON table_name (column);
CREATE UNIQUE INDEX index_name ON table_name (column);
DROP INDEX index_name;

Must-know points

• An index is a sorted structure (usually a B-tree) pointing back to the rows.
• It helps WHERE, JOIN … ON, ORDER BY and GROUP BY on the indexed columns.
• Primary keys and UNIQUE constraints get an index automatically; foreign keys usually don't — index them yourself.
• Every index slows down writes and uses space. Index the columns you filter and join on.
• An index helps most when it narrows the search to a small share of the rows (high selectivity).
• B-trees versus LSM trees, and indexing at scale, are in the System Design tab, Phase 3.

Common mistakes

• Indexing every column "just in case", which slows every write.
• Forgetting to index foreign keys, so joins and deletes on the parent table are slow.
• Indexing a column with only a few values (like status) and expecting it to help a query that matches half the table.

Interview answer

An index is a separate sorted structure, typically a B-tree, mapping column values to row locations, so the database can find matching rows in logarithmic time instead of scanning the table. I index columns used in selective WHERE filters, join keys — especially foreign keys — and ORDER BY, and I keep in mind the trade-off: every index adds work to inserts, updates and deletes, and uses storage.

Follow-up questions

Reading a query plan with EXPLAIN

Should know

EXPLAIN shows the plan the database chose for a query — which tables it scans, which indexes it searches, how it joins and sorts — without (or, with ANALYZE, while) running it.

EXPLAIN QUERY PLAN SELECT ...;   -- SQLite
EXPLAIN SELECT ...;              -- PostgreSQL, MySQL: the plan
EXPLAIN ANALYZE SELECT ...;      -- PostgreSQL: run it, real timings

Must-know points

• EXPLAIN shows the chosen plan; EXPLAIN ANALYZE (PostgreSQL) runs it and shows real times and rows.
• SCAN = read everything; SEARCH / Index Scan = jump via an index.
• Sorts and temp B-trees for ORDER BY, GROUP BY or DISTINCT are expensive on big data.
• In a join plan, the first table is the outer loop; the inner table should be searched by index.
• Big gaps between estimated and actual row counts point to stale statistics (run ANALYZE).

Common mistakes

• Tuning by guesswork instead of reading the plan.
• Judging a plan on a tiny test table — the optimiser may choose differently on real data.
• Running EXPLAIN ANALYZE on a DELETE or UPDATE in production. It really runs the statement.

Interview answer

My first step with a slow query is EXPLAIN, or EXPLAIN ANALYZE in PostgreSQL to see real timings and row counts. I look for sequential scans on large tables, expensive sorts, nested loops over big inputs, and estimates that are far from actual counts. Typical fixes are an index on the filter or join columns, rewriting a non-sargable condition, filtering earlier, or refreshing statistics — then I re-check the plan.

Follow-up questions

Index-friendly queries: sargable filters and composite indexes

Should know

A filter is sargable when the database can use an index for it — the indexed column stands alone on one side of the comparison — and a composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone.

-- not sargable: the column is inside a function
WHERE strftime('%Y-%m', order_date) = '2024-02'
-- sargable: the bare column, compared with a range
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01'

CREATE INDEX idx ON orders (customer_id, order_date);

Must-know points

• Keep the indexed column bare: no functions, no maths, no leading % in LIKE.
• Rewrite "same month" or "same year" filters as half-open ranges: >= start AND < next start.
• A composite index (a, b) serves filters on a, or a and b — not b alone (leftmost prefix).
• Put the column you test with = first, and the range column after it.
• A covering index holds every column a query needs, so the table isn't read at all.

Common mistakes

• WHERE YEAR(order_date) = 2024 on an indexed date column.
• Creating (order_date, customer_id) when the queries filter by customer first.
• Comparing a column with a value of another type (a number column with '42'), which can force a conversion on every row.

Interview answer

An index can only be used when the predicate is sargable — the indexed column appears bare, compared with a constant or range — so I avoid functions or arithmetic on indexed columns and rewrite things like YEAR(date) = 2024 as a date range. For composite indexes the leftmost-prefix rule applies: an index on customer_id then order_date serves filters on customer, or customer and date, but not date alone, so I put equality columns first and range columns last, and make the index covering when a hot query only needs a few columns.

Follow-up questions

How a database runs a join: nested loop, hash join, merge join

Bonus

Databases use three main join methods — a nested loop (for each row of one table, look up matches in the other), a hash join (build a hash table of one side, probe it with the other), and a merge join (walk two sorted inputs together) — and the optimiser picks one per join.

Must-know points

• Nested loop: best for a small outer input with an index on the inner join key.
• Hash join: best for large, unsorted equality joins; needs memory for the hash table.
• Merge join: best when both inputs are already sorted on the join key.
• The optimiser chooses using row-count estimates; stale statistics cause bad choices.
• Indexing join keys (especially foreign keys) is the single most useful join optimisation.

Common mistakes

• Thinking an index always makes a join faster: for a hash join over whole tables it may not be used at all.
• Ignoring a nested loop over two large tables without an index — it grows as N × M.
• Forcing a join method with hints instead of fixing the statistics or indexes.

Interview answer

There are three main physical join algorithms. Nested loop iterates the outer input and looks up matches in the inner — ideal when the outer side is small and the inner join key is indexed. Hash join builds an in-memory hash table on the smaller input and probes it with the larger — ideal for big equality joins without useful indexes. Merge join walks two inputs sorted on the key. The optimiser chooses based on estimated cardinalities, so bad statistics are a common cause of a poor join plan.

Follow-up questions

How to answer any SQL interview question

Must know

A repeatable method: restate the question, look at the tables and their grain, ask about edge cases, plan the steps out loud, write the query as small CTEs, then check the result against a few rows you can work out by hand.

-- 1 clarify  2 look at the grain  3 plan out loud
WITH step_one AS (...),   -- 4 one CTE per step
     step_two AS (...)
SELECT ... FROM step_two; -- 5 check against rows you know

Must-know points

• Clarify first: ties, NULLs, duplicates, date ranges, and what "per" means.
• Know each table's grain and keys before joining.
• Think out loud — the interviewer is grading your reasoning.
• Write CTEs, one step each, named after what they hold.
• Verify: a row count, one total worked out by hand, and the edge cases said out loud.

Common mistakes

• Typing straight away and writing one huge nested query.
• Not asking about ties, then using LIMIT 1 when two rows tie.
• Stopping when the query runs, without checking that the answer is right.

Interview answer

I start by restating the question and asking about edge cases — ties, NULLs, duplicates, the time window. Then I look at the tables to understand the grain and how they join, explain my plan in steps, and write it as a chain of CTEs so each step is readable and testable. Finally I sanity-check the output — row counts and one number I can verify by hand — and walk through how the query handles the edge cases.

Follow-up questions

Quick-fire SQL theory questions

Must know

Short "what is the difference between…" questions that open many SQL interviews; each is best answered with a one-line definition of both sides, the key difference, and when you would use each.

Must-know points

• Shape: define both, the key difference, when to use each.
• Keep it under thirty seconds, then stop.
• Use a tiny example from tables you know, like employees and orders.
• Revise the follow-up questions below out loud until each answer is automatic.

Common mistakes

• Rambling for two minutes on a thirty-second question.
• Giving only one side ("HAVING is for groups") without the contrast.
• Memorising words without the reason, then freezing when the question is rephrased.

Interview answer

For any "what's the difference" question I give a one-line definition of each, the difference that matters in practice, and when I would choose each. For example: WHERE filters rows before grouping and can't use aggregates; HAVING filters groups after GROUP BY and can — so row conditions go in WHERE and aggregate conditions in HAVING.

Follow-up questions

The second highest salary (and the Nth)

Must knowInterview problemEasy

Return the second highest salary from employees. If there is no second highest salary, return NULL.

The pattern: Nth highest value

The words "second highest", "Nth largest", "third most recent". The answer is a value, not a person, so ties must collapse: think DENSE_RANK or DISTINCT.

The approach

1. Ask: if two people tie for the highest salary, is the "second highest" the next different amount? (Usually yes.)
2. Rank the distinct salaries from highest down, so ties share a rank and no number is skipped.
3. Keep the salary ranked 2.
4. Wrap it so that "no such salary" gives one row containing NULL, not an empty result — an aggregate like MAX does this.

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT MAX(salary) AS second_highest
FROM ranked
WHERE rnk = 2;

Common mistakes

• ORDER BY salary DESC LIMIT 1 OFFSET 1 without DISTINCT: returns the top salary again when two people tie for first.
• Using RANK: after a tie for first, there is no rank 2.
• Returning an empty result when the question asked for NULL.

Interview answer

I first confirm that ties should collapse, so the second highest is the second different amount. Then I rank salaries with DENSE_RANK in descending order — dense so a tie doesn't skip a number — and take rank 2. I wrap it in MAX so that if there is no second salary I return a single NULL instead of no rows, and for the Nth highest I just change the 2.

Follow-up questions

Employees who earn more than their manager

Must knowInterview problemEasy

Find every employee who earns more than their own manager. Show the employee's name and salary, and the manager's name and salary.

The pattern: Self join

"Their manager", "their parent", "the person who referred them" — a column pointing at another row of the same table.

The approach

1. Notice that manager_id points to another row of employees.
2. Use the table twice: e for the employee, m for the manager.
3. Join with m.emp_id = e.manager_id (m is e's manager).
4. Keep rows where e.salary > m.salary. An inner join is fine here: people with no manager can't earn more than one.

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.name;

Common mistakes

• Joining the wrong way round (e.emp_id = m.manager_id), which compares people with their reports.
• Leaving columns unprefixed, so name and salary are ambiguous.

Interview answer

manager_id references the same table, so I self-join employees as e and m, matching the manager alias's emp_id to the employee's manager_id, and filter where the employee's salary is greater than the manager's. An inner join is right here because someone without a manager can't out-earn one.

Follow-up questions

Customers who have never had an order delivered

Must knowInterview problemEasy

List every customer who has never had an order delivered — including customers who have never ordered at all.

The pattern: Rows with no match (anti-join)

"Never", "no", "without", "haven't" — you are looking for something that isn't there.

The approach

1. Start from customers, because every customer must be considered — including ones with no orders.
2. Look for a delivered order belonging to each customer.
3. Keep the customers for whom none exists: NOT EXISTS, or a LEFT JOIN with the status test inside ON and WHERE o.order_id IS NULL.
4. Check: Omar and the second Tom never ordered; Zoe only has a pending order.

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'delivered'
)
ORDER BY c.customer_id;

Common mistakes

• LEFT JOIN … WHERE o.status <> 'delivered': drops customers with no orders and keeps customers who also have delivered orders.
• WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE status = 'delivered') is fine here only because customer_id is NOT NULL in orders — say that out loud.
• Starting from orders, which can never return customers who have no orders.

Interview answer

This is an anti-join. I drive it from customers so customers with no orders are included, and keep those for which NOT EXISTS finds a delivered order. Equivalently, a LEFT JOIN with the status filter inside the ON clause and WHERE the order id IS NULL. The trap is putting the status filter in WHERE, which turns the left join into an inner join.

Follow-up questions

Find duplicate emails, then remove the duplicates

Must knowInterview problemEasy

Find every email that belongs to more than one customer, with how many sign-ups it has. Then delete the duplicates, keeping the row with the lowest customer_id.

The pattern: Duplicates (GROUP BY … HAVING COUNT(*) > 1)

"Duplicate", "appears more than once", "keep only one", "de-duplicate".

The approach

1. Group by the column that should be unique (email).
2. Keep groups with more than one row: HAVING COUNT(*) > 1.
3. To see the extra rows themselves, number the rows in each email group by customer_id and keep row numbers above 1.
4. To delete them, keep the MIN(customer_id) per email and delete everything else. Run it as a SELECT first.

SELECT email, COUNT(*) AS sign_ups
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Common mistakes

• Using WHERE COUNT(*) > 1 instead of HAVING.
• Deleting without checking the rows first with a SELECT.
• Fixing the data but not adding a UNIQUE constraint, so duplicates come straight back.

Interview answer

To find duplicates I group by the column that should be unique and keep groups with HAVING COUNT star greater than one. To remove them I number the rows within each group with ROW_NUMBER — or take the minimum id per group — and delete every row that isn't the keeper, checking it as a SELECT first. Then I'd add a unique constraint, and I'd ask whether emails need normalising for case and spaces before comparing.

Follow-up questions

The highest-paid employee in each department

Must knowInterview problemMedium

For each department, return the employee (or employees, if tied) with the highest salary. Show the department name, the employee's name and their salary.

The pattern: Top N per group

"In each", "per", "for every" together with "highest", "top 3", "most recent", "first".

The approach

1. Ask: if two people tie for top salary in a department, show both? (Yes here.)
2. Rank employees within each department by salary, highest first: RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC).
3. RANK keeps ties together; ROW_NUMBER would pick one at random.
4. Do the ranking in a CTE, then keep rank 1 outside it, and join departments for the name.

WITH ranked AS (
  SELECT d.dept_name, e.name, e.salary,
         RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary
FROM ranked
WHERE rnk = 1
ORDER BY dept_name;

Common mistakes

• Putting the window function in WHERE.
• GROUP BY dept_id with MAX(salary) and name in SELECT — the name isn't guaranteed to belong to the max.
• Using ROW_NUMBER when ties should all be returned.

Interview answer

This is top-N-per-group. I rank employees within each department with RANK partitioned by department and ordered by salary descending, in a CTE, then filter to rank one outside — window functions can't go in WHERE. I use RANK so tied top earners are all returned; if they wanted exactly one per department I'd switch to ROW_NUMBER with a tiebreaker. For "top three salaries" I'd use DENSE_RANK less than or equal to three.

Follow-up questions

Employees paid above their department's average

Should knowInterview problemMedium

List employees whose salary is above the average salary of their own department, showing that average next to them.

The pattern: Compare a row with its group

"Above the average for their…", "more than their team's…", "compared with others in the same…".

The approach

1. Work out each department's average salary.
2. Put that average next to each employee — a window function does both in one step.
3. Keep rows where salary > the department average, filtering outside the CTE.
4. Decide what to do with Gus, who has no department.

WITH with_avg AS (
  SELECT name, dept_id, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees
)
SELECT name, dept_id, salary, dept_avg
FROM with_avg
WHERE salary > dept_avg
ORDER BY dept_id, salary DESC;

Common mistakes

• WHERE salary > AVG(salary) — an aggregate in WHERE is not allowed, and it would be the company average anyway.
• Comparing with the company average when the question says "their department".
• Filtering on the window function in the same SELECT's WHERE.

Interview answer

I need each employee's department average next to their row, so I compute AVG of salary over a window partitioned by department in a CTE, and keep rows where salary exceeds it. An equivalent approach joins employees to a grouped subquery of averages per department. I'd mention the NULL-department case, since the two approaches treat it differently.

Follow-up questions

Month-over-month revenue growth

Must knowInterview problemMedium

For each month, show the revenue and the percentage change from the previous month, rounded to one decimal place.

The pattern: Period-over-period change

"Growth", "change from the previous", "month over month", "compared with last week".

The approach

1. Aggregate orders to one row per month first (a CTE).
2. Use LAG(revenue) OVER (ORDER BY month) to bring last month's revenue onto each row.
3. Growth % = 100.0 × (this − last) ÷ last. Use 100.0 to avoid integer division and NULLIF(last, 0) to avoid dividing by zero.
4. The first month has no previous month, so its growth is NULL.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

Common mistakes

• Using LAG on the order rows instead of the monthly totals.
• (revenue - prev) / prev * 100 with whole numbers: integer division gives 0 or -1.
• Grouping by order_date instead of the month.

Interview answer

I aggregate to one row per month in a CTE, then use LAG over month order to bring the previous month's revenue alongside, and compute 100.0 times the difference divided by the previous value, rounded. I guard the division with NULLIF for zero months, note that the first month is NULL, and would fill in missing months with a calendar join if gaps are possible.

Follow-up questions

Each customer's first order

Must knowInterview problemMedium

For each customer who has ordered, show their first order — its id, date and amount — and how many days after signing up they placed it.

The pattern: First (or latest) row per group

"First purchase", "most recent login", "latest status", "earliest", one per user.

The approach

1. Number each customer's orders by date: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id).
2. Add order_id as a tiebreaker in case two orders share a date.
3. Keep row number 1 in an outer query.
4. Join customers for the name and sign-up date, and work out the days between.

WITH numbered AS (
  SELECT customer_id, order_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
  FROM orders
)
SELECT c.name, n.order_id, n.order_date, n.amount,
       julianday(n.order_date) - julianday(c.signup_date) AS days_to_first_order
FROM numbered n
JOIN customers c ON c.customer_id = n.customer_id
WHERE n.rn = 1
ORDER BY c.customer_id;

Common mistakes

• GROUP BY customer_id with MIN(order_date) and amount in SELECT — the amount isn't tied to the first order.
• Forgetting PARTITION BY, so only one order in the whole table gets number 1.
• No tiebreaker in ROW_NUMBER, so the answer can change between runs.

Interview answer

For first-per-group I number rows with ROW_NUMBER partitioned by customer and ordered by date with the order id as a tiebreaker, keep row number one in an outer query, and join customers for the sign-up date to compute days to first order. I'd confirm whether cancelled orders count, and mention that a MIN-date join can return duplicates on ties while ROW_NUMBER always returns exactly one.

Follow-up questions

Streaks: orders on consecutive days (gaps and islands)

Should knowInterview problemHard

Find every streak where a customer ordered on two or more consecutive days. Show the customer, the first and last day of the streak, and its length in days.

The pattern: Gaps and islands

"Consecutive", "in a row", "streak", "uninterrupted", "longest run".

The approach

1. Reduce to one row per customer per day (DISTINCT), so two orders on one day don't break the count.
2. Number each customer's days in order with ROW_NUMBER.
3. Subtract the row number from the date (as a day number). Consecutive days give the same result — that value labels the island.
4. Group by customer and that label: MIN date, MAX date and COUNT give the streak. Keep streaks of 2 or more.

WITH days AS (
  SELECT DISTINCT customer_id, order_date
  FROM orders
),
labelled AS (
  SELECT customer_id, order_date,
         julianday(order_date)
           - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS island
  FROM days
)
SELECT c.name, MIN(l.order_date) AS streak_start, MAX(l.order_date) AS streak_end,
       COUNT(*) AS days_in_a_row
FROM labelled l
JOIN customers c ON c.customer_id = l.customer_id
GROUP BY l.customer_id, c.name, l.island
HAVING COUNT(*) >= 2
ORDER BY days_in_a_row DESC, c.name;

Common mistakes

• Forgetting DISTINCT on (customer, day).
• Subtracting the row number from a date string instead of a day number.
• Using LAG alone, which finds pairs of consecutive days but not whole streaks.

Interview answer

This is gaps and islands. I first reduce to distinct customer-days, number each customer's days with ROW_NUMBER, and subtract that number from the date: within a run of consecutive days both go up by one, so the difference is constant and labels the island. Grouping by customer and that label gives each streak's start, end and length, and HAVING keeps the ones of two days or more.

Follow-up questions

Products bought together

Should knowInterview problemMedium

Find every pair of different products that appear in the same order, and count how many orders contain each pair. List each pair once.

The pattern: Pairs within a group (self join with a < b)

"Bought together", "pairs", "in the same order / session / team", "co-occurrence".

The approach

1. Join order_items to itself on the same order_id: every product paired with every other product in that order.
2. Keep only pairs where the first product's name is less than the second's — this removes self-pairs and mirrored duplicates (A–B and B–A).
3. Join products twice to get both names.
4. Group by the pair and count the orders.

SELECT p1.product_name AS product_a, p2.product_name AS product_b,
       COUNT(*) AS orders_together
FROM order_items a
JOIN order_items b ON b.order_id = a.order_id
JOIN products p1   ON p1.product_id = a.product_id
JOIN products p2   ON p2.product_id = b.product_id
WHERE p1.product_name < p2.product_name
GROUP BY p1.product_name, p2.product_name
ORDER BY orders_together DESC, product_a, product_b;

Common mistakes

• Using <> instead of <, which lists every pair twice.
• Joining on product_id instead of order_id, which pairs a product with itself across orders.
• Counting rows when an order could contain a pair more than once — use COUNT(DISTINCT a.order_id) if so.

Interview answer

I self-join the order items table on order_id so every product is paired with every other product in the same order, filter with product a less than product b to drop self-pairs and mirrored duplicates, join product names, and count orders per pair. I'd note that the number of pairs grows quadratically with basket size, so on big data I might limit to recent orders or popular products.

Follow-up questions

Which products make up 80% of revenue? (share of total)

Should knowInterview problemMedium

Show each product's revenue, its percentage of total revenue, and the running (cumulative) percentage from the best seller down. Then list the products that bring in the first 80% of revenue.

The pattern: Share of total and cumulative share

"Percentage of total", "share", "contribution", "cumulative", "top products making up X%".

The approach

1. Total revenue per product in a CTE: SUM(quantity × price).
2. Share of total: revenue ÷ SUM(revenue) OVER (), times 100.0.
3. Cumulative share: a running SUM ordered by revenue descending, with ROWS UNBOUNDED PRECEDING, divided by the total.
4. For "the first 80%": keep products whose revenue before them is still under 80% of the total.

WITH product_revenue AS (
  SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_name
)
SELECT product_name, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total,
       ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING)
             / SUM(revenue) OVER (), 1) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC;

Common mistakes

• Integer division: revenue / SUM(revenue) OVER () is 0 without the 100.0.
• Leaving the frame as the default RANGE, so tied revenues get the same cumulative value.
• Filtering cumulative_pct <= 80, which drops the product that crosses 80% — usually it should be included.

Interview answer

I total revenue per product in a CTE, then divide each by SUM over an empty window for the share of total, and use a running SUM ordered by revenue descending with an explicit ROWS frame for the cumulative share. To list the products making up the first 80%, I keep those whose revenue before them is still under 80% of the total, which includes the product that crosses the line.

Follow-up questions

Retention: who came back within 30 days?

BonusInterview problemHard

Of the customers who placed an order, what percentage placed another order within 30 days of their first one?

The pattern: Retention (first event, then a later event within N days)

"Retention", "came back", "returned within", "repeat purchase rate", "day-7 / day-30".

The approach

1. Find each customer's first order date (MIN per customer).
2. For each customer, check whether any later order falls within 30 days of that first date.
3. Turn that into 1 or 0 per customer (MAX of a CASE).
4. Rate = 100.0 × customers who came back ÷ customers who ordered.

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders
  GROUP BY customer_id
),
flags AS (
  SELECT f.customer_id,
         MAX(CASE WHEN o.order_date > f.first_date
                   AND julianday(o.order_date) - julianday(f.first_date) <= 30
                  THEN 1 ELSE 0 END) AS came_back
  FROM firsts f
  JOIN orders o ON o.customer_id = f.customer_id
  GROUP BY f.customer_id
)
SELECT COUNT(*) AS customers,
       SUM(came_back) AS came_back_within_30_days,
       ROUND(100.0 * SUM(came_back) / COUNT(*), 1) AS retention_pct
FROM flags;

Common mistakes

• Counting any second order, regardless of how long after.
• Dividing whole numbers and getting 0.
• Measuring from sign-up date when the question says first order (or the other way round).

Interview answer

I anchor each customer on their first order with MIN per customer, join back to their orders, and flag whether any later order falls within 30 days, taking MAX of a CASE so each customer is one or zero. The retention rate is the sum of flags over the number of customers, times 100.0. I'd raise two definitional points: whether a same-day second order counts, and excluding customers who haven't had a full 30 days yet.

Follow-up questions

The median salary

BonusInterview problemHard

Find the median salary of all employees, and then the median salary in each department.

The pattern: Median with ROW_NUMBER and COUNT

"Median", "middle value", "50th percentile".

The approach

1. Sort the salaries and number them 1 to n with ROW_NUMBER; get n with COUNT(*) OVER ().
2. The middle positions are (n + 1) / 2 and (n + 2) / 2 using whole-number division: the same row when n is odd, the two middle rows when n is even.
3. Average the salaries at those positions.
4. For each department, add PARTITION BY dept_id to both window functions and GROUP BY dept_id.

WITH ordered AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS n
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

Common mistakes

• Using AVG — that is the mean, not the median.
• Taking only position n / 2, which is wrong for odd counts and only half the answer for even counts.
• Decimal division in the position maths ((n + 1) / 2.0), which gives 4.5 and matches no row.

Interview answer

If the database has PERCENTILE_CONT, the median is PERCENTILE_CONT 0.5 within group ordered by salary. Otherwise I number the sorted values with ROW_NUMBER, get the count with COUNT over the same window, and average the values at positions n plus one over two and n plus two over two with integer division — that's the single middle row for odd n and the two middle rows for even n. Partitioning both window functions gives a median per group.

Follow-up questions

Customers who bought every product in a category

BonusInterview problemHard

Find the customers who have bought every product in the Stationery category.

The pattern: "All of" (relational division)

"Every", "all of the", "each one of", "the complete set".

The approach

1. Count how many products the category has (here 2: Pen and Notebook).
2. For each customer, count the different products from that category they have bought.
3. Keep customers whose count equals the category's total: GROUP BY with HAVING COUNT(DISTINCT …) = (subquery).
4. Or say it as a double negative: there is no Stationery product this customer has not bought.

SELECT c.name
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE p.category = 'Stationery'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product_id) = (
  SELECT COUNT(*) FROM products WHERE category = 'Stationery'
)
ORDER BY c.name;

Common mistakes

• COUNT(*) instead of COUNT(DISTINCT product_id), so two notebooks look like two different products.
• Hard-coding the total (= 2) instead of counting the category.
• Using IN ('Pen', 'Notebook'), which finds customers who bought any of them, not all.

Interview answer

This is relational division — "bought all of". I join each customer to the category's products they bought, group by customer, and keep those whose count of distinct products equals the category's product count from a subquery. An equivalent formulation is a double NOT EXISTS: there's no product in the category that the customer hasn't bought. I'd mention the empty-category case, where the two versions disagree.

Follow-up questions