Asked about The queries you will actually be asked to write
Write a query for each user's most recent order.
An answer that works
Rank orders within each user by created_at descending with ROW_NUMBER in a CTE, then keep rows where the rank is 1. Mention that if two orders share a timestamp, you need a tiebreaker such as the id.
Then they push further
The table has a billion rows and this is slow. What do you do?
Check the plan. An index on (user_id, created_at) helps. If this is needed often, maintain a latest-order table or column updated on write rather than computing it on every read.
Could you do it without a window function?
Yes: join each order to the maximum created_at for its user from a grouped subquery. It is usually less clear and handles ties worse, which is why the window version is preferred.
What gets marked up
Marked up for handling ties explicitly and for thinking about indexes. Correct SQL with no mention of either passes; with both it stands out.
Reading an answer is not being able to give one. The section that teaches this is The queries you will actually be asked to write, 5 hours of SQL & data access.
Learn this properlyAlso asked about SQL & data access