Polling Booth Turnout Bands — SQL Conditional Logic Problem
After polling day the election office reviews turnout booth by booth. Turnout is votes_cast * 100 / registered_voters.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #77
Problem statement
After polling day the election office reviews turnout booth by booth. Turnout is votes_cast * 100 / registered_voters. A booth with registered_voters = 0 is a newly created booth whose roll is not loaded yet.
Return every booth with booth_id, turnout_pct (turnout rounded to one decimal) and turnout_band: 'High' when the unrounded turnout is at least 70, 'Moderate' when it is at least 50, otherwise 'Low'. For a booth with no roll, turnout_pct is NULL and the band is 'No Roll'. Return the rows in any order.
Tables
Table: booths
| Column | Type |
|---|---|
| booth_id | int |
| constituency | varchar |
| registered_voters | int |
| votes_cast | int |
Primary key: booth_id.
One row per polling booth; votes_cast never exceeds registered_voters.
Examples
Example 1
booths
| booth_id | constituency | registered_voters | votes_cast |
|---|---|---|---|
| 101 | Hebbal | 1000 | 700 |
| 102 | Hebbal | 863 | 512 |
| 103 | Jayanagar | 200 | 99 |
| 104 | Jayanagar | 0 | 0 |
| 105 | Shivajinagar | 941 | 466 |
| 106 | Shivajinagar | 500 | 250 |
Output
| booth_id | turnout_pct | turnout_band |
|---|---|---|
| 101 | 70 | High |
| 102 | 59.3 | Moderate |
| 103 | 49.5 | Low |
| 104 | NULL | No Roll |
| 105 | 49.5 | Low |
| 106 | 50 | Moderate |
How to solve Polling Booth Turnout Bands
Each booth needs a computed number and a label, which is a CASE expression over the same ratio. The first branch handles the booth without a roll, because any ratio there would divide by zero. For the number, NULLIF(registered_voters, 0) makes the divisor NULL for such a booth, so the whole expression is NULL instead of an error (MySQL returns NULL for division by zero anyway, but saying it explicitly keeps the intent clear and portable).
The thresholds apply to the unrounded turnout, and branches are tried in order, so >= 70 must come before >= 50. Writing the test as votes_cast * 100 >= 70 * registered_voters compares integers and avoids any decimal-precision doubt at exactly 70% — booth 101 (700 of 1000) is High, booth 106 (250 of 500) is Moderate.
The nested IF version is the same logic in MySQL's shorthand. One scan of the table, no grouping.
Reference solution (MySQL)
SELECT booth_id,
ROUND(votes_cast * 100 / NULLIF(registered_voters, 0), 1) AS turnout_pct,
CASE
WHEN registered_voters = 0 THEN 'No Roll'
WHEN votes_cast * 100 >= 70 * registered_voters THEN 'High'
WHEN votes_cast * 100 >= 50 * registered_voters THEN 'Moderate'
ELSE 'Low'
END AS turnout_band
FROM boothsAnother way
SELECT booth_id,
IF(registered_voters = 0, NULL, ROUND(votes_cast * 100 / registered_voters, 1)) AS turnout_pct,
IF(registered_voters = 0, 'No Roll', IF(votes_cast / registered_voters >= 0.7, 'High', IF(votes_cast / registered_voters >= 0.5, 'Moderate', 'Low'))) AS turnout_band
FROM booths← NGO Donors Grouped by Email Domain · Box Office Revenue per Show →