SQL Practice Problems

📘 Learn SQL Practice Problems from zero

Every problem in this set rewards the same disciplined process. Don't start typing SQL — start by reading and restating. Say out loud: "What is one row of my output, and what does it represent?" That single sentence fixes your output granularity and is the most common thing weak candidates get wrong.

Next, nail the schema and constraints. Note the primary key of each table (it tells you what's already unique — e.g. (player_id, event_date) means one login row per player per day), which columns can be NULL, and whether a join is one-to-one or one-to-many (a fan-out join silently double-counts your sums). Skim the expected-output sample — it tells you the granularity and whether zero-count rows must appear.

Then build the brute-force shape first: which table is the spine, what joins hang off it, what filter narrows rows. Get a correct (if naive) query running mentally before optimizing. Now spot the pattern from the recognition list: is this a per-group aggregate, a top-per-group window, an anti-join, a range-join weighted average, or a CASE bucketing? Map the signal to the technique — and resist over-reaching (a bare "max per group" wants MAX()...GROUP BY, not a window).

Optimize and clean: push filters into WHERE before grouping, replace correlated subqueries with joins or window functions, and use HAVING only for post-aggregate conditions.

Finally, walk the edge cases: empty groups, ties in a ranking (do you want RANK or ROW_NUMBER?), NULLs in join keys or COUNT/AVG, integer division, and duplicate rows. Mentally test with a tiny 2-3 row example and trace it through every clause. State your assumptions to the interviewer — clarifying granularity and null-handling out loud signals senior-level rigor.

✨ Added by the guide to build intuition — not from the source course.

🎯 Guided practice

Let's walk through Game Play Analysis II (return the device a player used on their first login). I'll think aloud about how the signal selects the technique.

1. Restate & granularity. "One row per player, showing the device_id of their earliest login." Table Activity(player_id, device_id, event_date, games_played), with PK (player_id, event_date) — so a player has at most one row per day. One row per player tells me I'm collapsing many login rows down to one — an aggregate or ranking job.
2. Spot the trap. The naive instinct is GROUP BY player_id with MIN(event_date). That's exactly the right shape for Game Play Analysis I (which only wants first_login). But here I need device_id from that same first row. A plain GROUP BY can't return a non-aggregated device_id tied to the min date — that's the classic "select a column from the group's extreme row" problem.
3. Signal → technique. "Pick the row with the earliest date per player, keeping its other columns" is the textbook top-per-group pattern → a window function: ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date). Partition by the entity (player), order by the tie-breaking key (date), filter to rank 1.
4. Optimal shape. SELECT player_id, device_id FROM (SELECT player_id, device_id, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn FROM Activity) t WHERE rn = 1;
5. Edge cases. Could a player have two first-day rows? The PK (player_id, event_date) forbids it, so the earliest date is unique and ROW_NUMBER is safe. If ties were possible and you wanted all tied rows you'd use RANK; if you wanted exactly one you'd keep ROW_NUMBER and add a deterministic tie-breaker to the ORDER BY. No NULL dates here, so ordering is safe.

The transferable lesson: the moment you need "a column from the min/max row per group," your hand should move to ROW_NUMBER() ... PARTITION BY ... ORDER BY. But if you only need the extreme value itself and no companion column, stay with plain GROUP BY MIN()/MAX() — that's all Bikes Last Time Used (just MAX(end_time) per bike) and Game Play Analysis I (just MIN(event_date)) require. Knowing which side of that line a prompt falls on is the real skill the set is training.

✨ Added by the guide — work these before the full problem set.