Tools › Interview

SQL interview sandbox

24 prompts the desk actually uses: top N per group, running totals, anti-joins, the LEFT JOIN WHERE trap. Draft T-SQL in the box. Reveal a worked answer. We do not run it here — Monday on Harbour & Co does.

24 prompts

Top N per groupPARTITION BY customer, filter rn <= 3.Running totalWindow SUM, not a triangular self-join.Second highestROW_NUMBER = 2, or OFFSET 1 FETCH 1. Not MAX of a MAX.Find duplicatesHAVING COUNT(*) > 1, then join back for the rows.Gaps and islandsdate − ROW_NUMBER is the island key.Anti-joinLEFT JOIN … WHERE right.key IS NULL. Or NOT EXISTS.Month over monthAggregate to month, then LAG the total.Cohort retentionFirst-seen month, then activity month, then COUNT DISTINCT.Self-joinJoin the table to itself on manager_id = staff_id.PivotCASE per status is enough. PIVOT if they asked for the keyword.MedianPERCENTILE_CONT(0.5) or a pair of ROW_NUMBERs.Consecutive datesIsland length >= 3, or three self-joins on date+1.Latest rowROW_NUMBER = 1, not MAX + join that duplicates ties.Delete dupesROW_NUMBER in a CTE, DELETE WHERE rn > 1.Recursive CTEAnchor the CEO, UNION ALL the staff who report to the current set.Percent of totalamount / SUM(amount) OVER ().Year over yearGrain to year (or month), LAG(..., 1) or LAG(..., 12) for months.Window vs GROUP BYGROUP BY collapses. OVER keeps every row.LEFT JOIN trapFilter right-hand columns in ON, not WHERE.Age from a dateDATEDIFF(year) overcounts. Step back one if the birthday has not happened.IN vs EXISTSEXISTS short-circuits. IN + NULL in the list is a trap.UNION vs UNION ALLUNION ALL is cheaper. UNION is DISTINCT across the stack.COUNT DISTINCTThree different questions. NULL and doubles change the answer.NULL three-valued logicNULL <> 'x' is unknown. WHERE keeps only true.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free