Databricks Data Engineer Associate Question 210
Single answerYou are working on a Databricks SQL query to analyze sales data. The sales_data table includes columns: 'sales_amount' (numeric) and 'region' (string). The requirement is to create a new column 'sales_category' which categorizes sales as 'High', 'Medium', or 'Low' based on the following criteria:
- 'High' if sales_amount is greater than 1000,
- 'Medium' if sales_amount is between 500 and 1000 (inclusive),
- 'Low' if sales_amount is less than 500. Which of the following CASE/WHEN implementations correctly achieves this?
- A
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
- B
CASE WHEN sales_amount >= 1000 THEN 'High' WHEN sales_amount > 500 AND sales_amount <= 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
- C
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount >= 500 AND sales_amount <= 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
- D
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' WHEN sales_amount < 500 THEN 'Low' END AS sales_category
Show answer and explanation
Correct answer: A
Explanation
The correct implementation of CASE/WHEN in SQL must adhere to the specified conditions in the question. The use of 'BETWEEN' is appropriate for the 'Medium' range, and values not matching any conditions should be handled by the ELSE clause. Option 1 satisfies these criteria.
- A. Correct.
This is the correct implementation. It uses the CASE/WHEN syntax properly and the BETWEEN clause correctly specifies the range for 'Medium' sales.
- B. Incorrect.
This implementation uses an incorrect condition for 'High' sales. It uses >= 1000 instead of > 1000, which does not match the criteria provided in the question.
- C. Incorrect.
This implementation uses >= for 'Medium' and <= for 'Low', which is correct, but it uses >= 1000 for 'High' sales, which is incorrect as the question specifies > 1000.
- D. Incorrect.
This implementation incorrectly includes an additional condition 'WHEN sales_amount < 500' for 'Low', which is unnecessary as any value not matching the previous conditions should fall under the ELSE clause.