Which CASE statement correctly implements this logic?
hard📝 Application Q15 of 15
dbt - Jinja in dbt
You want to create a new column category in your dbt model based on sales amount: if sales > 1000, category is 'High'; if sales between 500 and 1000 inclusive, category is 'Medium'; otherwise, 'Low'. Which CASE statement correctly implements this logic?
ACASE WHEN sales > 1000 THEN 'High' WHEN sales > 500 THEN 'Medium' ELSE 'Low' END
BCASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END
CCASE WHEN sales > 500 AND sales <= 1000 THEN 'Medium' WHEN sales > 1000 THEN 'High' ELSE 'Low' END
DCASE WHEN sales > 1000 THEN 'High' ELSE WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END
Step-by-Step Solution
Solution:
Step 1: Analyze the conditions and their order
Check sales > 1000 first for 'High', then sales between 500 and 1000 inclusive for 'Medium', else 'Low'.
Step 2: Verify correct syntax and condition order
CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END correctly orders conditions (highest first) and uses proper inclusive range with AND. CASE WHEN sales > 500 AND sales <= 1000 THEN 'Medium' WHEN sales > 1000 THEN 'High' ELSE 'Low' END misses inclusive lower bound (sales=500 fails) and suboptimal order. CASE WHEN sales > 1000 THEN 'High' WHEN sales > 500 THEN 'Medium' ELSE 'Low' END misses inclusive lower bound. CASE WHEN sales > 1000 THEN 'High' ELSE WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END has syntax error with ELSE WHEN.
Final Answer:
CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END -> Option B
Quick Check:
Order and range checks correct in CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END [OK]
Quick Trick:Check ranges carefully and order conditions top-down [OK]
Common Mistakes:
MISTAKES
Wrong order of conditions causing misclassification
Syntax error using ELSE WHEN instead of WHEN
Missing inclusive range checks
Master "Jinja in dbt" in dbt
9 interactive learning modes - each teaches the same concept differently
