You are tasked with transforming a dataset of sales transactions in Databricks. The dataset contains a column salesamount and you need to create a new column salescategory based on the following rules: - If salesamount is greater than 1000, label it as 'High'. - If salesamount is between 500 and 1000 (inclusive), label it as 'Medium'. - Otherwise, label it as 'Low'. Which of the following SQL expressions correctly implements this logic using CASE/WHEN?

B. CASE WHEN salesamount 1000 THEN 'High' WHEN salesamount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS salescategory

A. CASE WHEN salesamount 1000 THEN 'High' WHEN salesamount = 500 AND salesamount <= 1000 THEN 'Medium' ELSE 'Low' END AS salescategory

B. CASE WHEN salesamount 1000 THEN 'High' WHEN salesamount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS salescategory

C. CASE WHEN salesamount = 1000 THEN 'High' WHEN salesamount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS salescategory

D. CASE WHEN salesamount 1000 THEN 'High' WHEN salesamount < 500 THEN 'Medium' ELSE 'Low' END AS salescategory

Databricks Data Engineer Associate Question 214: You are tasked with transforming a dataset of sales transactions in... | Databricks Data Engineer Associate Dumps

Databricks Data Engineer Associate practice question 214 of 532

Databricks Certified Data Engineer Associate. Associate level, Databricks. Free question with the correct answer and a full explanation.

Databricks Data Engineer Associate Question 214

Single answer

You are tasked with transforming a dataset of sales transactions in Databricks. The dataset contains a column sales_amount and you need to create a new column sales_category based on the following rules:

If sales_amount is greater than 1000, label it as 'High'.
If sales_amount is between 500 and 1000 (inclusive), label it as 'Medium'.
Otherwise, label it as 'Low'. Which of the following SQL expressions correctly implements this logic using CASE/WHEN?

A
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount >= 500 AND sales_amount <= 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
B
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
C
CASE WHEN sales_amount >= 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category
D
CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount < 500 THEN 'Medium' ELSE 'Low' END AS sales_category

Show answer and explanation

Correct answer: B

Explanation

The correct answer is the second option because it implements the given rules accurately and efficiently using the CASE/WHEN statement. The BETWEEN operator is used to check if sales_amount falls between 500 and 1000 inclusively, while values greater than 1000 are labeled as 'High', and all other values are labeled as 'Low'.

A. Incorrect.
This option works but is slightly verbose as it uses sales_amount >= 500 AND sales_amount <= 1000 instead of the simpler BETWEEN syntax.
B. Correct.
This option is correct. It uses the BETWEEN operator to define the range for 'Medium', which is concise and accurate. All conditions are implemented correctly.
C. Incorrect.
This option is incorrect because it assigns 'High' to sales_amount >= 1000 instead of strictly greater than 1000 as required in the problem statement.
D. Incorrect.
This option is incorrect because it assigns 'Medium' to sales_amount < 500, which does not match the rules outlined in the problem statement.

Databricks Data Engineer Associate practice question 214 of 532

Databricks Data Engineer Associate Question 214

Take a Databricks Data Engineer Associate practice test under exam conditions

More Databricks Data Engineer Associate practice questions