ADA-C01 Question 315
Single answerPerform grouping, sorting, and orderingA Snowflake administrator is validating a daily chargeback report that ranks warehouses by total credits consumed. The report is built from ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY and should return one row per warehouse for the last 7 complete days, sorted from highest to lowest total credits. During testing, the administrator notices that the query returns multiple rows per warehouse and the sort order changes between runs when two warehouses have the same total credits. Which query change best meets the reporting requirement?
- A
Select WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY where START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP()) group by WAREHOUSE_NAME, START_TIME order by TOTAL_CREDITS desc;
- B
Select WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY where START_TIME >= DATE_TRUNC('DAY', DATEADD(day, -7, CURRENT_TIMESTAMP())) and START_TIME < DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) group by WAREHOUSE_NAME order by TOTAL_CREDITS desc, WAREHOUSE_NAME asc;
- C
Select distinct WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY where START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP()) order by TOTAL_CREDITS desc;
- D
Select WAREHOUSE_NAME, CREDITS_USED AS TOTAL_CREDITS from SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY where START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP()) group by WAREHOUSE_NAME order by 2 desc;
Show answer and explanation
Correct answer: B
Explanation
To satisfy the scenario, the query must do three things correctly: aggregate at the warehouse level, filter the correct reporting window, and apply deterministic ordering. In Snowflake, GROUP BY controls aggregation granularity. Including START_TIME in the GROUP BY creates a more detailed grouping level than required, resulting in multiple rows per warehouse. For a report covering the last 7 complete days, it is a best practice to bound the time range from the beginning of the day 7 days ago up to the beginning of the current day, rather than using a rolling CURRENT_TIMESTAMP()-based window, which would include partial current-day data. For sorting, ORDER BY TOTAL_CREDITS DESC provides the ranking, but when equal totals exist, adding a secondary key such as WAREHOUSE_NAME ASC ensures stable and predictable output. This aligns with Snowflake SQL behavior for GROUP BY and ORDER BY as documented in the SQL reference.
- A. Incorrect.
Incorrect. Grouping by both WAREHOUSE_NAME and START_TIME produces multiple rows per warehouse because metering history contains time-sliced records. That violates the requirement to return one row per warehouse. It also sorts only by total credits, so ties can appear in a non-deterministic order across executions if no secondary sort key is specified.
- B. Correct.
Correct. Grouping only by WAREHOUSE_NAME aggregates usage into a single row per warehouse. Filtering from the start of the day 7 days ago up to, but not including, the start of the current day correctly captures the last 7 complete days. Ordering by TOTAL_CREDITS DESC ranks warehouses from highest to lowest usage, and adding WAREHOUSE_NAME ASC as a secondary sort key makes tie handling deterministic.
- C. Incorrect.
Incorrect. DISTINCT does not replace the need for GROUP BY when using SUM on non-aggregated columns. This query is not valid as written because WAREHOUSE_NAME is selected alongside an aggregate without a proper GROUP BY clause. It also does not address the requirement for complete-day filtering or deterministic tie ordering.
- D. Incorrect.
Incorrect. This query attempts to group by WAREHOUSE_NAME while selecting CREDITS_USED without aggregation, which is not valid SQL in Snowflake. Even if corrected syntactically, using raw CREDITS_USED would not produce total credits per warehouse across the reporting window. Ordering by column position is allowed, but it does not solve the aggregation problem.