Query: nba_team_average_points_per_game

with total_points as (
  select
    team_id_home as team_id,
    sum(pts_home) as points
  from
    game
  where
    team_id_home = $1
  group by
    team_id_home
  union all
  select
    team_id_away,
    sum(pts_away)
  from
    game
  where
    team_id_away = $1
  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
    where team_id_home = $1
    union all
    select team_id_away from game
    where team_id_away = $1
  )
  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
  'Average Points Per Game' as label,
  ap.avg_points_per_game as value
from
  avg_points ap
where
  ap.team_id = $1;