Polling Booth Turnout Bands

Easy
77.

Polling Booth Turnout Bands

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

ColumnType
booth_idint
constituencyvarchar
registered_votersint
votes_castint

Primary key: booth_id.

One row per polling booth; votes_cast never exceeds registered_voters.

Example

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

booths

booth_idconstituencyregistered_votersvotes_cast
101Hebbal1000700
102Hebbal863512
103Jayanagar20099
104Jayanagar00
105Shivajinagar941466
106Shivajinagar500250

Output

booth_idturnout_pctturnout_band
10170High
10259.3Moderate
10349.5Low
104NULLNo Roll
10549.5Low
10650Moderate
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.

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

ColumnType
booth_idint
constituencyvarchar
registered_votersint
votes_castint

Primary key: booth_id.

One row per polling booth; votes_cast never exceeds registered_voters.

Examples

Example 1

booths

booth_idconstituencyregistered_votersvotes_cast
101Hebbal1000700
102Hebbal863512
103Jayanagar20099
104Jayanagar00
105Shivajinagar941466
106Shivajinagar500250

Output

booth_idturnout_pctturnout_band
10170High
10259.3Moderate
10349.5Low
104NULLNo Roll
10549.5Low
10650Moderate

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 booths

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