Playbook
DuckDB Query Examples
Set menu of DuckDB queries for common NBA analysis workflows.
DuckDB Query Examples
DuckDB provides the fastest analytical query experience for nbadb data. Treat this page like a scouting packet: pick the set that matches your question, then run that possession end-to-end.
Pick a query lane
| If you need… | Jump to |
|---|---|
| Season leaders and player workload reads | Scoring and player load |
| Standings, matchup, or pace prep | Team prep and standings |
| Shot-zone volume and efficiency | Shot profile reads |
| Game-flow or clutch context | Late-game reads |
Coaching note: Keep season_year, season_type, and minimum-games filters explicit so analysts can rerun the same possession and reach the same conclusion.
Setup
Connect to DuckDB
import duckdb
conn = duckdb.connect("nba.duckdb")Scoring and player load
Top scorers by season
Use this when you want a quick leaderboard with a minimum-games floor.
SELECT
p.first_name || ' ' || p.last_name AS player,
t.abbreviation AS team,
g.season_year,
ROUND(AVG(b.pts), 1) AS ppg,
COUNT(*) AS games
FROM fact_box_score_player b
JOIN dim_player p ON b.player_id = p.player_id
JOIN dim_team t ON b.team_id = t.team_id
JOIN dim_game g ON b.game_id = g.game_id
WHERE g.season_type = 'Regular Season'
GROUP BY 1, 2, 3
HAVING COUNT(*) >= 50
ORDER BY ppg DESC
LIMIT 20;Player career averages
Use this when you want a broad regular-season baseline across long careers.
SELECT
p.first_name || ' ' || p.last_name AS player,
COUNT(DISTINCT g.season_year) AS seasons,
COUNT(*) AS games,
ROUND(AVG(b.pts), 1) AS ppg,
ROUND(AVG(b.reb), 1) AS rpg,
ROUND(AVG(b.ast), 1) AS apg,
ROUND(AVG(b.stl), 1) AS spg,
ROUND(AVG(b.blk), 1) AS bpg
FROM fact_box_score_player b
JOIN dim_player p ON b.player_id = p.player_id
JOIN dim_game g ON b.game_id = g.game_id
WHERE g.season_type = 'Regular Season'
GROUP BY 1
HAVING COUNT(*) >= 200
ORDER BY ppg DESC
LIMIT 25;Triple-doubles in a season
Use this when you want a fast all-around production check without building a custom metric first.
SELECT
p.first_name || ' ' || p.last_name AS player,
g.season_year,
COUNT(*) AS triple_doubles
FROM fact_box_score_player b
JOIN dim_player p ON b.player_id = p.player_id
JOIN dim_game g ON b.game_id = g.game_id
WHERE b.pts >= 10 AND b.reb >= 10 AND b.ast >= 10
AND g.season_type = 'Regular Season'
GROUP BY 1, 2
ORDER BY triple_doubles DESC
LIMIT 20;Most efficient scorers (TS%)
Use this when volume alone is not enough and you need efficiency with a minimum scoring threshold.
SELECT
p.first_name || ' ' || p.last_name AS player,
g.season_year,
COUNT(*) AS games,
ROUND(AVG(b.pts), 1) AS ppg,
ROUND(
SUM(b.pts) / NULLIF(2 * (SUM(b.fga) + 0.44 * SUM(b.fta)), 0),
3
) AS ts_pct
FROM fact_box_score_player b
JOIN dim_player p ON b.player_id = p.player_id
JOIN dim_game g ON b.game_id = g.game_id
WHERE g.season_type = 'Regular Season'
GROUP BY 1, 2
HAVING COUNT(*) >= 50 AND AVG(b.pts) >= 20
ORDER BY ts_pct DESC
LIMIT 20;Team prep and standings
Team records and standings
Use this when you need a season snapshot that already carries conference and playoff context.
SELECT
t.city || ' ' || t.nickname AS team,
s.conference,
s.division,
s.wins,
s.losses,
ROUND(s.win_pct, 3) AS win_pct,
s.playoff_rank,
s.current_streak
FROM fact_standings s
JOIN dim_team t ON s.team_id = t.team_id
WHERE s.season_id = '2024-25'
ORDER BY s.conference, s.playoff_rank;Head-to-head matchups
Use this when you want a quick opponent-specific prep packet for one club.
SELECT
home_t.abbreviation AS home_team,
away_t.abbreviation AS away_team,
COUNT(*) AS games,
SUM(CASE WHEN g.home_score > g.away_score THEN 1 ELSE 0 END) AS home_wins,
SUM(CASE WHEN g.away_score > g.home_score THEN 1 ELSE 0 END) AS away_wins,
ROUND(AVG(g.home_score), 1) AS avg_home_pts,
ROUND(AVG(g.away_score), 1) AS avg_away_pts
FROM dim_game g
JOIN dim_team home_t ON g.home_team_id = home_t.team_id
JOIN dim_team away_t ON g.away_team_id = away_t.team_id
WHERE g.season_type = 'Regular Season'
AND g.season_year = 2024
AND home_t.abbreviation = 'BOS'
GROUP BY 1, 2
ORDER BY games DESC;Team pace leaders
Use this when you need tempo plus offensive and defensive context in the same read.
SELECT
t.city || ' ' || t.nickname AS team,
g.season_year,
ROUND(AVG(a.pace), 1) AS pace,
ROUND(AVG(a.off_rating), 1) AS off_rating,
ROUND(AVG(a.def_rating), 1) AS def_rating,
ROUND(AVG(a.net_rating), 1) AS net_rating
FROM fact_box_score_advanced_team a
JOIN dim_team t ON a.team_id = t.team_id
JOIN dim_game g ON a.game_id = g.game_id
WHERE g.season_type = 'Regular Season'
AND g.season_year = 2024
GROUP BY 1, 2
ORDER BY pace DESC;Shot profile reads
Shot chart zone analysis
Use this when you want zone-level volume and efficiency before you move into plotting.
SELECT
p.first_name || ' ' || p.last_name AS player,
sc.shot_zone_basic,
sc.shot_zone_area,
COUNT(*) AS attempts,
SUM(sc.shot_made_flag) AS makes,
ROUND(100.0 * SUM(sc.shot_made_flag) / COUNT(*), 1) AS fg_pct
FROM fact_shot_chart sc
JOIN dim_player p ON sc.player_id = p.player_id
JOIN dim_game g ON sc.game_id = g.game_id
WHERE p.last_name = 'Curry'
AND p.first_name = 'Stephen'
AND g.season_year = 2024
GROUP BY 1, 2, 3
ORDER BY attempts DESC;Late-game reads
Win probability swings
Use this when you need the event-by-event leverage moments from one specific game.
SELECT
g.game_id,
home_t.abbreviation AS home,
away_t.abbreviation AS away,
wp.period,
wp.pctimestring,
wp.description,
ROUND(wp.home_pct * 100, 1) AS home_win_pct,
wp.home_pts,
wp.visitor_pts
FROM fact_win_probability wp
JOIN dim_game g ON wp.game_id = g.game_id
JOIN dim_team home_t ON wp.home_team_id = home_t.team_id
JOIN dim_team away_t ON wp.visitor_team_id = away_t.team_id
WHERE g.game_id = '0022400001'
AND wp.is_score_change = 1
ORDER BY wp.event_num;Clutch performance (Last 5 minutes, score within 5)
Use this when you want a closer-only shooting table you can compare across players.
SELECT
p.first_name || ' ' || p.last_name AS player,
COUNT(*) AS clutch_plays,
SUM(CASE WHEN pbp.shot_result = 'Made' THEN 1 ELSE 0 END) AS makes,
SUM(CASE WHEN pbp.is_field_goal = 1 THEN 1 ELSE 0 END) AS fg_attempts,
SUM(pbp.points_total) AS clutch_points
FROM fact_play_by_play pbp
JOIN dim_player p ON pbp.person_id = p.player_id
JOIN dim_game g ON pbp.game_id = g.game_id
WHERE pbp.period = 4
AND pbp.is_field_goal = 1
AND g.season_year = 2024
AND g.season_type = 'Regular Season'
GROUP BY 1
HAVING COUNT(*) >= 30
ORDER BY clutch_points DESC
LIMIT 20;Where to go next
| If this packet got you moving, next read… | Why |
|---|---|
| Analytics Quickstart | Faster first-query route for new analysts |
| Shot Chart Analysis | Turns the shot-zone query into actual court maps |
| Player Comparison | Builds side-by-side player reads from similar building blocks |
| Schema Reference | Helps you trace table names and columns before writing custom SQL |
Keep moving
Stay in the same possession
Keep the mental model warm with adjacent pages, section hubs, and search-friendly routes into the same topic cluster.
Analytics Quickstart
Land quick wins fast and move from setup to analysis with intent.
Shot Chart Analysis
Lean into basketball-native visual storytelling on one of the best-fit pages.
Visual Asset Prompt Pack
Generate hero art, OG cards, icons, and texture systems without losing the docs identity.