League Table Position and Tier Banding

Hard

A competition analytics team needs a deterministic standings board from raw team results. Table: TeamStats team_id: unique identifier for a team team_name: team label matches_played: number of matches played wins: number of wins draws: number of draws losses: number of losses Scoring rules: win = 3 points draw = 1 point loss = 0 points Per team, compute: points = wins * 3 + draws position = competition rank by points descending (ties share the same rank) tier using position-based cutoffs with N = total teams: Tier 1 if position <= ceil(N / 3) Tier 2 if position <= ceil(2N / 3) Tier 3 otherwise Tie-boundary policy: If multiple teams share points at a tier cutoff, all tied teams remain in the higher tier. Return columns in this exact order: team_name points position tier Final ordering: points descending team_name ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

TeamStats:
| team_id | team_name      | matches_played | wins | draws | losses |
|---------|----------------|----------------|------|-------|--------|
| 1       | Crimson Rovers | 26             | 20   | 2     | 4      |
| 2       | Falcon City    | 28             | 18   | 6     | 4      |
| 3       | Blue Harbor    | 30             | 16   | 8     | 6      |
| 4       | Northbridge    | 30             | 14   | 9     | 7      |
| 5       | Oakgate        | 30             | 14   | 9     | 7      |
| 6       | Ironfield      | 30             | 10   | 9     | 11     |
| 7       | Kingsport      | 30             | 9    | 9     | 12     |
| 8       | Lakeview       | 30             | 8    | 12    | 10     |
| 9       | Moorland       | 30             | 7    | 8     | 15     |
| 10      | Riverstead     | 30             | 6    | 9     | 15     |

Output

[
  {"team_name":"Crimson Rovers","points":62,"position":1,"tier":"Tier 1"},
  {"team_name":"Falcon City","points":60,"position":2,"tier":"Tier 1"},
  {"team_name":"Blue Harbor","points":56,"position":3,"tier":"Tier 1"},
  {"team_name":"Northbridge","points":51,"position":4,"tier":"Tier 1"},
  {"team_name":"Oakgate","points":51,"position":4,"tier":"Tier 1"},
  {"team_name":"Ironfield","points":39,"position":6,"tier":"Tier 2"},
  {"team_name":"Kingsport","points":36,"position":7,"tier":"Tier 2"},
  {"team_name":"Lakeview","points":36,"position":7,"tier":"Tier 2"},
  {"team_name":"Moorland","points":29,"position":9,"tier":"Tier 3"},
  {"team_name":"Riverstead","points":27,"position":10,"tier":"Tier 3"}
]

Explanation

For N=10, the cutoff positions are ceil(10/3)=4 and ceil(20/3)=7. Ties at positions 4 and 7 are kept in the higher tier.

Example 2

Input

TeamStats:
| team_id | team_name   | matches_played | wins | draws | losses |
|---------|-------------|----------------|------|-------|--------|
| 101     | Atlas FC    | 18             | 10   | 3     | 5      |
| 102     | Boreal FC   | 18             | 9    | 2     | 7      |
| 103     | Cedar Town  | 18             | 6    | 4     | 8      |
| 104     | Delta Town  | 18             | 4    | 3     | 11     |

Output

[
  {"team_name":"Atlas FC","points":33,"position":1,"tier":"Tier 1"},
  {"team_name":"Boreal FC","points":29,"position":2,"tier":"Tier 1"},
  {"team_name":"Cedar Town","points":22,"position":3,"tier":"Tier 2"},
  {"team_name":"Delta Town","points":15,"position":4,"tier":"Tier 3"}
]

Explanation

For N=4, cutoff positions are 2 and 3, so tiers split as 2/1/1.

Example 3

Input

TeamStats:
| team_id | team_name    | matches_played | wins | draws | losses |
|---------|--------------|----------------|------|-------|--------|
| 301     | Eastfield    | 12             | 5    | 3     | 4      |
| 302     | Glenford     | 12             | 4    | 6     | 2      |
| 303     | Highwater    | 12             | 6    | 0     | 6      |
| 304     | Ivybridge    | 12             | 5    | 3     | 4      |
| 305     | Juniper Vale | 12             | 3    | 9     | 0      |

Output

[
  {"team_name":"Eastfield","points":18,"position":1,"tier":"Tier 1"},
  {"team_name":"Glenford","points":18,"position":1,"tier":"Tier 1"},
  {"team_name":"Highwater","points":18,"position":1,"tier":"Tier 1"},
  {"team_name":"Ivybridge","points":18,"position":1,"tier":"Tier 1"},
  {"team_name":"Juniper Vale","points":18,"position":1,"tier":"Tier 1"}
]

Explanation

All teams have identical points, so all share rank 1 and therefore all belong to Tier 1.

Constraints

1 <= number of rows in TeamStats <= 200000
team_id is unique within each test case
1 <= team_id <= 1000000000
team_name is non-empty text
0 <= wins, draws, losses <= 100000
matches_played = wins + draws + losses
points must be computed as wins * 3 + draws
position uses competition rank by points descending
tier cutoffs are based on position using ceil(N/3) and ceil(2N/3)
ties at cutoff positions must stay in the higher tier
Return columns in exact order: team_name, points, position, tier
Sort final output by points DESC and team_name ASC
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [team_name, points, position, tier]

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|----------------|----------------|------|-------|--------| | 1 | Crimson Rovers | 26 | 20 | 2 | 4 | | 2 | Falcon City | 28 | 18 | 6 | 4 | | 3 | Blue Harbor | 30 | 16 | 8 | 6 | | 4 | Northbridge | 30 | 14 | 9 | 7 | | 5 | Oakgate | 30 | 14 | 9 | 7 | | 6 | Ironfield | 30 | 10 | 9 | 11 | | 7 | Kingsport | 30 | 9 | 9 | 12 | | 8 | Lakeview | 30 | 8 | 12 | 10 | | 9 | Moorland | 30 | 7 | 8 | 15 | | 10 | Riverstead | 30 | 6 | 9 | 15 |

[ {"team_name":"Crimson Rovers","points":62,"position":1,"tier":"Tier 1"}, {"team_name":"Falcon City","points":60,"position":2,"tier":"Tier 1"}, {"team_name":"Blue Harbor","points":56,"position":3,"tier":"Tier 1"}, {"team_name":"Northbridge","points":51,"position":4,"tier":"Tier 1"}, {"team_name":"Oakgate","points":51,"position":4,"tier":"Tier 1"}, {"team_name":"Ironfield","points":39,"position":6,"tier":"Tier 2"}, {"team_name":"Kingsport","points":36,"position":7,"tier":"Tier 2"}, {"team_name":"Lakeview","points":36,"position":7,"tier":"Tier 2"}, {"team_name":"Moorland","points":29,"position":9,"tier":"Tier 3"}, {"team_name":"Riverstead","points":27,"position":10,"tier":"Tier 3"} ]

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|-------------|----------------|------|-------|--------| | 101 | Atlas FC | 18 | 10 | 3 | 5 | | 102 | Boreal FC | 18 | 9 | 2 | 7 | | 103 | Cedar Town | 18 | 6 | 4 | 8 | | 104 | Delta Town | 18 | 4 | 3 | 11 |

[ {"team_name":"Atlas FC","points":33,"position":1,"tier":"Tier 1"}, {"team_name":"Boreal FC","points":29,"position":2,"tier":"Tier 1"}, {"team_name":"Cedar Town","points":22,"position":3,"tier":"Tier 2"}, {"team_name":"Delta Town","points":15,"position":4,"tier":"Tier 3"} ]

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|--------------|----------------|------|-------|--------| | 301 | Eastfield | 12 | 5 | 3 | 4 | | 302 | Glenford | 12 | 4 | 6 | 2 | | 303 | Highwater | 12 | 6 | 0 | 6 | | 304 | Ivybridge | 12 | 5 | 3 | 4 | | 305 | Juniper Vale | 12 | 3 | 9 | 0 |

[ {"team_name":"Eastfield","points":18,"position":1,"tier":"Tier 1"}, {"team_name":"Glenford","points":18,"position":1,"tier":"Tier 1"}, {"team_name":"Highwater","points":18,"position":1,"tier":"Tier 1"}, {"team_name":"Ivybridge","points":18,"position":1,"tier":"Tier 1"}, {"team_name":"Juniper Vale","points":18,"position":1,"tier":"Tier 1"} ]