Topic 1 Question 48
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?
Replace sum(sales_amount) with count(*) for the aggregation.
Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023.
Add HAVING sum(sales_amount) > 0 after the GROUP BY clause.
Remove the GROUP BY clause.
ユーザの投票
コメント(17)
- 正解だと思う選択肢: B
"SELECT product_name, sum(sales_amount) FROM sales_data WHERE extract(year FROM sales_date) = 2023 GROUP BY product_name;" A. This would change the query to count the number of rows instead of summing sales. C. This would filter out products with zero sales amounts. D. Removing the GROUP BY clause would result in a single sum of all sales amounts without grouping by product_name.
👍 12GiorgioGss2024/03/19 None of these options make sense. I think the question is worded incorrectly. I understand that the problem is supposed to be: the products that did not have any sales in 2023 should also be visible in the report with sum of sales_amount = 0. So, the WHERE condition should be deleted and replaced with a CASE WHEN. That way all of the products in the table will be visible, but only sales for 2023 will be summed. Which is what I think this question is asking. None of the provided options do that.
👍 7pikuantne2024/10/30- 正解だと思う選択肢: B
year should be the partition in s3 so its necessary to extract. its not a column
👍 5valuedate2024/05/24
シャッフルモード