Databricks Data Engineer Associate Question 211
Single answerYou are tasked with analyzing a sales dataset in Databricks that includes a column named sales_amount. You need to create a new column sales_category that classifies each sale as 'High', 'Medium', or 'Low' based on the following rules:
- 'High' if
sales_amountis greater than 1000. - 'Medium' if
sales_amountis between 500 and 1000 (inclusive). - 'Low' if
sales_amountis less than 500. Which SQL query correctly implements this classification using a CASE/WHEN statement?
- A
SELECT *, 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 FROM sales_data
- B
SELECT *, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount <= 1000 AND sales_amount >= 500 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
- C
SELECT *, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount >= 500 AND sales_amount <= 1000 THEN 'Medium' WHEN sales_amount < 500 THEN 'Low' ELSE 'Undefined' END AS sales_category FROM sales_data
- D
SELECT *, CASE WHEN sales_amount >= 1000 THEN 'High' WHEN sales_amount >= 500 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales_data
Show answer and explanation
Correct answer: A
Explanation
The correct answer uses a CASE/WHEN statement that evaluates each condition in sequence, explicitly covering all possible ranges of sales_amount based on the rules provided in the question. The first condition (sales_amount > 1000) captures 'High', the second condition (sales_amount BETWEEN 500 AND 1000) captures 'Medium', and the third condition (sales_amount < 500) captures 'Low'. No ELSE clause is needed as all cases are explicitly defined.
- A. Correct.
Correct. This query correctly implements the CASE/WHEN logic to classify the
sales_amountvalues into 'High', 'Medium', and 'Low' categories based on the given conditions. Each condition is evaluated in sequence and no additional ELSE statement is needed here since all cases are covered. - B. Incorrect.
Incorrect. While this query works for 'High' and 'Medium' classifications, it does not explicitly check for sales_amount < 500 for the 'Low' category. Instead, it relies on the ELSE clause, which is unnecessary given the provided rules.
- C. Incorrect.
Incorrect. This query adds an additional ELSE clause with 'Undefined', which is not required by the problem. Additionally, all cases are already covered explicitly by the given rules, so the ELSE clause introduces unnecessary complexity.
- D. Incorrect.
Incorrect. This query incorrectly classifies 'High' sales as sales_amount >= 1000 instead of sales_amount > 1000, which violates the given rules. Additionally, the 'Medium' category condition is not properly bounded as it doesn’t check for an upper limit.