SQL gotchas & interview

Chapter 13 11 code blocks 3132 words

This chapter is the revision sheet. Everything that silently produces a wrong answer, collected in one place; the classic interview problems worked through; and a short style guide, because readable SQL is a professional skill in itself.

The gotcha catalogue

Ranked by how much damage they do in real work. Every one of these runs successfully — that is what makes them dangerous.

How to attack a query task

Under interview pressure students freeze and start typing. Give them a procedure instead.

1. Restate the question in one sentence. "For each course, the student with the highest grade." If you cannot say it, you cannot write it.
2. Name the output. How many rows should come back — one per course? One per student? What columns?
3. Find the tables and the keys. Which tables hold those columns, and what links them?
4. Say it out loud in SQL order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. The English sentence usually maps straight onto it.
5. Build it in layers. Write the FROM + JOIN, run it. Add the WHERE, run it. Then group. Never write 30 lines before running anything.
6. Check the row count at each step. If it jumps after a JOIN, that is fan-out.
7. Ask about NULLs and ties. "Should ungraded students count?" "If two tie for first, do you want both?" Interviewers award marks for asking — it shows you have been bitten before.

Classic interview problems

These come up again and again. Each is worked on school.db so the answers are verifiable.

1. The second-highest value

The most-asked SQL question in existence. Three solutions, each teaching something different:

-- (a) DENSE_RANK — generalises to Nth immediately, handles ties correctly
WITH ranked AS (
    SELECT grade, DENSE_RANK() OVER (ORDER BY grade DESC) AS rnk
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT DISTINCT grade FROM ranked WHERE rnk = 2;        -- 95

-- (b) LIMIT/OFFSET — simple, but wrong when there are ties
SELECT DISTINCT grade FROM enrollments
WHERE grade IS NOT NULL ORDER BY grade DESC LIMIT 1 OFFSET 1;

-- (c) "the max below the max" — the classic pre-window-function answer
SELECT MAX(grade) FROM enrollments
WHERE grade < (SELECT MAX(grade) FROM enrollments);

Result 1

1 row

Result 2

1 row

Result 3

1 row

2 ms

Say why (a) is the best answer: change rnk = 2 to rnk = 5 and it still works, and ties behave sensibly.

2. Top-N per group

WITH ranked AS (
    SELECT course_id, student_id, grade,
           ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rn
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT * FROM ranked WHERE rn <= 2 ORDER BY course_id, rn;

… first 15 of 28 rows

28 rows

1 ms

1. Find duplicates

SELECT student_id, course_id, COUNT(*) AS times
FROM enrollments
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;
-- (none in our clean data)

-- and delete them, keeping the lowest id of each group
DELETE FROM enrollments
WHERE enrollment_id NOT IN (
    SELECT MIN(enrollment_id) FROM enrollments GROUP BY student_id, course_id
);

… first 15 of 514 rows

514 rows

2 ms

1. Rows in A with no match in B

SELECT c.title FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.course_id);
-- Deep Learning

1 row

0 ms

1. Running total

SELECT enrolled_at,
       COUNT(*) AS n,
       SUM(COUNT(*)) OVER (ORDER BY enrolled_at) AS running_total
FROM enrollments
GROUP BY enrolled_at ORDER BY enrolled_at;

… first 15 of 91 rows

91 rows

1 ms

1. Median — harder than it looks There is no MEDIAN() in standard SQL, so this tests whether you can think:

WITH ordered AS (
    SELECT grade,
           ROW_NUMBER() OVER (ORDER BY grade) AS rn,
           COUNT(*)     OVER ()               AS n
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT AVG(grade) AS median
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);
-- 14 grades → rows 7 and 8 → (81 + 83) / 2 = 82.0

1 row

1 ms

The (n+1)/2, (n+2)/2 trick handles both cases at once: for an odd count both expressions give the same row, for an even count they give the middle two.

7. Percentage of total

SELECT course_id,
       COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM enrollments GROUP BY course_id ORDER BY pct DESC;

14 rows

1 ms

1. Consecutive / gap analysis

-- Students who enrolled in two courses on the same day
SELECT student_id, enrolled_at, COUNT(*) AS same_day
FROM enrollments
GROUP BY student_id, enrolled_at
HAVING COUNT(*) > 1;
-- students 1, 3, 5, 6, 7, 8 — each enrolled twice on one day

-- Days between a student's consecutive enrollments
SELECT student_id, enrolled_at,
       CAST(JULIANDAY(enrolled_at) - JULIANDAY(
           LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at)
       ) AS INTEGER) AS gap_days
FROM enrollments ORDER BY student_id, enrolled_at;

Result 1

… first 15 of 184 rows

184 rows

Result 2

… first 15 of 1866 rows

1866 rows

3 ms

1. Pivot without a PIVOT keyword

SELECT course_id,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'active'    THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END) AS failed
FROM enrollments GROUP BY course_id;

14 rows

1 ms

1. Above-average members of each group

SELECT enrollment_id, course_id, grade
FROM (
    SELECT *, AVG(grade) OVER (PARTITION BY course_id) AS course_avg
    FROM enrollments WHERE grade IS NOT NULL
)
WHERE grade > course_avg
ORDER BY course_id;

… first 15 of 687 rows

687 rows

1 ms

Writing readable SQL Interviewers judge style, and colleagues have to maintain it. Six rules cover it:

1. Keywords uppercase, your names lowercase. The structure becomes visible at a glance.
2. One clause per line, one column per line once there are more than three.
3. Meaningful table aliases — s, e, c — never t1, t2.
4. Always AS on computed columns. An unnamed expression is unreadable in the output and in pandas.
5. CTEs instead of nested subqueries once the query passes ~15 lines. Name each step.
6. Comment the why, not the what. -- exclude test accounts (ticket #412) is useful; -- select the name is noise.

-- ❌ hard to read
select s.first_name,c.title,e.grade from enrollments e join students s on e.student_id=s.student_id join courses c on e.course_id=c.course_id where e.grade>80 order by e.grade desc;

-- ✅ the same query
SELECT
    s.first_name,
    c.title,
    e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
WHERE e.grade > 80
ORDER BY e.grade DESC;

Result 1

… first 15 of 338 rows

338 rows

Result 2

… first 15 of 338 rows

338 rows

1 ms

Full question bank Grouped by topic, for revision or for an exam. Answers are in the chapters shown.

Fundamentals (Ch. 1)

• What is the difference between SQL, a database and a DBMS?
• Primary key vs unique key vs foreign key.
• Is NULL the same as 0 or an empty string? How do you test for it?
• In what order does a SELECT actually execute?
• DELETE vs TRUNCATE vs DROP.
• Why avoid SELECT * in production code?

Querying (Ch. 2–4)

• Why can't you use a column alias in WHERE?
• WHERE vs HAVING.
• COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col).
• Does AVG include NULLs? What does SUM return over zero rows?
• Why does SELECT 7/2 return 3?
• DISTINCT vs GROUP BY.
• Will WHERE city != 'X' return rows where city is NULL?

Joins & subqueries (Ch. 5–6)

• INNER vs LEFT vs FULL OUTER JOIN.
• Why does COUNT(*) give 1 instead of 0 after a LEFT JOIN?
• Condition in ON vs in WHERE — what changes?
• How do you find rows in A with no match in B?
• What is a self join, and why a.id < b.id?
• Your SUM doubled after adding a JOIN — why?
• IN vs EXISTS vs JOIN.
• CTE vs subquery — when must you use a CTE?
• What are the two parts of a recursive CTE?

Window functions (Ch. 7)

• Window function vs aggregate.
• ROW_NUMBER vs RANK vs DENSE_RANK.
• How do you get the top 3 per group?
• Why can't you filter a window function in WHERE?
• What does ORDER BY inside OVER() change?
• How do you compute month-over-month growth?
• ROWS vs RANGE.

Functions & sets (Ch. 8–9)

• How does CASE choose a branch? How do you build a pivot?
• Why is ELSE 0 wrong inside AVG(CASE ...)?
• How do you get the last day of a month?
• UNION vs UNION ALL vs JOIN.
• What are the requirements for a UNION?
• Why is EXCEPT safer than NOT IN?

Design, performance & Python (Ch. 10–12)

• Explain 1NF, 2NF, 3NF. When would you denormalize?
• What is a transaction? What does ACID stand for?
• What is a view? Does it store data?
• How does an index speed up a query, and what does it cost?
• Does column order matter in a composite index?
• What does "sargable" mean?
• How do you diagnose a slow query?
• What is SQL injection and how do you prevent it?
• Should you filter in SQL or in pandas, and why?

Summary — the SQL half

Thirteen chapters, one small database, and a complete working knowledge of analytical SQL. What a student who has finished Part I can do:

• Read data: SELECT, WHERE, ORDER BY, LIMIT, DISTINCT, and every filtering operator including the NULL traps.
• Summarise: the five aggregates, GROUP BY, HAVING, and the difference between filtering rows and filtering groups.
• Combine: every join type, anti-joins, and — crucially — spot fan-out before it corrupts a report.
• Compose: subqueries, EXISTS, CTEs and recursive CTEs to build multi-step analyses that stay readable.
• Analyse: window functions for rankings, top-N per group, running totals, moving averages and period-over-period change.
• Reshape: CASE WHEN pivots, string and date handling, set operations and unpivoting to long format.
• Build and protect: tables, constraints, transactions, normalization and views.
• Make it fast: indexes, sargable predicates and EXPLAIN QUERY PLAN.
• Connect it to Python: parameterised queries into a tidy DataFrame, ready to plot.

Next — Part II: Chapter 14 begins Seaborn. Every DataFrame we plot from here comes out of a query like the ones you have just learned to write.