Interactive SQL queries on MLB Statcast data (2021-2024)
Individual at-bat records with outcomes, player IDs, and game context
Pitch-by-pitch data including velocity, location, and type
-- View sample at-bats with outcomes
SELECT game_pk, at_bat_number, inning, inning_topbot, final_event, batter, pitcher
FROM tbl_atbats
WHERE final_event IS NOT NULL
LIMIT 15;
-- At-bat outcome frequencies
SELECT final_event, COUNT(*) as occurrences,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tbl_atbats WHERE final_event IS NOT NULL), 2) as percentage
FROM tbl_atbats
WHERE final_event IS NOT NULL
GROUP BY final_event
ORDER BY occurrences DESC;
-- Pitch types with velocities
SELECT pitch_type, COUNT(*) as pitch_count,
ROUND(AVG(release_speed), 1) as avg_speed
FROM tbl_pitchinfo
WHERE pitch_type IS NOT NULL
GROUP BY pitch_type
ORDER BY pitch_count DESC;
-- Running slash line for batter 605141 vs pitcher 608566
WITH RECURSIVE matchup_progression AS (
-- Base case: first confrontation (earliest chronologically)
SELECT
batter, pitcher, game_pk, at_bat_number,
1 as ab_sequence,
1 as total_abs,
CASE WHEN final_event IN ('single', 'double', 'triple', 'home_run') THEN 1 ELSE 0 END as total_hits,
CASE WHEN final_event IN ('single', 'double', 'triple', 'home_run', 'walk', 'hit_by_pitch') THEN 1 ELSE 0 END as total_times_on_base,
CASE WHEN final_event = 'single' THEN 1
WHEN final_event = 'double' THEN 2
WHEN final_event = 'triple' THEN 3
WHEN final_event = 'home_run' THEN 4
ELSE 0 END as total_bases,
final_event
FROM tbl_atbats
WHERE batter = 605141 AND pitcher = 608566
AND batter IS NOT NULL AND pitcher IS NOT NULL
AND (game_pk, at_bat_number) = (
SELECT game_pk, at_bat_number
FROM tbl_atbats
WHERE batter = 605141 AND pitcher = 608566
ORDER BY game_pk, at_bat_number
LIMIT 1
)
UNION ALL
-- Recursive case: find the next chronological at-bat
SELECT
ab.batter, ab.pitcher, ab.game_pk, ab.at_bat_number,
mp.ab_sequence + 1,
mp.total_abs + 1,
mp.total_hits + CASE WHEN ab.final_event IN ('single', 'double', 'triple', 'home_run') THEN 1 ELSE 0 END,
mp.total_times_on_base + CASE WHEN ab.final_event IN ('single', 'double', 'triple', 'home_run', 'walk', 'hit_by_pitch') THEN 1 ELSE 0 END,
mp.total_bases + CASE WHEN ab.final_event = 'single' THEN 1
WHEN ab.final_event = 'double' THEN 2
WHEN ab.final_event = 'triple' THEN 3
WHEN ab.final_event = 'home_run' THEN 4
ELSE 0 END,
ab.final_event
FROM tbl_atbats ab
JOIN matchup_progression mp ON ab.batter = mp.batter AND ab.pitcher = mp.pitcher
WHERE ab.batter = 605141 AND ab.pitcher = 608566
AND (ab.game_pk > mp.game_pk OR (ab.game_pk = mp.game_pk AND ab.at_bat_number > mp.at_bat_number))
AND (ab.game_pk, ab.at_bat_number) = (
SELECT game_pk, at_bat_number
FROM tbl_atbats ab2
WHERE ab2.batter = 605141 AND ab2.pitcher = 608566
AND (ab2.game_pk > mp.game_pk OR (ab2.game_pk = mp.game_pk AND ab2.at_bat_number > mp.at_bat_number))
ORDER BY ab2.game_pk, ab2.at_bat_number
LIMIT 1
)
AND mp.ab_sequence < 50
)
SELECT
ab_sequence,
game_pk,
final_event,
ROUND(CAST(total_hits AS FLOAT) / total_abs, 3) as running_ba,
ROUND(CAST(total_times_on_base AS FLOAT) / total_abs, 3) as running_obp,
ROUND(CAST(total_bases AS FLOAT) / total_abs, 3) as running_slg,
ROUND(CAST(total_times_on_base AS FLOAT) / total_abs + CAST(total_bases AS FLOAT) / total_abs, 3) as running_ops
FROM matchup_progression
ORDER BY ab_sequence;