Worked answers · Real data · Practice each one

SQL InterviewQuestions — with answers you can run

SQL interviews come back to the same dozen patterns. Below is each one with a short answer that runs on real data, and a link to the DB Quacks level where you can practice it in your browser. No signup, no install.

Every example uses the duck village database from the SQL tutorial: a ducks table (id, name, age, species, birth_date, arrival_date), a duck_diaries table, and CSV files such as pond_visits.csv. Open any linked level and paste the query into its editor to see the result.

1. How do you find duplicate values?

Group by the column you want to check and keep the groups that have more than one row. The filter belongs in HAVING, because WHERE runs before the rows are grouped. Here the ages 8 and 7 belong to one duck each, so HAVING drops them and six ages remain.

SELECT age, COUNT(*) AS n
FROM ducks
GROUP BY age
HAVING COUNT(*) > 1
ORDER BY n DESC, age DESC;

Practice it: SQL HAVING.

2. How do you find the second highest value?

Sort the distinct values from high to low and skip the first. Here the two oldest ducks are 8 and 7, so the answer is 7 with or without DISTINCT. Ties are where it matters: two ducks are 6, so asking for the fourth-highest age returns 6 again without DISTINCT and 5 with it.

SELECT DISTINCT age
FROM ducks
ORDER BY age DESC
LIMIT 1 OFFSET 1;

For the Nth value, rank with DENSE_RANK() OVER (ORDER BY age DESC) and keep rank N. Practice it: SQL LIMIT and SQL ROW_NUMBER.

3. How do you get the top row in each group?

Number the rows inside each group with a window function and keep row 1. QUALIFY (DuckDB, Snowflake, BigQuery) filters on the window result directly; PostgreSQL, MySQL and SQL Server have no QUALIFY, so wrap the query in a subquery or CTE and filter outside.

SELECT species, name, age
FROM ducks
QUALIFY ROW_NUMBER() OVER (PARTITION BY species ORDER BY age DESC, name) = 1;

Practice it: SQL ROW_NUMBER.

4. How do you find rows with no match in another table?

This is the anti-join. LEFT JOIN keeps every row from the left table and fills the right side with NULL when nothing matches, so the unmatched rows are the ones where the right table's key is null. Here: ducks that never wrote a diary entry.

SELECT d.name
FROM ducks d
LEFT JOIN duck_diaries dd ON dd.duck_id = d.id
WHERE dd.diary_id IS NULL;

WHERE NOT EXISTS (SELECT 1 FROM duck_diaries dd WHERE dd.duck_id = d.id) gives the same rows. Practice it: SQL LEFT JOIN.

5. How do you calculate a running total?

SUM becomes a running total when it is used as a window function with an ORDER BY: each row adds up everything up to and including itself. PARTITION BY restarts the total for each duck.

SELECT duck_id, date, distance,
       SUM(distance) OVER (
         PARTITION BY duck_id ORDER BY date
       ) AS running_distance
FROM 'data/daily_distances.csv';

Practice it: SQL running total with SUM OVER.

6. How do you compare a row with the previous one?

LAG reads a value from an earlier row in the same window; LEAD reads a later one. The first row of each duck has no previous visit, so it gets NULL.

SELECT duck_id, visit_date,
       LAG(visit_date) OVER (
         PARTITION BY duck_id ORDER BY visit_date
       ) AS previous_visit
FROM 'data/pond_visits.csv';

Practice it: SQL LAG and LEAD.

7. How do you find rows above the average?

Aggregates aren't allowed in WHERE, so compute the average in a subquery and compare each row with it.

SELECT name, age
FROM ducks
WHERE age > (SELECT AVG(age) FROM ducks);

Practice it: SQL subquery.

8. How do you count rows that meet a condition, per group?

Put the condition inside the aggregate. DuckDB, PostgreSQL and SQLite support COUNT(*) FILTER (WHERE …); SUM(CASE WHEN … THEN 1 ELSE 0 END) does the same and works in every database.

SELECT species,
       COUNT(*) FILTER (WHERE age >= 3) AS adults,
       COUNT(*) FILTER (WHERE age < 3)  AS young
FROM ducks
GROUP BY species;

Practice it: SQL CASE WHEN and SQL GROUP BY.

9. What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens. HAVING filters the groups after aggregation, which is why it can test COUNT(*) or SUM(). A query can use both: WHERE to drop rows you don't want counted, HAVING to drop groups whose totals don't qualify. Practice it: GROUP BY, HAVING and ORDER BY.

10. What is the difference between UNION and UNION ALL?

UNION combines two results and removes duplicate rows; UNION ALL keeps every row. UNION ALL is faster because it skips the de-duplication, so use UNION only when duplicates are actually possible and unwanted. Practice it: SQL UNION.

11. How do you query a hierarchy?

A recursive CTE starts from the root rows and repeatedly joins the next level until no new rows appear — the standard way to walk an org chart, a category tree or a family tree. Practice it: recursive CTE in SQL, where you trace every descendant of Grandpa Quackers.

12. What is a CTE and why use one?

A common table expression (WITH name AS (…)) names an intermediate result so the final query reads top to bottom instead of inside out. Interviewers ask it because it shows you can break a hard question into steps. Practice it: SQL CTE (WITH clause).

Beyond the classics

Interview take-home tasks look less like these one-liners and more like a messy business question. The story-driven SQL challenges are built that way: a case to crack, several tables, and one correct answer. The weekly challenge adds a fresh puzzle every week.