← 🗄️ Data & SQL EngineeringNEXT IN DATA & SQL ENGINEERINGQuery Execution and Optimization→
Core
Ranking and Top-N Per Group
Top-N-per-group is the partition-then-filter idiom: rank rows within each group with a window function, then keep the ranks you want. The choice between ROW_NUMBER, RANK, and DENSE_RANK comes down to tie handling, and getting ties wrong is the usual bug. Applied-AI interviews probe it because it is the cleanest replacement for a clumsy self-join or correlated subquery, and the ranking-family distinction is a quick fluency check.
a free account unlocks the core curriculum tier · no card
RELATED CONCEPTS
PRACTICE THIS IN REAL QUESTIONS
SQL & Data EngineeringFind the top-N records per group and a running total per group in SQL.→SQL & Data EngineeringGroup a stream of user events into sessions in SQL (30-minute inactivity gap) using window functions.→SQL & Data EngineeringGiven rows with start and end timestamps, merge all overlapping intervals per user in SQL.→SQL & Data EngineeringImplement Slowly Changing Dimension Type 2 history tracking in a Delta lakehouse.→SQL & Data EngineeringHow do you find and remove duplicate rows in SQL, including 'fuzzy' near-duplicates?→SQL & Data EngineeringWrite SQL for a cohort retention analysis (what % of users return in week N after signup).→
COMPANIES THAT ASSUME THIS