Active Fleet Vehicles by Registration State

Easy
301.

Active Fleet Vehicles by Registration State

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

ColumnTypeValues
vehicle_idint
registration_novarchar
vehicle_typeenumauto, bike, cab
activebool

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.

Example

The rows must come back in the order shown.

Vehicle

vehicle_idregistration_novehicle_typeactive
1KA01AB1234cab1
2 KA05MN9876bike1
3MH12PQ4455 cab1
4TN09XY0001auto0
5MH02CD7788auto1
6DL3CRS5521cab0
7KA53JK3002bike0

Output

state_codeactive_vehicles
KA2
MH2
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.

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

ColumnType
vehicle_idint
registration_novarchar
vehicle_typeenum(auto, bike, cab)
activebool

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_idregistration_novehicle_typeactive
1KA01AB1234cab1
2 KA05MN9876bike1
3MH12PQ4455 cab1
4TN09XY0001auto0
5MH02CD7788auto1
6DL3CRS5521cab0
7KA53JK3002bike0

Output

state_codeactive_vehicles
KA2
MH2

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_code

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