ORIGINAL TECHNICAL PRACTICE
Select a complete latest record
Why is SELECT customer_id, MAX(updated_at), status not a reliable way to get each customer's latest status?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
The maximum timestamp and an ungrouped status are not generally guaranteed to come from the same row. Select the desired row using an explicit ordering and deterministic tie rule.
Work through it
- Define whether updated_at represents event time, ingestion time or effective time. Specify how NULL timestamps and corrections are handled.
- Assign ROW_NUMBER over each customer ordered by updated_at descending and a stable tie-breaker, then retain row1 in an outer query.
- Test two records sharing the latest timestamp, an out-of-order arrival and a customer with only a NULL timestamp. Select all desired columns from the chosen row.
Tradeoffs to discuss
- A join back to the maximum timestamp can be efficient but may return multiple tied records. A window query makes the tie policy explicit and portable across many databases.
Common pitfalls
- Database-specific bare-column behavior should not be mistaken for a portable guarantee. Using ingestion order can select the wrong business-effective state.