Databricks Data Engineer Associate Question 209
Single answerYou are working with a Delta table named 'sales'. The table contains the following columns: 'order_id', 'product_category', 'revenue', and 'order_status'. You want to create a query that adds a new column named 'priority' where:
- 'High' is assigned to rows with 'revenue' greater than 1000.
- 'Medium' is assigned to rows with 'revenue' between 500 and 1000 (inclusive).
- 'Low' is assigned to rows with 'revenue' less than 500. Which of the following SQL queries correctly implements the required logic?
- A
SELECT order_id, product_category, revenue, order_status, CASE WHEN revenue > 1000 THEN 'High' WHEN revenue BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS priority FROM sales
- B
SELECT order_id, product_category, revenue, order_status, CASE WHEN revenue > 1000 THEN 'High' WHEN revenue > 500 THEN 'Medium' ELSE 'Low' END AS priority FROM sales
- C
SELECT order_id, product_category, revenue, order_status, CASE WHEN revenue >= 500 AND revenue <= 1000 THEN 'Medium' ELSE 'Low' END AS priority FROM sales
- D
SELECT order_id, product_category, revenue, order_status, IF(revenue > 1000, 'High', IF(revenue BETWEEN 500 AND 1000, 'Medium', 'Low')) AS priority FROM sales
Show answer and explanation
Correct answer: A
Explanation
The correct query uses the CASE/WHEN construct to implement the specified logic. It evaluates the conditions in the correct order, ensuring that 'High' is assigned first for revenue > 1000, followed by 'Medium' for revenue between 500 and 1000, and 'Low' as the default case. The CASE/WHEN construct is recommended for handling such conditional logic in SQL when working with Databricks.
- A. Correct.
This query correctly uses the CASE/WHEN construct to implement all three conditions: 'High' for revenue > 1000, 'Medium' for revenue between 500 and 1000, and 'Low' as the default case.
- B. Incorrect.
This query incorrectly assigns 'Medium' to rows with revenue > 500, which would result in overlaps with the 'High' condition for revenue > 1000.
- C. Incorrect.
This query is incomplete as it only considers two conditions ('Medium' and 'Low') and ignores the 'High' condition.
- D. Incorrect.
This query uses the IF statement instead of the CASE/WHEN construct, which is not the approach being tested in this question.