League Table Position and Tier Banding

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

TeamStats =

[{"team_id":1,"team_name":"Crimson Rovers","matches_played":37,"wins":16,"draws":14,"losses":7},{"team_id":2,"team_name":"Falcon City","matches_played":39,"wins":17,"draws":9,"losses":13},{"team_id":3,"team_name":"Blue Harbor","matches_played":36,"wins":16,"draws":8,"losses":12},{"team_id":4,"team_name":"Northbridge","matches_played":27,"wins":14,"draws":9,"losses":4},{"team_id":5,"team_name":"Oakgate","matches_played":37,"wins":17,"draws":0,"losses":20},{"team_id":6,"team_name":"Ironfield","matches_played":27,"wins":11,"draws":6,"losses":10},{"team_id":7,"team_name":"Kingsport","matches_played":36,"wins":9,"draws":9,"losses":18},{"team_id":8,"team_name":"Lakeview","matches_played":25,"wins":9,"draws":9,"losses":7},{"team_id":9,"team_name":"Moorland","matches_played":27,"wins":7,"draws":8,"losses":12},{"team_id":10,"team_name":"Riverstead","matches_played":33,"wins":6,"draws":9,"losses":18}]

League Table Position and Tier Banding

Hints

League Table Position and Tier Banding

Hints