Active Fleet Vehicles by Registration State — SQL Problem
Indian vehicle registration numbers start with a two-letter state code (KA01AB1234 is Karnataka, MH12PQ4455 Maharashtra).
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #301
Problem statement
Indian vehicle registration numbers start with a two-letter state code (KA01AB1234 is Karnataka, MH12PQ4455 Maharashtra). The onboarding team typed some numbers with stray spaces before or after them.
For every state code with at least one active vehicle (active = 1), return state_code (the first two characters of the registration number once the surrounding spaces are removed) and active_vehicles (how many active vehicles carry it). Order by active_vehicles descending, then state_code ascending.
Tables
Table: Vehicle
| Column | Type |
|---|---|
| vehicle_id | int |
| registration_no | varchar |
| vehicle_type | enum(auto, bike, cab) |
| active | bool |
Primary key: vehicle_id.
Registration numbers are upper case; some carry leading or trailing spaces. active is 1 for a vehicle currently on the platform.
Examples
Example 1
Vehicle
| vehicle_id | registration_no | vehicle_type | active |
|---|---|---|---|
| 1 | KA01AB1234 | cab | 1 |
| 2 | KA05MN9876 | bike | 1 |
| 3 | MH12PQ4455 | cab | 1 |
| 4 | TN09XY0001 | auto | 0 |
| 5 | MH02CD7788 | auto | 1 |
| 6 | DL3CRS5521 | cab | 0 |
| 7 | KA53JK3002 | bike | 0 |
Output
| state_code | active_vehicles |
|---|---|
| KA | 2 |
| MH | 2 |
How to solve Active Fleet Vehicles by Registration State
The grouping key is computed, not stored: the state code is the first two characters of the registration number. LEFT(registration_no, 2) would be enough if the data were clean, but a value typed as ' KA05MN9876' starts with a space, so its first two characters are ' K' — a bogus state. Applying TRIM first removes the surrounding spaces, and LEFT(TRIM(registration_no), 2) gives KA for both spellings.
Inactive vehicles are filtered in WHERE before grouping, so a state whose vehicles are all inactive never forms a group, which is what "at least one active vehicle" asks. The alternative keeps every row, counts actives with a CASE, and removes the zero groups with HAVING — same answer, a little more work.
Group by the same expression you select (or by its alias in MySQL). The order is by count descending with the code as the tie-breaker. The query is one scan; no index can help with a computed key unless you store the state as its own column, which is what a production schema would do.
Reference solution (MySQL)
SELECT LEFT(TRIM(registration_no), 2) AS state_code, COUNT(*) AS active_vehicles
FROM Vehicle
WHERE active = 1
GROUP BY LEFT(TRIM(registration_no), 2)
ORDER BY active_vehicles DESC, state_codeAnother way
SELECT state_code, SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_vehicles FROM (SELECT SUBSTRING(TRIM(registration_no), 1, 2) AS state_code, active FROM Vehicle) v GROUP BY state_code HAVING SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) > 0 ORDER BY active_vehicles DESC, state_code← Shipments Booked at Each Courier Hub · Ride Request Outcome Mix: Rider, Driver or No Driver →