Databricks Data Engineer Associate Question 215
Single answerYou are working with a Delta table named 'sales_data' containing columns 'region', 'sales_amount', and 'sales_category'. You want to create a new column called 'performance_label' that categorizes sales performance based on the 'sales_amount' column as follows:
- 'High' if sales_amount is greater than 10000
- 'Medium' if sales_amount is between 5000 and 10000 (inclusive)
- 'Low' if sales_amount is less than 5000
Which of the following SQL queries correctly uses a CASE/WHEN statement to implement this logic?
- A
SELECT region, sales_amount, sales_category, CASE WHEN sales_amount > 10000 THEN 'High' WHEN sales_amount BETWEEN 5000 AND 10000 THEN 'Medium' ELSE 'Low' END AS performance_label FROM sales_data
- B
SELECT region, sales_amount, sales_category, CASE WHEN sales_amount > 10000 THEN 'High' WHEN sales_amount >= 5000 AND sales_amount < 10000 THEN 'Medium' ELSE 'Low' END AS performance_label FROM sales_data
- C
SELECT region, sales_amount, sales_category, CASE WHEN sales_amount > 10000 THEN 'High' WHEN sales_amount >= 5000 OR sales_amount <= 10000 THEN 'Medium' ELSE 'Low' END AS performance_label FROM sales_data
- D
SELECT region, sales_amount, sales_category, CASE WHEN sales_amount > 10000 THEN 'High' WHEN sales_amount < 5000 THEN 'Low' ELSE 'Medium' END AS performance_label FROM sales_data
Show answer and explanation
Correct answer: A
Explanation
The CASE/WHEN statement is used to implement conditional logic in SQL. In this scenario, the correct query must ensure that 'sales_amount' is categorized into 'High', 'Medium', and 'Low' based on the specified ranges. Option 1 correctly implements this logic by using proper range conditions and an ELSE clause.
- A. Correct.
This is the correct query. It uses the CASE/WHEN statement properly to classify sales_amount into 'High', 'Medium', and 'Low' categories. It correctly checks for values greater than 10000, between 5000 and 10000 (inclusive), and less than 5000.
- B. Incorrect.
This query is almost correct, but it incorrectly checks for the 'Medium' range by using 'sales_amount >= 5000 AND sales_amount < 10000', which excludes 10000 from the range.
- C. Incorrect.
This query is incorrect because the condition for 'Medium' uses OR instead of AND, which results in a logical error. The range check for 'Medium' will not work as expected.
- D. Incorrect.
This query is incorrect because the ELSE condition is used for 'Medium', but the condition for 'Low' is checked before 'Medium', leading to incorrect categorization.