turbot/nba

Query: nba_top_teams_by_points_per_game

Usage

powerpipe query nba.query.nba_top_teams_by_points_per_game

SQL

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;

Dashboards

The query is used in the dashboards: