Databricks Data Engineer Associate Question 212
Single answerA retail company has a transactional table named sales with columns product_id, quantity, and price. The company wants to classify each sale into three categories based on the revenue (calculated as quantity * price):
- 'High' if the revenue is greater than 1000
- 'Medium' if the revenue is between 500 and 1000 (inclusive)
- 'Low' if the revenue is less than 500
Which SQL query correctly uses the CASE/WHEN statement to achieve this classification in a new column named
revenue_category?
- A
SELECT product_id, quantity, price, CASE WHEN quantity * price > 1000 THEN 'High' WHEN quantity * price BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS revenue_category FROM sales
- B
SELECT product_id, quantity, price, CASE WHEN quantity * price > 1000 THEN 'High' WHEN quantity * price >= 500 AND quantity * price < 1000 THEN 'Medium' WHEN quantity * price < 500 THEN 'Low' END AS revenue_category FROM sales
- C
SELECT product_id, quantity, price, CASE WHEN quantity * price > 1000 THEN 'High' WHEN quantity * price BETWEEN 500 AND 1000 THEN 'Medium' WHEN quantity * price < 500 THEN 'Low' END AS revenue_category FROM sales
- D
SELECT product_id, quantity, price, CASE WHEN quantity * price BETWEEN 500 AND 1000 THEN 'Medium' WHEN quantity * price > 1000 THEN 'High' ELSE 'Low' END AS revenue_category FROM sales
Show answer and explanation
Correct answer: A
Explanation
The CASE/WHEN statement is evaluated sequentially, meaning the order of conditions matters. The correct query evaluates high revenue first (quantity * price > 1000), followed by medium revenue (quantity * price BETWEEN 500 AND 1000), and uses ELSE to handle all other cases (low revenue). This ensures accurate classification without redundant conditions.
- A. Correct.
This is the correct query. It properly uses a CASE/WHEN statement to classify sales into 'High', 'Medium', and 'Low' categories. The ELSE clause correctly captures the 'Low' category for cases not covered by the previous conditions.
- B. Incorrect.
This query is incorrect because the
WHEN quantity * price < 500condition is redundant after the ELSE clause, which already handles all remaining cases. - C. Incorrect.
This query is incorrect because the
WHEN quantity * price < 500condition is also redundant after the ELSE clause, which already captures all other cases. - D. Incorrect.
This query is incorrect because the CASE/WHEN conditions are not evaluated in the correct order. Since CASE/WHEN evaluates sequentially, the 'Medium' condition being first would incorrectly classify some 'High' revenue cases as 'Medium'.