Each customer has several update records.
Keep only the row with the newest update time for analysis.
Which SQL structure is most appropriate?
Use ROW_NUMBER with PARTITION BY customer_id and ORDER BY updated_at descending, then keep rank one.
Detailed explanation
IAM access analysis does not describe customer-record update history.
IAM access analysis does not describe customer-record update history.
The window function ranks rows within each customer so rank one is the newest record.
The window function ranks rows within each customer so rank one is the newest record.
An object timestamp is not guaranteed to be the record update time.
An object timestamp is not guaranteed to be the record update time.
LIMIT one returns one row for the whole table, not one row per customer.
LIMIT one returns one row for the whole table, not one row per customer.
Check official documentation
Compare the following points with the official documentation.
Amazon AthenaまたはRedshiftのROW_NUMBER、OVER、PARTITION BYを確認する。
What to confirm
全体LIMITとグループ内順位付けの違いを説明できる。
Key points
- ROW_NUMBER
- PARTITION BY
- Window function
- Latest row
Notes
- Environment: AWS公式DEA-C01試験ガイドと各サービスの公式ドキュメントの確認
- Confirm current AWS service behavior in the official DEA-C01 documentation.
Foundation review
Group-local ranking
Partitioning by customer keeps one ranking sequence per customer.
Deterministic order
Add a tie-breaker such as an event ID when update times can be equal.