You want to create a new column risk_level in a dbt model based on score: 'high' if score > 80, 'medium' if score between 50 and 80, and 'low' otherwise. Which SQL snippet correctly implements this?
hard📝 Application Q8 of 15
dbt - Jinja in dbt
You want to create a new column risk_level in a dbt model based on score: 'high' if score > 80, 'medium' if score between 50 and 80, and 'low' otherwise. Which SQL snippet correctly implements this?
ACASE WHEN score > 80 THEN 'high' WHEN score > 50 THEN 'medium' ELSE 'low' END
BCASE WHEN score > 80 THEN 'high' WHEN score BETWEEN 50 AND 80 THEN 'medium' ELSE 'low' END
CCASE WHEN score >= 80 THEN 'high' WHEN score > 50 THEN 'medium' ELSE 'low' END
DCASE WHEN score > 80 THEN 'high' ELSE 'medium' END
Step-by-Step Solution
Solution:
Step 1: Understand the score ranges for risk levels
'high' if score > 80, 'medium' if score between 50 and 80 inclusive, 'low' otherwise.
Step 2: Check which snippet correctly matches these ranges
CASE WHEN score > 80 THEN 'high' WHEN score BETWEEN 50 AND 80 THEN 'medium' ELSE 'low' END uses BETWEEN 50 AND 80 for medium, which includes 50 and 80, matching the requirement exactly.
Final Answer:
CASE WHEN score > 80 THEN 'high' WHEN score BETWEEN 50 AND 80 THEN 'medium' ELSE 'low' END -> Option B
Quick Check:
Use BETWEEN for inclusive ranges [OK]
Quick Trick:Use BETWEEN for inclusive range conditions [OK]
Common Mistakes:
MISTAKES
Using > 50 excludes 50
Overlapping conditions
Missing ELSE branch
Master "Jinja in dbt" in dbt
9 interactive learning modes - each teaches the same concept differently
