Mobile Bill Bands

A mobile operator sorts its accounts into three bill bands for a pricing review:

• 'Under 25': monthly_bill below 25.00
• '25 to 75': from 25.00 up to and including 75.00
• 'Over 75': above 75.00

Return exactly three rows, one per band, with the columns bill_band (the label text exactly as above) and accounts (the number of accounts in that band). A band with no accounts must still appear with 0, and an empty table gives three rows of 0. Rows may be in any order.

Tables

phone_accounts — One row per mobile account.

Example

phone_accounts

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Strings & CASE · problem 57 of 70 · SQLite

The idea

Labelling is the easy half: a CASE with the bounds checked in order (< 25, then <= 75, then everything else) gives each account exactly one band. The boundaries follow the statement: 25.00 and 75.00 both belong to the middle band.

The hard half is the zero rows. GROUP BY only creates groups for values that exist, so a band nobody falls into simply disappears. The fix is to start from a fixed list of bands, written with VALUES in a CTE, and LEFT JOIN the labelled accounts to it. Every band survives the join; a band with no accounts gets one row padded with NULL. COUNT(l.bill_band) ignores that NULL and returns 0, while COUNT(*) would return 1.

The alternative writes one aggregate query per band and glues them with UNION ALL. An aggregate without GROUP BY always returns one row, even over zero matches, so each band is guaranteed to appear.

Solution

WITH bands(bill_band) AS (
  VALUES ('Under 25'), ('25 to 75'), ('Over 75')
),
labelled AS (
  SELECT CASE
           WHEN monthly_bill < 25 THEN 'Under 25'
           WHEN monthly_bill <= 75 THEN '25 to 75'
           ELSE 'Over 75'
         END AS bill_band
  FROM phone_accounts
)
SELECT b.bill_band,
       COUNT(l.bill_band) AS accounts
FROM bands AS b
LEFT JOIN labelled AS l
  ON l.bill_band = b.bill_band
GROUP BY b.bill_band;

Another way

SELECT 'Under 25' AS bill_band,
       COUNT(*) AS accounts
FROM phone_accounts
WHERE monthly_bill < 25
UNION ALL
SELECT '25 to 75', COUNT(*)
FROM phone_accounts
WHERE monthly_bill BETWEEN 25 AND 75
UNION ALL
SELECT 'Over 75', COUNT(*)
FROM phone_accounts
WHERE monthly_bill > 75;

Why common answers fail

Grouping the accounts can only produce bands that have accounts, so empty bands are missing instead of showing 0.

SELECT CASE WHEN monthly_bill < 25 THEN 'Under 25' WHEN monthly_bill <= 75 THEN '25 to 75' ELSE 'Over 75' END AS bill_band, COUNT(*) AS accounts FROM phone_accounts GROUP BY bill_band;

COUNT(*) counts the NULL-padded row that the LEFT JOIN creates, so an empty band shows 1 instead of 0.

WITH bands(bill_band) AS (VALUES ('Under 25'), ('25 to 75'), ('Over 75')),
labelled AS (SELECT CASE WHEN monthly_bill < 25 THEN 'Under 25' WHEN monthly_bill <= 75 THEN '25 to 75' ELSE 'Over 75' END AS bill_band FROM phone_accounts)
SELECT b.bill_band, COUNT(*) AS accounts FROM bands b LEFT JOIN labelled l ON l.bill_band = b.bill_band GROUP BY b.bill_band;

Puts exactly 25.00 and 75.00 in the wrong bands by using <= where < is required and vice versa.

WITH bands(bill_band) AS (VALUES ('Under 25'), ('25 to 75'), ('Over 75')),
labelled AS (SELECT CASE WHEN monthly_bill <= 25 THEN 'Under 25' WHEN monthly_bill < 75 THEN '25 to 75' ELSE 'Over 75' END AS bill_band FROM phone_accounts)
SELECT b.bill_band, COUNT(l.bill_band) AS accounts FROM bands b LEFT JOIN labelled l ON l.bill_band = b.bill_band GROUP BY b.bill_band;

What the interviewer is checking

Tests whether you notice that GROUP BY cannot produce categories with no data, and whether you know the dimension-table plus LEFT JOIN pattern.

Mistakes to avoid

• Grouping by the CASE label and losing empty bands.
• COUNT(*) after the LEFT JOIN, which turns empty bands into 1.
• Off-by-one boundaries at exactly 25.00 or 75.00.

Follow-ups to expect

• Return the bands in a fixed order (add a sort column to the VALUES list and ORDER BY it).
• Add the share of accounts in each band (divide by the total with * 1.0 and ROUND).
• Make the bands configurable (store them in a table with lower and upper bounds and join with BETWEEN).

In real work

Histograms for dashboards, price-tier reports and SLA buckets all need every bucket shown, including the empty ones, or charts silently shift.

Practice next