Label Each Campaign by Its Monthly Budget Tier — SQL Problem
Media planners sort campaigns into budget tiers before the approval meeting. A campaign whose monthly_budget is NULL has not been planned yet.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #280
Problem statement
Media planners sort campaigns into budget tiers before the approval meeting. A campaign whose monthly_budget is NULL has not been planned yet.
Return campaign_id, campaign_name and budget_tier: 'unset' when the budget is NULL, 'small' below ₹50,000, 'mid' from ₹50,000 up to but not including ₹2,00,000, and 'large' at ₹2,00,000 or more. Return the rows in any order.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| monthly_budget | int |
Primary key: campaign_id.
One row per campaign. monthly_budget is in rupees, or NULL before the plan is approved.
Examples
Example 1
Campaign
| campaign_id | campaign_name | monthly_budget |
|---|---|---|
| 1 | Navratri Ethnic Wear | 250000 |
| 2 | Raksha Bandhan Gifts | 50000 |
| 3 | Winter Skincare | 49999 |
| 4 | Eid Festive Edit | NULL |
| 5 | Wedding Season Gold | 200000 |
| 6 | Ganesh Chaturthi Decor | 120000 |
Output
| campaign_id | campaign_name | budget_tier |
|---|---|---|
| 1 | Navratri Ethnic Wear | large |
| 2 | Raksha Bandhan Gifts | mid |
| 3 | Winter Skincare | small |
| 4 | Eid Festive Edit | unset |
| 5 | Wedding Season Gold | large |
| 6 | Ganesh Chaturthi Decor | mid |
How to solve Label Each Campaign by Its Monthly Budget Tier
A searched CASE returns the result of the first branch whose condition is true. Checking IS NULL first, then < 50000, then < 200000, leaves only budgets of ₹2,00,000 or more for ELSE, and each boundary value falls on the right side: 50,000 is not below 50,000, so it is mid; 2,00,000 is not below 2,00,000, so it is large.
NULL needs care: every comparison with it is unknown, so with no explicit branch it would reach ELSE and be labelled large. The second alternative turns that around — testing the large tiers first and letting NULL fall through every comparison to ELSE 'unset'. Nested IF calls are MySQL's shorter spelling of the same decision. One scan of the table.
Reference solution (MySQL)
SELECT campaign_id, campaign_name,
CASE
WHEN monthly_budget IS NULL THEN 'unset'
WHEN monthly_budget < 50000 THEN 'small'
WHEN monthly_budget < 200000 THEN 'mid'
ELSE 'large'
END AS budget_tier
FROM CampaignAnother way
SELECT campaign_id, campaign_name, IF(monthly_budget IS NULL, 'unset', IF(monthly_budget < 50000, 'small', IF(monthly_budget < 200000, 'mid', 'large'))) AS budget_tier FROM CampaignAnother way
SELECT campaign_id, campaign_name, CASE WHEN monthly_budget >= 200000 THEN 'large' WHEN monthly_budget >= 50000 THEN 'mid' WHEN monthly_budget >= 0 THEN 'small' ELSE 'unset' END AS budget_tier FROM Campaign← Paid Media Spend per Channel in March 2024 · Sign-ups by UTM Source Read From Landing URLs →