Build regional and product aggregates from the same date-filtered sales rows.
Avoid duplicating the filter so the SQL remains readable and maintainable.
Which approach is most appropriate?
Define the filtered rows in a common table expression and reference it from both aggregates.
Detailed explanation
IAM policies define permissions and do not define SQL intermediate results.
IAM policies define permissions and do not define SQL intermediate results.
A named intermediate result can be referenced by multiple subsequent aggregates.
A named intermediate result can be referenced by multiple subsequent aggregates.
A later condition change can update one copy and leave the other wrong.
A later condition change can update one copy and leave the other wrong.
A key names an object and does not define SQL computation or dependencies.
A key names an object and does not define SQL computation or dependencies.
Check official documentation
Compare the following points with the official documentation.
Athena・RedshiftのWITH句、CTE、実行計画の公式説明を確認する。
What to confirm
SQLの再利用性と、保存キー・権限定義の役割を区別できる。
Key points
- SQL CTE
- WITH
- Intermediate result
- Maintainability
Notes
- Environment: AWS公式DEA-C01試験ガイドと各サービスの公式ドキュメントの確認
- Confirm current AWS service behavior in the official DEA-C01 documentation.
Foundation review
Logical reuse
A CTE makes extraction and aggregation stages explicit and easier to maintain.
Performance check
Confirm whether the engine materializes or re-reads a CTE and inspect scan volume.