Where To Start The Query

An interviewer gives you a schema of users, orders and order_items and asks for: "for each user, the total of their most recent order, and how it compares with the order before it."

You have not written this exact query before. What is the strongest first move?

Decide the join strategy first, work out whether a hash join or a nested loop is appropriate for these table sizes, then write SQL that encourages that plan.

Write a query that lists every order per user with its position in that user's history and the previous order's total alongside it, check it against four rows you made up by hand, then filter down to the rows you actually want.

Recall the standard latest-row-per-group template you have memorized and type it out, then adapt the column names to this schema.

Write a correlated subquery per user, since the requirement is phrased per user and that maps onto the sentence most directly.