Many work and interview questions share a shape: the best-selling product in each category, each customer's latest order, the two best-paid people in each department. GROUP BY can't answer them, because you want whole rows, not just a MAX.
The pattern: rank inside a CTE, then filter on the rank outside it.
WITH ranked AS (
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE rn = 1;
You need the CTE (or a subquery) because WHERE runs before window functions; WHERE ROW_NUMBER() OVER (...) = 1 is an error.
Choose the ranking function on purpose. ROW_NUMBER() returns exactly N rows per group, breaking ties arbitrarily. RANK() and DENSE_RANK() keep every row tied at the cutoff, which is usually fairer for questions like top earners. Snowflake, BigQuery and DuckDB also have QUALIFY, which filters on a window function directly; PostgreSQL, MySQL and SQL Server don't.