✏️ Explanatory Question
Level: Coding Round — Tests CASE-based bucketing plus the tricky "show bands with zero employees" completeness requirement.
The Puzzle: Group employees into salary bands — Low (below 50k), Medium (50k to 80k), and High (above 80k) — and count how many employees fall in each band. Every band must appear in the result, even if it has zero employees.
employees| id | name | salary |
|---|---|---|
| 1 | Rumman | 90000 |
| 2 | Krushna | 65000 |
| 3 | Swetha | 70000 |
| 4 | Ritesh | 40000 |
| salary_band | employee_count |
|---|---|
| Low | 1 |
| Medium | 2 |
| High | 1 |
Ritesh (40k)=Low, Krushna & Swetha (65k, 70k)=Medium, Rumman (90k)=High.
The core idea — CASE to bucket, then GROUP BY the bucket: Use a CASE expression to assign each row a band label, then GROUP BY that same expression to count per band. The subtle part is making empty bands still show up, which a plain GROUP BY cannot do on its own.
SELECT
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'High'
END AS salary_band,
COUNT(*) AS employee_count
FROM employees
GROUP BY salary_band
ORDER BY FIELD(salary_band, 'Low', 'Medium', 'High');The completeness trap: If no employee falls in a band (say nobody is "Low"), that band vanishes from the output — GROUP BY only produces rows for data that exists. To guarantee all three bands always appear, you must LEFT JOIN against a fixed list of bands.
-- One row, three columns: always shows every band (0 when empty)
SELECT
SUM(salary < 50000) AS Low,
SUM(salary BETWEEN 50000 AND 80000) AS Medium,
SUM(salary > 80000) AS High
FROM employees;
-- A boolean condition is 1/0, so SUM() counts matching rows per band-- Define the bands, then LEFT JOIN so empty bands show as 0
WITH bands AS (
SELECT 'Low' AS band, 0 AS lo, 49999 AS hi
UNION ALL SELECT 'Medium', 50000, 80000
UNION ALL SELECT 'High', 80001, 99999999
)
SELECT b.band AS salary_band, COUNT(e.id) AS employee_count
FROM bands b
LEFT JOIN employees e ON e.salary BETWEEN b.lo AND b.hi
GROUP BY b.band
ORDER BY FIELD(b.band, 'Low', 'Medium', 'High'); Why COUNT(e.id) not COUNT(*) here: In the LEFT JOIN, an empty band still produces one row (with NULL employee columns). COUNT(*) would count that phantom row as 1; COUNT(e.id) counts only real matched employees, correctly giving 0 for empty bands. (Same lesson as the earlier LEFT JOIN counting puzzle.)
Interviewer follow-up: "Make the band thresholds configurable without editing the query." → Move the band definitions into a small lookup table (salary_bands(band, low, high)) and join against it, exactly like Solution 3 but reading from a real table. Business users can then adjust thresholds by updating data, not code — a clean, maintainable design.