Databricks Data Engineer Associate Question 208
Select 2You are working on a Databricks SQL query that analyzes customer purchase data. The dataset contains a column 'purchase_amount' indicating the value of purchases and a column 'customer_type' indicating whether the customer is 'new' or 'existing'. You need to create a new column 'discount' that applies a 10% discount for 'new' customers who made purchases exceeding $100, a 5% discount for 'existing' customers who made purchases exceeding $200, and no discount otherwise. Which of the following SQL CASE statements correctly implements this logic?
- A
CASE WHEN customer_type = 'new' AND purchase_amount > 100 THEN 0.1 * purchase_amount WHEN customer_type = 'existing' AND purchase_amount > 200 THEN 0.05 * purchase_amount ELSE 0 END
- B
CASE WHEN customer_type = 'new' AND purchase_amount > 200 THEN 0.05 * purchase_amount WHEN customer_type = 'existing' AND purchase_amount > 100 THEN 0.1 * purchase_amount ELSE 0 END
- C
CASE WHEN customer_type = 'existing' AND purchase_amount > 200 THEN 0.05 * purchase_amount WHEN customer_type = 'new' AND purchase_amount > 100 THEN 0.1 * purchase_amount ELSE 0 END
- D
CASE WHEN customer_type = 'new' AND purchase_amount > 100 THEN 0.05 * purchase_amount WHEN customer_type = 'existing' AND purchase_amount > 200 THEN 0.1 * purchase_amount ELSE 0 END
Show answer and explanation
Correct answers: A, C
Explanation
CASE statements in SQL evaluate conditions sequentially and apply the logic defined for the first matching condition. In this scenario, both Option 1 and Option 3 correctly implement the logic of applying a 10% discount for 'new' customers making purchases over $100 and a 5% discount for 'existing' customers making purchases over $200. The order of conditions in this case does not affect the correctness of the query.
- A. Correct.
This correctly applies a 10% discount for 'new' customers with purchases over $100 and a 5% discount for 'existing' customers with purchases over $200. The conditions are evaluated in the correct order.
- B. Incorrect.
This incorrectly swaps the discount percentages for 'new' and 'existing' customers, applying 5% to 'new' customers and 10% to 'existing' customers.
- C. Correct.
This is another correct implementation, as the order of the conditions in the CASE statement does not matter as long as the logic matches the requirements.
- D. Incorrect.
This incorrectly swaps the discounts for 'new' and 'existing' customers, applying 5% to 'new' customers and 10% to 'existing' customers.