runtimetask Self joins · medium
Teams that hosted each other
For the 2025 regular season, return team_a and team_b (team_a < team_b) for every pair where each team hosted the other at least once, with games_at_a and games_at_b.
what to return
- Columns
team_ateam_bgames_at_agames_at_bin this order- Rows
- 225 rows · any order
- Skill
- Find relationships that hold in both directions. Refresher: Self-joins & referral relationships →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result225 rows · any order
| team_a | team_b | games_at_a | games_at_b |
|---|---|---|---|
| ANA | ATH | 6 | 7 |
| ANA | BAL | 3 | 3 |
| ANA | BOS | 3 | 3 |
first 3 of 225 rows
data you can query
Sports dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
games13 columns · 23,168 rows
| game_idtext · unique id | seasonnumber · links to seasons | game_datedate | game_numbernumber | game_typetext | venue_idtext · links to venues | home_team_idtext · links to teams | away_team_idtext · links to teams | home_scorenumber | away_scorenumber | day_nighttext | attendancenumber | duration_minutesnumber |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ANA201604040 | 2016 | 2016-04-04 | 0 | regular | ANA01 | ANA | CHN | 0 | 9 | night | 44020 | 188 |
| ANA201604050 | 2016 | 2016-04-05 | 0 | regular | ANA01 | ANA | CHN | 1 | 6 | night | 37042 | 154 |
| ANA201604070 | 2016 | 2016-04-07 | 0 | regular | ANA01 | ANA | TEX | 4 | 3 | night | 39089 | 191 |
| ANA201604080 | 2016 | 2016-04-08 | 0 | regular | ANA01 | ANA | TEX | 3 | 7 | night | 35207 | 210 |
| ANA201604090 | 2016 | 2016-04-09 | 0 | regular | ANA01 | ANA | TEX | 1 | 4 | night | 38106 | 173 |
| ANA201604100 | 2016 | 2016-04-10 | 0 | regular | ANA01 | ANA | TEX | 3 | 1 | day | 35097 | 163 |
- game_idtext · unique id
- ANA201604040
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-04
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- CHN
- home_scorenumber
- 0
- away_scorenumber
- 9
- day_nighttext
- night
- attendancenumber
- 44020
- duration_minutesnumber
- 188
- game_idtext · unique id
- ANA201604050
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-05
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- CHN
- home_scorenumber
- 1
- away_scorenumber
- 6
- day_nighttext
- night
- attendancenumber
- 37042
- duration_minutesnumber
- 154
- game_idtext · unique id
- ANA201604070
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-07
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- TEX
- home_scorenumber
- 4
- away_scorenumber
- 3
- day_nighttext
- night
- attendancenumber
- 39089
- duration_minutesnumber
- 191
- game_idtext · unique id
- ANA201604080
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-08
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- TEX
- home_scorenumber
- 3
- away_scorenumber
- 7
- day_nighttext
- night
- attendancenumber
- 35207
- duration_minutesnumber
- 210
- game_idtext · unique id
- ANA201604090
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-09
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- TEX
- home_scorenumber
- 1
- away_scorenumber
- 4
- day_nighttext
- night
- attendancenumber
- 38106
- duration_minutesnumber
- 173
- game_idtext · unique id
- ANA201604100
- seasonnumber · links to seasons
- 2016
- game_datedate
- 2016-04-10
- game_numbernumber
- 0
- game_typetext
- regular
- venue_idtext · links to venues
- ANA01
- home_team_idtext · links to teams
- ANA
- away_team_idtext · links to teams
- TEX
- home_scorenumber
- 3
- away_scorenumber
- 1
- day_nighttext
- day
- attendancenumber
- 35097
- duration_minutesnumber
- 163
first 6 of 23,168 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.