A Databricks SQL analyst needs to find all customers who placed more than 5 orders in 2024. Which query correctly uses a GROUP BY with a HAVING clause to achieve this?

Question

Accepted Answer

B. SELECT customer_id, COUNT(*) as order_count FROM orders WHERE YEAR(order_date) = 2024 GROUP BY customer_id HAVING COUNT(*) > 5. In ANSI SQL (which Databricks SQL adheres to), aggregate filter conditions must use HAVING, not WHERE. The WHERE clause filters rows before aggregation; HAVING filters after aggregation. Option A incorrectly uses an aggregate function in the WHERE clause. Option C omits GROUP BY, which is required when using HAVING with an aggregate. Option D places WHERE after HAVING, which is invalid SQL syntax.

A Databricks SQL analyst needs to find all customers who placed more than 5 orders in 2024. Which query correctly uses a GROUP BY with a HAVING clause to achieve this?

Related Questions

Discussion