The Rockies ABS Analysis, Rebuilt in SQL

My Rockies ABS analysis was built in Python. Most baseball operations groups spend their day in SQL, so I wanted to know two things: could I express the heart of the analysis as clean queries, and would they agree with the Python? They do. Every query below reproduces a number from the ABS article, and an automated test checks each one against the published results, including all 30 clubs.

The short version of that article: Colorado's edge came mostly from volume (challenging more often), not selection (picking pitches better). Here is how that looks in SQL.

The data model

The database has four tables. The grain of the main one is a called pitch: a ball or strike the umpire called, with no swing.

Table	One row per	Rows
teams	club	30
games	game	2,427
called_pitches	called pitch (game, at-bat, pitch number)	371,081
challenge_propensity	called pitch the model scored	343,292

challenge_propensity is the one modeled input: the probability that an average club challenges a given pitch. I fit that model in Python and load its output as a table. Everything downstream of it is plain SQL arithmetic.

Every query starts from one shared view, so "an opportunity" has a single definition. An opportunity is a called pitch that went against a team that still had a challenge in hand (Savant's definition), with full game context:

CREATE VIEW opportunities AS
WITH base AS (
    SELECT cp.game_pk, cp.at_bat_number, cp.pitch_number, cp.inning,
           cp.opp_team_id, cp.opp_side, cp.wrong_call, cp.margin_in,
           cp.plate_x, cp.plate_z, cp.sz_top, cp.sz_bot, cp.overturned,
           cp.rv_ball_minus_strike AS rv,
           pc.p_chal,
           CASE WHEN cp.challenged = 1 AND cp.challenge_team_id = cp.opp_team_id THEN 1 ELSE 0 END AS by_opp
    FROM called_pitches AS cp
    LEFT JOIN challenge_propensity AS pc
           ON pc.game_pk = cp.game_pk AND pc.at_bat_number = cp.at_bat_number AND pc.pitch_number = cp.pitch_number
    WHERE cp.opportunity = 1
      AND cp.outs IS NOT NULL
)
SELECT base.*,
       CASE WHEN by_opp = 1 AND overturned = 1 THEN 1 ELSE 0 END AS ovt
FROM base;

1. Who wins their challenges?

Colorado's hitters won 42% of their challenges, well below the rest of MLB. A window function nets each club out of its own league total, so the comparison is against the other 29 clubs, not a league average that includes Colorado.

WITH per_side AS (
    SELECT t.abbr,
           o.opp_side        AS side,
           SUM(o.by_opp)     AS challenges,
           SUM(o.ovt)        AS overturned
    FROM opportunities AS o
    JOIN teams AS t ON t.team_id = o.opp_team_id
    GROUP BY t.abbr, o.opp_side
),
scored AS (
    SELECT abbr, side, challenges, overturned,
           100.0 * overturned / challenges AS win_pct,
           -- league totals for this side, minus the club itself
           100.0 * (SUM(overturned) OVER by_side - overturned)
                 / (SUM(challenges) OVER by_side - challenges) AS rest_of_mlb_pct
    FROM per_side
    WINDOW by_side AS (PARTITION BY side)
)
SELECT abbr, side, challenges, overturned,
       ROUND(win_pct, 1)         AS win_pct,
       ROUND(rest_of_mlb_pct, 1) AS rest_of_mlb_pct
FROM scored
WHERE abbr = 'COL'
ORDER BY side;

abbr	side	challenges	overturned	win_pct	rest_of_mlb_pct
COL	bat	178	75	42.1	49.1
COL	fld	208	134	64.4	57.7

Here fld means catchers and pitchers, who challenge called balls. Hitters challenge called strikes.

2. Volume versus selection

This is the core of the analysis. For one club and one side:

• Volume is the value earned by challenging more or less often than an average club would on the same pitches.
• Selection is what is left: how valuable each challenge was compared with an average one.

WITH side_totals AS (
    SELECT opp_team_id AS team_id,
           opp_side    AS side,
           SUM(by_opp) AS n,
           SUM(p_chal) AS expected_n,
           SUM(CASE WHEN ovt = 1 THEN rv END) AS gain,
           SUM(p_chal * wrong_call * rv)      AS expected_gain
    FROM opportunities
    GROUP BY opp_team_id, opp_side
),
decomposed AS (
    SELECT team_id, side, n, expected_n,
           expected_gain * (n / expected_n - 1) AS volume,
           gain - expected_gain * n / expected_n AS selection
    FROM side_totals
),
ranked AS (
    SELECT team_id, side, n, expected_n, volume, selection,
           RANK() OVER (PARTITION BY side ORDER BY volume    DESC) AS volume_rank,
           RANK() OVER (PARTITION BY side ORDER BY selection DESC) AS selection_rank
    FROM decomposed
)
SELECT t.abbr, r.side, r.n AS challenges,
       ROUND(r.expected_n, 0)  AS avg_club_would_make,
       ROUND(r.volume, 1)      AS volume_runs,
       r.volume_rank,
       ROUND(r.selection, 1)   AS selection_runs,
       r.selection_rank
FROM ranked AS r
JOIN teams AS t ON t.team_id = r.team_id
WHERE t.abbr = 'COL'
ORDER BY r.side;

abbr	side	challenges	avg club would make	volume	volume rank	selection	selection rank
COL	bat	178	130	+5.2	1	−2.8	29
COL	fld	208	184	+2.6	7	+3.5	4

Colorado's hitters challenged 178 times where an average club would have challenged about 130. That is the most volume in MLB and the second-worst selection. The 42% win rate is the cost of challenging that much.

3. All 30 clubs

The same split for the whole league is a short query (03_volume_vs_selection_by_club.sql), ranked with RANK() OVER (ORDER BY ...). The top of the table:

abbr	total	volume	selection	total rank
MIN	11.8	12.6	−0.8	1
NYY	8.6	7.8	0.8	2
COL	8.6	7.9	0.7	3

Colorado is third on this measure rather than second. My version leaves out Savant's penalty for lost challenges, so the rankings are not meant to match exactly. The Colorado line is the one in the article: +8.6 total, of which +7.9 is volume and +0.7 is selection.

4. Where in the zone does Colorado win?

Conditional aggregation puts Colorado and the other 29 clubs side by side in one pass, and z says how far Colorado sits from the league rate.

WITH challenges AS (
    SELECT opp_team_id, opp_side AS side, ovt,
           plate_x, plate_z,
           (sz_top + sz_bot) / 2 * 12 AS zone_center_in,
           (sz_top - sz_bot) / 2 * 12 AS zone_half_height_in
    FROM opportunities
    WHERE by_opp = 1
),
edges AS (
    SELECT opp_team_id, side, ovt,
           CASE
               -- how far outside the zone horizontally (half-width 8.5 in) vs vertically decides which edge is closest
               WHEN ABS(plate_x) * 12 - 8.5 >= ABS(plate_z * 12 - zone_center_in) - zone_half_height_in THEN 'side'
               WHEN plate_z * 12 > zone_center_in THEN 'top'
               ELSE 'bottom'
           END AS edge
    FROM challenges
),
rates AS (
    SELECT side, edge,
           SUM(CASE WHEN opp_team_id =  (SELECT team_id FROM teams WHERE abbr = 'COL') THEN 1   ELSE 0 END) AS col_n,
           SUM(CASE WHEN opp_team_id =  (SELECT team_id FROM teams WHERE abbr = 'COL') THEN ovt ELSE 0 END) AS col_ovt,
           SUM(CASE WHEN opp_team_id <> (SELECT team_id FROM teams WHERE abbr = 'COL') THEN 1   ELSE 0 END) AS rest_n,
           SUM(CASE WHEN opp_team_id <> (SELECT team_id FROM teams WHERE abbr = 'COL') THEN ovt ELSE 0 END) AS rest_ovt
    FROM edges
    GROUP BY side, edge
),
with_rate AS (
    SELECT *, 1.0 * rest_ovt / rest_n AS rest_rate FROM rates
)
SELECT side, edge, col_n, col_ovt,
       ROUND(100.0 * col_ovt / col_n, 0)  AS col_pct,
       ROUND(100.0 * rest_rate, 0)        AS rest_of_mlb_pct,
       ROUND((col_ovt - col_n * rest_rate) / SQRT(col_n * rest_rate * (1 - rest_rate)), 1) AS z
FROM with_rate
ORDER BY side, edge;

side	edge	col_n	col_ovt	col_pct	rest_of_mlb_pct	z
bat	bottom	73	25	34	49	−2.5
bat	side	92	42	46	45	0.1
bat	top	13	8	62	62	0.0
fld	bottom	73	52	71	59	2.1
fld	side	101	65	64	59	1.2
fld	top	34	17	50	53	−0.3

The bottom edge stands out in both directions: hitters do badly there and catchers and pitchers do well. With six comparisons on the table, I would treat a z of 2.5 as a lead, not a finding, and the article says the same.

5. What did Colorado leave on the table?

Savant's unrealized-value number counts every unchallenged wrong call. Bucketing them by how far the call missed the line tells a different story.

WITH colorado_misses AS (
    SELECT margin_in, rv
    FROM opportunities
    WHERE opp_team_id = (SELECT team_id FROM teams WHERE abbr = 'COL')
      AND wrong_call = 1
      AND by_opp = 0
)
SELECT CASE WHEN margin_in < 1 THEN 'a. under 1 inch'
            WHEN margin_in < 2 THEN 'b. 1 to 2 inches'
            ELSE                    'c. 2 inches or more' END AS how_far_off,
       COUNT(*)                                              AS unchallenged_calls,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 0)    AS pct_of_total,
       ROUND(SUM(rv), 1)                                     AS runs_at_stake
FROM colorado_misses
GROUP BY 1
ORDER BY 1;

how far off	unchallenged calls	% of total	runs at stake
under 1 inch	318	67%	43.3
1 to 2 inches	113	24%	14.0
2 inches or more	42	9%	4.7

Two thirds of the missed calls were within an inch of the line, a distance no player can judge from the box or from behind the plate.

6. A check on my own bookkeeping

The analysis depends on knowing how many challenges each team had left at every pitch. The Python pipeline replays each game to track that. As a cross-check, I rebuilt it independently with a running window sum: each team starts with two challenges and loses one per lost challenge. Every pitch involves two teams, so the table is unpivoted with UNION ALL first.

WITH team_pitch AS (
    SELECT game_pk, at_bat_number, pitch_number, inning, 'bat' AS role, bat_team_id AS team_id, chal_rem_bat AS python_left,
           CASE WHEN challenged = 1 AND overturned = 0 AND challenge_team_id = bat_team_id THEN 1 ELSE 0 END AS lost
    FROM called_pitches
    UNION ALL
    SELECT game_pk, at_bat_number, pitch_number, inning, 'fld', fld_team_id, chal_rem_fld,
           CASE WHEN challenged = 1 AND overturned = 0 AND challenge_team_id = fld_team_id THEN 1 ELSE 0 END
    FROM called_pitches
),
ledger AS (
    SELECT *,
           2 - COALESCE(SUM(lost) OVER (PARTITION BY game_pk, team_id
                                        ORDER BY at_bat_number, pitch_number
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS sql_left,
           MAX(inning) OVER (PARTITION BY game_pk) AS last_inning
    FROM team_pitch
)
SELECT COUNT(*)                                        AS team_pitches_checked,
       COUNT(DISTINCT game_pk)                         AS games,
       SUM(CASE WHEN sql_left = python_left THEN 1 ELSE 0 END) AS agree,
       SUM(CASE WHEN sql_left <> python_left THEN 1 ELSE 0 END) AS mismatches
FROM ledger
WHERE last_inning <= 9;

team-pitches checked	games	agree	mismatches
669,870	2,218	669,870	0

The check covers games that ended in nine innings or fewer. Extra innings refill challenges under rules I inferred from the feed, and I did not try to model those in a single query.

What I would say about the exercise

• The two implementations agree. The SQL and the Python share only the raw inputs and the model's probabilities. Every number above matches, including the volume and selection split for all 30 clubs. Matching to the first decimal across independent code is the kind of check I want before I trust a result.
• SQL made me write definitions down. The opportunities view is now the one place that says what an opportunity is. In the pandas code, that definition was spread across several steps.
• Limits. The model training, the bootstrap ranges and the run-expectancy table stay in Python; I did not try to force them into SQL. The queries are written for SQLite and use standard features (CTEs, window functions, conditional aggregation), so moving them to Postgres or SQL Server should take only small edits, but I have only run them on SQLite.
• Each query has a test. The queries live next to the analysis, and each one has an automated test that compares it with the published results. If the data or a definition changes, the tests fail before the article is wrong.

If you want the full method behind the numbers, the ABS article covers it, including limitations.