SQL Interview Prompts: Ranking and Presence

Most SQL interview questions come from four families, and the wording of the prompt tells you which one. This video maps the four, then works the first two on a small fixed dataset: a ranking prompt (each customer's most recent order) written as ROW_NUMBER inside a CTE with the filter outside, and two presence prompts, which emails repeat and which customers ordered in one month but not the next.

You'll review how to:

• Hear a ranking prompt and name its two parts, the group and the survivor rule, including the tiebreaker interviewers listen for
• Number rows in a CTE and filter outside it, and why the filter cannot sit in the same WHERE
• Find repeated values with GROUP BY and HAVING, and tell that question apart from one that keeps a row
• Write active in A but not in B with NOT EXISTS and half-open date windows inside it

For engineers who know window functions, GROUP BY and subqueries and want to recognize the prompt before writing the query.