Label Each Campaign by Its Monthly Budget Tier

Easy
280.

Label Each Campaign by Its Monthly Budget Tier

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

ColumnType
campaign_idint
campaign_namevarchar
monthly_budgetint

Primary key: campaign_id.

One row per campaign. monthly_budget is in rupees, or NULL before the plan is approved.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Campaign

campaign_idcampaign_namemonthly_budget
1Navratri Ethnic Wear250000
2Raksha Bandhan Gifts50000
3Winter Skincare49999
4Eid Festive EditNULL
5Wedding Season Gold200000
6Ganesh Chaturthi Decor120000

Output

campaign_idcampaign_namebudget_tier
1Navratri Ethnic Wearlarge
2Raksha Bandhan Giftsmid
3Winter Skincaresmall
4Eid Festive Editunset
5Wedding Season Goldlarge
6Ganesh Chaturthi Decormid
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
campaign_idint
campaign_namevarchar
monthly_budgetint

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_idcampaign_namemonthly_budget
1Navratri Ethnic Wear250000
2Raksha Bandhan Gifts50000
3Winter Skincare49999
4Eid Festive EditNULL
5Wedding Season Gold200000
6Ganesh Chaturthi Decor120000

Output

campaign_idcampaign_namebudget_tier
1Navratri Ethnic Wearlarge
2Raksha Bandhan Giftsmid
3Winter Skincaresmall
4Eid Festive Editunset
5Wedding Season Goldlarge
6Ganesh Chaturthi Decormid

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 Campaign

Another 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 Campaign

Another 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 →