Databricks Data Engineer Associate Question 217
Single answerYou are working with a sales dataset in a Databricks table named sales_data. The table contains the columns: sales_amount, region, and category. You need to create a query that categorizes the sales data into three groups: 'High' for sales greater than 1000, 'Medium' for sales between 500 and 1000 (inclusive), and 'Low' for sales less than 500. Which of the following SQL queries correctly implements this logic using CASE/WHEN?
- A
SELECT sales_amount, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
- B
SELECT sales_amount, CASE WHEN sales_amount >= 1000 THEN 'High' WHEN sales_amount > 500 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
- C
SELECT sales_amount, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount >= 500 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
- D
SELECT sales_amount, CASE WHEN sales_amount <= 1000 THEN 'High' WHEN sales_amount >= 500 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
Show answer and explanation
Correct answer: A
Explanation
The CASE/WHEN expression in SQL allows for custom control flow and conditional logic. It evaluates conditions sequentially, and the first condition that matches is applied. In this scenario, the correct query uses BETWEEN to define an inclusive range for 'Medium' and ensures the conditions for 'High' and 'Low' are mutually exclusive and correctly ordered.
- A. Correct.
This is the correct query. The CASE/WHEN logic accurately categorizes sales greater than 1000 as 'High', sales between 500 and 1000 (inclusive) as 'Medium', and sales less than 500 as 'Low'. The BETWEEN operator ensures an inclusive range for 'Medium'.
- B. Incorrect.
This query is incorrect because it categorizes sales equal to 1000 as 'Medium' instead of 'High', due to the use of '>=' in the second condition.
- C. Incorrect.
This query is incorrect because sales equal to 500 would incorrectly fall under 'Medium' instead of being part of the 'Low' category, as it uses '>=' for the second condition.
- D. Incorrect.
This query is incorrect because it misclassifies sales less than or equal to 1000 as 'High', which does not align with the problem requirements.