Interview pack: SQL interview problems

Fifty classic SQL interview questions, from the second-highest salary to login streaks, each graded on hidden data.

1. Salary classics: Second/Nth highest: MAX below MAX or DENSERANK, wrapped so "none" is one NULL row; manager problems are self-joins on e.managerid = m.empid.
2. Ranking and top-N: One per group: ROWNUMBER with tie-breakers; ties share a place: RANK (gaps) or DENSERANK (levels); rank in a subquery, filter outside.
3. Dates and streaks: Streaks: DISTINCT days, then date − ROWNUMBER() groups each island; missing days come from generateseries; growth = LAG over per-month rows.
4. Joins and anti-joins: Anti-join with NOT EXISTS (never NOT IN over a nullable column); pairs with a.id < b.id; dedupe with DELETE … USING on a lower id; count DISTINCT after a fan-out.
5. Tricky reports: Pivot with COUNT() FILTER; median with PERCENTILECONT(0.5); swap seats by changing ids; sessions = LAG flag + running SUM.

Ends with a 12-question checkpoint (80% passes) and a project: Customer scorecard.