Exam AWS Certified Data Engineer - Associate DEA-C01 topic 1 question 48 discussion

"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.

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.

C, query in the question is correct you just need to get amounts grater than Zero

year should be the partition in s3 so its necessary to extract. its not a column

No need to extract the year again

https://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/sql-reference-having-clause.html

Wrong answers A. Replace sum(sales_amount) with count(*) for the aggregation. This option will return the count of records for each product, not the sum of sales amounts, which is the desired result. B. Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023. The year column likely stores the year value directly, so there's no need to extract it from a date or timestamp column. D. Remove the GROUP BY clause. Removing the GROUP BY clause will cause an error because the sum(sales_amount) aggregation function requires a GROUP BY clause to specify the grouping column (product_name in this case).

B B. Change `WHERE year = 2023` to `WHERE extract(year FROM sales_data) = 2023`. The issue with the original query is that it assumes there is a column named `year` in the `sales_data` table. However, it's more likely that the date or timestamp information is stored in a single column, for example, a column named `sales_date`. To extract the year from a date or timestamp column, you need to use the `extract()` function in Athena SQL.

None of the answer makes senses. Option C will exclude any amount that is 0. This option would be correct if it is: Add HAVING sum(sales_amount) >= 0 after the GROUP BY clause.

Gemini: C. Add HAVING sum(sales_amount) > 0 after the GROUP BY clause. Zero Sales Products: The original query is likely missing products that had zero sales amount in 2023. This modification filters the grouped results, ensuring only products with positive sales are displayed. Why Other Options Don't Address the Core Issue: A. Replace sum(sales_amount) with count(*) for the aggregation. This would show how many sales transactions a product had, but not if it generated any revenue. It wouldn't solve the issue of missing products. B. Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023. This is functionally equivalent to the original WHERE clause if the year column is already an integer type. It wouldn't fix missing products. D. Remove the GROUP BY clause. This would aggregate all sales for 2023 with no product breakdown, losing the granularity needed.

Not A because the engineer wants a sum not the total count. Not C because it will filter out the data with sales_amount zero. Not D because it will return just one result and the engineer wants the sales for multiple products. B should be the right answer if the sales_data is a date field.

https://docs.aws.amazon.com/athena/latest/ug/select.html