Query: nba_top_teams_by_points_per_game

with total_points as (
  select
    team_id_home as team_id,
    sum(pts_home) as points
  from
    game
  group by
    team_id_home
  union all
  select
    team_id_away,
    sum(pts_away)
  from
    game
  group by
    team_id_away
),
game_counts as (
  select
    team_id,
    COUNT(*) as games_played
  from (
    select team_id_home as team_id from game
    union all
    select team_id_away from game
  )
  group by team_id
),
avg_points as (
  select
    tp.team_id,
    sum(tp.points) / gc.games_played as avg_points_per_game
  from
    total_points tp
  join game_counts gc on tp.team_id = gc.team_id
  group by
    tp.team_id
  order by
    avg_points_per_game desc
)
select
  t.full_name,
  ap.avg_points_per_game
from
  avg_points ap
join team t on ap.team_id = t.id
order by
  avg_points_per_game desc;