Return only the newest order from each customer's history.
Produce a deterministic result when two orders have the same timestamp.
Which SQL approach is most appropriate?
Rank rows within each customer with ROW_NUMBER and use a stable order ID as a tie-breaker.
Detailed explanation
TTL expires items and does not rank Athena rows.
TTL expires items and does not rank Athena rows.
The maximum time alone does not safely identify the other columns of that order.
The maximum time alone does not safely identify the other columns of that order.
A window rank identifies the complete newest row while a unique tie-breaker makes the result stable.
A window rank identifies the complete newest row while a unique tie-breaker makes the result stable.
Deleting history harms retention and does not implement a query result.
Deleting history harms retention and does not implement a query result.
Check official documentation
Compare the following points with the official documentation.
Amazon Athena window functions、ROW_NUMBER、ORDER BYの公式説明を確認する。
What to confirm
MAX時刻だけでは行全体を特定できない理由を説明できる。
Key points
- Window function
- ROW_NUMBER
- PARTITION BY
- Deterministic order
Notes
- Environment: AWS公式DEA-C01試験ガイドと各サービスの公式ドキュメントの確認
- Confirm current AWS service behavior in the official DEA-C01 documentation.
Foundation review
Aggregate versus rank
MAX returns a value but does not identify a complete source row by itself.
Tie breaking
Use a stable ID after the timestamp to avoid nondeterministic results.