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

COURSES COVERING THIS TOPIC

No lesson covers this one directly yet. These teach the surrounding topic from the beginning.

Applied AI Engineering·The interviewPremium12mTurning this course into a study planA concrete four-week plan mapping the seven modules onto the question bank, plus what to do differently if your interview is next week rather than next month.

RELATED CONCEPTS

Window Functions GROUP BY and Aggregation Gaps and Islands (Sessionization)SQL Joins

PRACTICE THIS IN REAL QUESTIONS

SQL & Data EngineeringTop-N per group in SQL: ROW_NUMBER vs RANK vs DENSE_RANK.→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 EngineeringSCD Type 2 in Delta Lake: track history with one atomic MERGE.→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

Scale AI Palantir Databricks Snowflake Zendesk Clay

NEXT IN DATA & SQL ENGINEERINGQuery Execution and Optimization→