ADA-C01 Question 314
Single answerPerform grouping, sorting, and orderingA data engineering team is building a daily finance report in Snowflake. The report must show one row per business unit with total invoice amount, and it must be sorted from highest total to lowest total. The analyst wants to avoid repeating the full aggregate expression in the ORDER BY clause and writes the following query:
SELECT business_unit, SUM(invoice_amount) AS total_amount FROM finance.invoices WHERE invoice_date >= DATEADD(day, -30, CURRENT_DATE()) GROUP BY business_unit ORDER BY total_amount DESC;
The analyst asks whether this approach is valid and what Snowflake will do. Which statement is correct?
- A
The query is valid. Snowflake allows ORDER BY to reference the SELECT-list alias total_amount after the grouped result set is produced, so rows will be sorted by the aggregated sum in descending order.
- B
The query is invalid because any expression used in ORDER BY for a grouped query must also appear in the GROUP BY clause, including aliases for aggregates.
- C
The query is invalid because Snowflake does not allow aliases created from aggregate functions to be referenced in ORDER BY; the full SUM(invoice_amount) expression must be repeated.
- D
The query is valid, but Snowflake will sort the underlying detail rows before aggregation, so the final grouped output order is not guaranteed to match total_amount DESC.
Show answer and explanation
Correct answer: A
Explanation
This question tests applied SQL behavior in Snowflake for grouping and ordering aggregated results. The query is valid because Snowflake supports using SELECT-list aliases in ORDER BY, including aliases assigned to aggregate expressions such as SUM(invoice_amount) AS total_amount. After the WHERE filter is applied and rows are grouped by business_unit, the aggregated output can be ordered by the alias total_amount.
This aligns with standard Snowflake SQL usage for GROUP BY and ORDER BY:
- GROUP BY defines the aggregation level for non-aggregated columns in the SELECT list.
- ORDER BY sorts the final result set and can reference output columns, aliases, expressions, or ordinal positions.
A practical best practice is to use explicit aliases in ORDER BY for readability rather than repeating long aggregate expressions. Candidates should also remember that if multiple rows have the same total_amount, Snowflake does not guarantee a deterministic relative order among ties unless additional ORDER BY expressions are added.
- A. Correct.
Correct. In Snowflake, ORDER BY can reference a column alias defined in the SELECT list, including an alias assigned to an aggregate expression. Because GROUP BY produces one row per business_unit, ORDER BY total_amount DESC sorts the grouped result set by the aggregated SUM(invoice_amount) value from highest to lowest. This is a common and readable pattern for reporting queries.
- B. Incorrect.
Incorrect. This reflects a common misunderstanding between GROUP BY and ORDER BY requirements. Non-aggregated selected columns generally must be grouped, but ORDER BY is allowed to sort by output expressions or aliases and does not require every ordered expression to be listed in GROUP BY. Aggregate results are especially common to sort on after grouping.
- C. Incorrect.
Incorrect. Snowflake does allow SELECT-list aliases to be used in ORDER BY, including aliases for aggregate expressions. Repeating the full SUM(invoice_amount) expression would also work, but it is not required here. Choosing this option usually comes from experience with stricter assumptions from other SQL patterns rather than Snowflake behavior.
- D. Incorrect.
Incorrect. ORDER BY applies to the final query result unless used inside a subquery with a limiting construct for a specific purpose. In this statement, Snowflake groups first and then sorts the grouped output by total_amount DESC. The idea that Snowflake sorts detail rows first in a way that determines the final grouped order is not how this query is processed logically.