Question 600
For this Snowflake situation — An analyst tries to use an aggregate function in a WHERE filter before GROUP BY and receives a SQL compilation error. — the team adopts: Apply aggregate conditions in the appropriate HAVING clause or derived-query stage. Which ongoing practice BEST limits the same problem recurring?
Explanation
Correct Answer
The relevant ongoing safeguard is: Review SQL evaluation order when teaching analytic filtering. It controls the recurring exposure associated with this mechanism: WHERE filters rows before grouping while HAVING filters grouped aggregate results. The original action, apply aggregate conditions in the appropriate HAVING clause or derived-query stage., still needs operational checks when the environment or workload changes.
Incorrect Answers
This routine guards against another mechanism: CTEs organize query logic but do not themselves guarantee materialization or faster execution. The recurring exposure in the current case is: WHERE filters rows before grouping while HAVING filters grouped aggregate results. The matching preventive check is “Review SQL evaluation order when teaching analytic filtering”.
The suggested review tracks a different technical risk: Many numeric aggregate functions require values with compatible scalar types rather than arbitrary nested objects. Here the sustained control must address: WHERE filters rows before grouping while HAVING filters grouped aggregate results. Choose “Review SQL evaluation order when teaching analytic filtering”.
That safeguard is relevant to: Additional warehouse clusters primarily address concurrency, not resources for one SQL statement. The current failure instead follows from: WHERE filters rows before grouping while HAVING filters grouped aggregate results. Its prevention measure is “Review SQL evaluation order when teaching analytic filtering”.