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.

If you want to rehearse the SQL structure before you connect to a local nba.duckdb file, start with SQL Playground. That page runs DuckDB-WASM in the browser with self-contained NBA-flavored sample rows, then this guide shows the same style of reads against the real warehouse tables.

Setup

Connect to DuckDB

import duckdb

conn = duckdb.connect("nbadb/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_player_game_traditional 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_player_game_traditional 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_player_game_traditional 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_player_game_traditional 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.conference_rank,
    s.streak
FROM fact_standings s
JOIN dim_team t ON s.team_id = t.team_id
WHERE s.season_year = '2024-25'
ORDER BY s.conference, s.conference_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 r.pts_home > r.pts_away THEN 1 ELSE 0 END) AS home_wins,
    SUM(CASE WHEN r.pts_away > r.pts_home THEN 1 ELSE 0 END) AS away_wins,
    ROUND(AVG(r.pts_home), 1) AS avg_home_pts,
    ROUND(AVG(r.pts_away), 1) AS avg_away_pts
FROM fact_game_result r
JOIN dim_game g ON r.game_id = g.game_id
JOIN dim_team home_t ON g.home_team_id = home_t.team_id
JOIN dim_team away_t ON g.visitor_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
    wp.game_id,
    wp.period,
    wp.pc_time_string,
    ROUND(wp.home_pct * 100, 1) AS home_win_pct,
    wp.home_pts,
    wp.visitor_pts,
    wp.home_score_margin
FROM fact_win_probability wp
WHERE wp.game_id = '0022400001'
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_shots,
    SUM(CASE WHEN pbp.event_type_name = 'made_shot' THEN 1 ELSE 0 END) AS makes,
    ROUND(100.0 * SUM(CASE WHEN pbp.event_type_name = 'made_shot' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0), 1) AS fg_pct
FROM fact_play_by_play pbp
JOIN dim_player p ON pbp.player1_id = p.player_id
JOIN dim_game g ON pbp.game_id = g.game_id
WHERE pbp.period = 4
  AND pbp.event_type_name IN ('made_shot', 'missed_shot')
  AND g.season_year = 2024
  AND g.season_type = 'Regular Season'
GROUP BY 1
HAVING COUNT(*) >= 30
ORDER BY makes DESC
LIMIT 20;

Where to go next

If this packet got you moving, next read…	Why
SQL Playground	Browser-only warm-up for DuckDB syntax, CTEs, and NBA-flavored sample queries
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

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("nbadb/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_player_game_traditional 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_player_game_traditional 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_player_game_traditional 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_player_game_traditional 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.conference_rank,
    s.streak
FROM fact_standings s
JOIN dim_team t ON s.team_id = t.team_id
WHERE s.season_year = '2024-25'
ORDER BY s.conference, s.conference_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 r.pts_home > r.pts_away THEN 1 ELSE 0 END) AS home_wins,
    SUM(CASE WHEN r.pts_away > r.pts_home THEN 1 ELSE 0 END) AS away_wins,
    ROUND(AVG(r.pts_home), 1) AS avg_home_pts,
    ROUND(AVG(r.pts_away), 1) AS avg_away_pts
FROM fact_game_result r
JOIN dim_game g ON r.game_id = g.game_id
JOIN dim_team home_t ON g.home_team_id = home_t.team_id
JOIN dim_team away_t ON g.visitor_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
    wp.game_id,
    wp.period,
    wp.pc_time_string,
    ROUND(wp.home_pct * 100, 1) AS home_win_pct,
    wp.home_pts,
    wp.visitor_pts,
    wp.home_score_margin
FROM fact_win_probability wp
WHERE wp.game_id = '0022400001'
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_shots,
    SUM(CASE WHEN pbp.event_type_name = 'made_shot' THEN 1 ELSE 0 END) AS makes,
    ROUND(100.0 * SUM(CASE WHEN pbp.event_type_name = 'made_shot' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0), 1) AS fg_pct
FROM fact_play_by_play pbp
JOIN dim_player p ON pbp.player1_id = p.player_id
JOIN dim_game g ON pbp.game_id = g.game_id
WHERE pbp.period = 4
  AND pbp.event_type_name IN ('made_shot', 'missed_shot')
  AND g.season_year = 2024
  AND g.season_type = 'Regular Season'
GROUP BY 1
HAVING COUNT(*) >= 30
ORDER BY makes DESC
LIMIT 20;

Where to go next

If this packet got you moving, next read…	Why
SQL Playground	Browser-only warm-up for DuckDB syntax, CTEs, and NBA-flavored sample queries
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

DuckDB Query Examples

Stay in the same possession

Analytics Quickstart

SQL Playground

On this page

DuckDB Query Examples

Stay in the same possession

Analytics Quickstart

SQL Playground

On this page