SQL problem track

Ranking & Dedup

Latest row per key, top-N per group, and the department-average trap. ROW_NUMBER earns its keep here.

3 problems · ~40 min total · Data Engineer · Analytics Engineer · BI Analyst · Data Scientist

1. Step 1 · ~12 min · Shopify, Square, Instacart

   Latest order per customer

   Build a one-row-per-customer table holding their most recent order. Two orders can share a timestamp, so the tie-break has to be deterministic or your pipeline produces different rows on every run.

   DeduplicationWindow functionsRanking

   mediumProNot started

2. Step 2 · ~14 min · Amazon, Walmart, Target

   Top two products per category

   Top-N per group is asked in almost every SQL screen. Return the two best-selling products inside each category by units, with a deterministic tie-break.

   RankingWindow functionsJoins

   hardProNot started

3. Step 3 · ~14 min · Google, Microsoft, Bloomberg

   Salaries above the department average

   Return employees earning more than their own department’s average, and show that average beside them. The trap: compute the average with a window and filter it in WHERE, and the average is recomputed over only the surviving rows.

   Window functionsSubqueries

   hardProNot started

Other tracks