Overseas Players in the Auction Pool by Role

Easy
452.

Overseas Players in the Auction Pool by Role

EasyStrings

MySQL

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

ColumnType
player_idint
player_namevarchar
reg_codevarchar
base_price_lakhint

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.

Example

The rows must come back in the order shown.

AuctionPool

player_idplayer_namereg_codebase_price_lakh
1Liam BrooksOS-BAT-0231200
2Rohan IyerIN-BWL-004250
3Noah FernandoOS-AR-0412150
4David MalanOS-WK-0118200
5Kabir KhanIN-AR-0077100
6Alex CarterOS-BWL-039075

Output

player_namerolebase_price_lakh
David MalanWK200
Liam BrooksBAT200
Noah FernandoAR150
Alex CarterBWL75
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.

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

ColumnType
player_idint
player_namevarchar
reg_codevarchar
base_price_lakhint

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_idplayer_namereg_codebase_price_lakh
1Liam BrooksOS-BAT-0231200
2Rohan IyerIN-BWL-004250
3Noah FernandoOS-AR-0412150
4David MalanOS-WK-0118200
5Kabir KhanIN-AR-0077100
6Alex CarterOS-BWL-039075

Output

player_namerolebase_price_lakh
David MalanWK200
Liam BrooksBAT200
Noah FernandoAR150
Alex CarterBWL75

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_name

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

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