Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 77 ยท Databases and Data Interview Questions

SQL: Query-Writing Interview Questions

Solve the SQL query problems interviewers love: joins, GROUP BY and HAVING, second-highest salary, duplicates, gaps and window functions, all runnable.

SQL interviews are mostly "write a query" problems. The same dozen patterns appear again and again, wearing different table names: join two tables and keep the unmatched rows, count per group, find the Nth highest value, find duplicates, find gaps in a sequence, rank within a group, build a running total. If you can recognize the pattern, you can solve a new problem in minutes.

Every query in this lesson runs in the in-browser SQL runner, so you can edit it and watch the result change. If you need the basics first, start with joins, aggregate functions and window functions. Dialect note: the examples use standard SQL that also works in PostgreSQL, MySQL 8 and SQL Server unless a comment says otherwise (LIMIT is the main difference: SQL Server uses TOP or OFFSET ... FETCH).

Six patterns cover most questions

Join Match rows across tables.
Group One row per category.
Nth highest Rank, offset or subquery.
Duplicates GROUP BY with HAVING.

Gaps and window functions complete the set.

1 / 2

The practice data

All the examples use the same two tables. Paste the setup at the top of any TryIt in this lesson.

SQL Setup: employees and departments

Press Run (or Ctrl + Enter) to see the result here.

Joins: keep or drop the unmatched rows?

Question: "List every department with its number of employees, including departments with none."

The trap is the join type. An INNER JOIN drops departments with no employees. Start from the table whose rows you must keep (departments) and use LEFT JOIN. Then count a column from the right table: COUNT(*) would count the NULL-extended row as 1, while COUNT(e.id) skips NULLs and gives 0.

SQL Departments with zero employees

Press Run (or Ctrl + Enter) to see the result here.

A related favorite is the self join: "show each employee with their manager's name". Join the table to itself with two aliases, and use a LEFT JOIN so the top manager (whose manager_id is NULL) still appears. See self join.

SQL Employee and manager

Press Run (or Ctrl + Enter) to see the result here.

GROUP BY, HAVING and the WHERE difference

WHERE filters rows before grouping; HAVING filters groups after aggregation. "Departments whose average salary is above 6,000" needs HAVING AVG(salary) > 6000, because the average does not exist until the groups are built. Putting an aggregate in WHERE is an error, and interviewers ask about it constantly.

Another standard trap: every selected column must either be in GROUP BY or inside an aggregate. Some databases (older MySQL, SQLite) let you break that rule and return an arbitrary row's value; PostgreSQL and SQL Server reject it. Write it correctly regardless.

SQL Departments with average salary above 6000

Press Run (or Ctrl + Enter) to see the result here.

The second-highest salary

This is probably the most-asked SQL question. Several solutions exist, and offering two shows depth.

Subquery: the highest salary that is lower than the maximum. It handles ties correctly (Ben and Cleo both earn 7000, and 7000 is the second highest distinct value) and returns NULL if there is no second value.

DISTINCT with OFFSET: order distinct salaries descending, skip one, take one. Wrap it in a subquery if you need NULL instead of "no rows".

Window function: DENSE_RANK() generalizes to "Nth highest, per department". RANK() leaves gaps after ties; ROW_NUMBER() never ties. Pick based on whether equal salaries should share a position, and say so out loud.

SQL Three ways to find the second-highest salary

Press Run (or Ctrl + Enter) to see the result here.

Finding duplicates, and removing them

Question: "Find duplicate emails." Group by the column and keep groups bigger than one:

SQL
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

To delete duplicates and keep one row, number the copies and delete everything past the first. The portable idea is "keep the row with the smallest id per group":

SQL
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);
SQL Find and remove duplicates

Press Run (or Ctrl + Enter) to see the result here.

Mention that duplicates should be prevented with a UNIQUE constraint, not cleaned up forever. (MySQL will not let you delete from a table while selecting from it in a subquery; the usual workaround is a join delete or a derived table.)

Gaps in a sequence

Question: "A table has IDs 1, 2, 3, 5, 6, 9. Find the gaps." The trick is to compare each row with the previous one. LAG() gives you the previous value; where the difference is more than 1, a gap starts.

SQL Find gaps with LAG

Press Run (or Ctrl + Enter) to see the result here.

The same trick finds islands (runs of consecutive values): subtract a ROW_NUMBER() from the value, and rows in the same run share the same difference. That is a more advanced follow-up; knowing it exists is already a good sign.

Window functions: rank, top-N per group, running total

Window functions compute across related rows without collapsing them. The two most-asked uses:

  • Top N per group: rank inside each partition, then filter. "Highest-paid employee in each department" is ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) with = 1. You cannot filter on the window function in the same SELECT's WHERE, so wrap it in a subquery or CTE.
  • Running total: SUM(x) OVER (ORDER BY day).
SQL Top earner per department and a running total

Press Run (or Ctrl + Enter) to see the result here.

Note the tie-break , id in the ordering. Without it, Ben and Cleo (both 7000) would be ordered arbitrarily and ROW_NUMBER could return either. Interviewers notice whether you think about ties.

NULL and other classic traps

  • WHERE x = NULL never matches; use IS NULL.
  • NOT IN (subquery) returns nothing if the subquery produces any NULL. Prefer NOT EXISTS.
  • COUNT(*) counts rows, COUNT(col) skips NULLs, COUNT(DISTINCT col) counts unique non-NULLs.
  • UNION removes duplicates (and sorts, in many engines); UNION ALL keeps them and is faster.
  • Without ORDER BY, row order is not guaranteed.

More advanced query questions: recursive CTEs, LAG and LEAD, pivots, anti-joins and upserts

The patterns above cover the first round of SQL interviews. Senior rounds ask for these next. All runnable blocks use SQLite in the page; syntax that differs between databases is labelled.

Walk a hierarchy with a recursive CTE

Question: "Given employees with a manager_id, list everyone under a given manager, with their depth." A self join only goes one level. A recursive CTE repeats until no new rows appear: the anchor query selects the starting rows, and the recursive query joins the CTE to the table to find the next level. UNION ALL glues the levels together. Always make sure the recursion terminates: a cycle in the data (A manages B, B manages A) loops forever unless you track visited IDs or limit the depth. PostgreSQL and SQLite need the RECURSIVE keyword; SQL Server and Oracle do not.

SQL Org chart with depth and path

Press Run (or Ctrl + Enter) to see the result here.

Compare with the previous or next row: LAG and LEAD

Question: "Show each month's revenue and the change from the previous month." LAG(col) reads the value from a previous row in the window order, LEAD(col) from a following row. The first row has no previous one, so you get NULL (or a default you pass as the third argument). Compute a percentage change with NULLIF to avoid dividing by zero. The running-total pattern from above and these two cover most "compare to neighbors" questions.

SQL Month over month with LAG and LEAD

Press Run (or Ctrl + Enter) to see the result here.

Pivot rows into columns

Question: "Turn one row per (product, quarter) into one row per product with a column for each quarter." SQL Server and Oracle have a PIVOT clause; PostgreSQL has the crosstab extension; MySQL and SQLite have neither. The portable answer is conditional aggregation: SUM(CASE WHEN ... THEN value ELSE 0 END) per output column. The limit is that the output columns must be known when you write the query. A dynamic list of columns needs dynamic SQL.

SQL Pivot with conditional aggregation

Press Run (or Ctrl + Enter) to see the result here.

Anti-joins: rows with no match

Question: "List customers who have never placed an order." There are three common forms: LEFT JOIN ... WHERE o.id IS NULL, NOT EXISTS (correlated subquery) and NOT IN (subquery). The first two are safe. NOT IN has a trap: if the subquery returns even one NULL, x NOT IN (..., NULL) is never true (it evaluates to unknown), so the whole result is empty. Prefer NOT EXISTS.

SQL Three anti-joins, and the NOT IN NULL trap

Press Run (or Ctrl + Enter) to see the result here.

The first two forms return Ben and Cleo. The NOT IN form returns no rows, because one order has a NULL customer_id.

Upsert and MERGE

Question: "Insert a row, or update it if the key already exists." This is an upsert. The syntax is not portable: PostgreSQL and SQLite use INSERT ... ON CONFLICT (key) DO UPDATE, MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and SQL Server, Oracle and (since version 15) PostgreSQL support the standard MERGE. Mention atomicity: doing a SELECT and then an INSERT or UPDATE in the application is a race condition, because another session can insert between the two statements.

SQL
-- Read-only: standard MERGE (SQL Server, Oracle, PostgreSQL 15+)
MERGE INTO stock AS t
USING incoming AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);
SQL Upsert with ON CONFLICT

Press Run (or Ctrl + Enter) to see the result here.

OLTP versus OLAP, dynamic SQL and NOLOCK

OLTP (online transaction processing) systems serve many small reads and writes: orders, payments, logins. They use normalized schemas, indexes on keys and short transactions. OLAP (online analytical processing) systems answer big aggregate questions over history: revenue by region by year. They use denormalized star schemas (a central fact table plus dimension tables), often columnar storage, and are loaded in batches by ETL or ELT pipelines. A data warehouse is OLAP; running heavy reports on the OLTP database slows the application, which is why teams use read replicas or a warehouse.

Dynamic SQL builds a statement as a string at run time (EXEC or sp_executesql in SQL Server, EXECUTE in PL/pgSQL). It is needed for variable table or column names and dynamic pivots, but string concatenation with user input is SQL injection. Pass values as parameters and validate or quote identifiers (QUOTENAME in SQL Server, format('%I', name) in PostgreSQL).

NOLOCK is a SQL Server table hint equal to READ UNCOMMITTED: the query takes no shared locks, so it does not wait for writers but can read uncommitted (dirty) data, miss rows or read rows twice. Do not use it on data that must be correct. The better modern fix for blocking readers is row-versioning isolation (READ_COMMITTED_SNAPSHOT). The isolation levels and their anomalies are explained in Databases: Indexes, Normalization and Transactions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You need every department, including those with no employees, with an employee count. Which is correct?

    Start from the table whose rows you must keep. COUNT(e.id) ignores the NULL-extended row, so empty departments show 0; COUNT(*) would show 1.
  2. 2. Why can't you write WHERE AVG(salary) > 6000?

    WHERE filters rows before GROUP BY. HAVING filters the groups after aggregates are computed.
  3. 3. Employees earn 9000, 7000, 7000, 6500. Which function gives the two 7000 rows rank 2 and 6500 rank 3?

    DENSE_RANK has no gaps after ties. RANK would give 6500 rank 4, and ROW_NUMBER never gives two rows the same number.
  4. 4. SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b) returns no rows even though many ids are missing from b. What is a likely cause?

    x NOT IN (..., NULL) evaluates to unknown for every row, so nothing passes. Use NOT EXISTS or filter the NULLs out.

Summary

  • Recognize the pattern: join type, group and filter groups, Nth value, duplicates, gaps, window ranking.
  • Use LEFT JOIN from the table whose rows must all appear; count a right-table column.
  • WHERE filters rows, HAVING filters groups.
  • For "Nth highest", decide how ties behave and pick DENSE_RANK, RANK or ROW_NUMBER accordingly.
  • Find duplicates with GROUP BY ... HAVING COUNT(*) > 1; find gaps with LAG.
  • Always add a tie-breaker to window ordering, and beware of NULL in NOT IN.
  • Recursive CTEs walk hierarchies (anchor plus recursive step, guard against cycles); LAG and LEAD compare neighboring rows.
  • Pivot with conditional aggregation, prefer NOT EXISTS over NOT IN (the NULL trap), and use upsert syntax (ON CONFLICT, ON DUPLICATE KEY, MERGE) instead of select-then-write.
  • OLTP means many small transactions on normalized data; OLAP means big aggregates on star schemas; NOLOCK reads dirty data and concatenated dynamic SQL invites injection.

Next: Databases: Indexes, Normalization and Transactions.