Q37 — AWS DEA-C01 Ch.1

Question 37 of 100 | ← Chapter 1

A data engineer is using Amazon Athena to analyze sales data that is in Amazon S3. The data engineer writes a query to retrieve sales Amounts for 2023 for several products from a table named sales_data. However, the query does not return results for all of the products that Are in the sales_data table. The data engineer needs to troubleshoot the query to resolve the issue. The data engineer's original query is as follows: SELECT product_name, sum(sales_amount) FROM sales_data - WHERE year = 2023 - GROUP BY product_name - How should the data engineer modify the Athena query to meet these requirements?

• A. Replace sum(sales_amount) with count(∗) for the aggregation.
• B. Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023. ✓
• C. Add HAVING sum(sales_amount) > 0 after the GROUP BY clause.
• D. Remove the GROUP BY clause.

Correct Answer: B. Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023.

Explanation

答案B是正确的。原查询中通过“year=2023”来筛选2023年的数据可能存在问题。在Athena中，对于日期类型的字段，通常使用“extract(yearFROM字段名)=年份”的方式来准确提取年份进行筛选。选项A改变了聚合方式，不符合需求；选项C增加条件不符合解决此问题的思路；选项D去除分组也不正确。所以应选择B选项，将“WHEREyear=2023”修改为“WHEREextract(yearFROMsales_data)=2023”。