Overseas Players in the Auction Pool by Role — SQL Problem
Every player registered for the T20 auction gets a code <origin>-<role>-<number>: the origin is IN for an Indian player or OS for an overseas one, the role…
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #452
Problem statement
Every player registered for the T20 auction gets a code <origin>-<role>-<number>: the origin is IN for an Indian player or OS for an overseas one, the role is BAT, BWL, AR (all-rounder) or WK (wicket-keeper), and the number is four digits, for example OS-AR-0412.
Franchises can sign only a few overseas players, so the auctioneer wants them listed separately. Return every overseas player with the columns player_name, role (the middle part of the code, such as AR) and base_price_lakh. Order the rows by base_price_lakh descending, then by player_name.
Tables
Table: AuctionPool
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
| reg_code | varchar |
| base_price_lakh | int |
Primary key: player_id.
One row per registered player. reg_code is always in upper case and well formed; base_price_lakh is the reserve price in lakh rupees.
Examples
Example 1
AuctionPool
| player_id | player_name | reg_code | base_price_lakh |
|---|---|---|---|
| 1 | Liam Brooks | OS-BAT-0231 | 200 |
| 2 | Rohan Iyer | IN-BWL-0042 | 50 |
| 3 | Noah Fernando | OS-AR-0412 | 150 |
| 4 | David Malan | OS-WK-0118 | 200 |
| 5 | Kabir Khan | IN-AR-0077 | 100 |
| 6 | Alex Carter | OS-BWL-0390 | 75 |
Output
| player_name | role | base_price_lakh |
|---|---|---|
| David Malan | WK | 200 |
| Liam Brooks | BAT | 200 |
| Noah Fernando | AR | 150 |
| Alex Carter | BWL | 75 |
How to solve Overseas Players in the Auction Pool by Role
Two string operations answer this: a prefix test for the origin and an extraction for the role.
The origin is always the first two letters, so reg_code LIKE 'OS-%' (or LEFT(reg_code, 2) = 'OS') keeps the overseas players. The role is harder because it is not a fixed width — AR and WK have two letters, BAT and BWL three — so a fixed SUBSTRING(reg_code, 4, 3) would return AR- for an all-rounder. SUBSTRING_INDEX splits on the delimiter instead: SUBSTRING_INDEX(reg_code, '-', 2) is OS-AR, and taking the part after its last hyphen with -1 gives AR.
Because the origin (OS-) and the tail (-0412) have fixed lengths, the role is also everything from position 4 with eight characters fewer than the whole code, which is the first alternative. The order is by price, highest first, with names breaking ties. Each row is examined once.
Reference solution (MySQL)
SELECT player_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(reg_code, '-', 2), '-', -1) AS role,
base_price_lakh
FROM AuctionPool
WHERE reg_code LIKE 'OS-%'
ORDER BY base_price_lakh DESC, player_nameAnother way
SELECT player_name, SUBSTRING(reg_code, 4, CHAR_LENGTH(reg_code) - 8) AS role, base_price_lakh FROM AuctionPool WHERE LEFT(reg_code, 2) = 'OS' ORDER BY base_price_lakh DESC, player_nameAnother way
SELECT player_name, REPLACE(SUBSTRING(reg_code, 4), RIGHT(reg_code, 5), '') AS role, base_price_lakh FROM AuctionPool WHERE SUBSTRING(reg_code, 1, 3) = 'OS-' ORDER BY base_price_lakh DESC, player_name← Fantasy Teams That Forgot to Pick a Captain · Season Attendance at Each Football Stadium →