Finding Relationships in Baseball with SQL

Overview

A client asked a series of baseball questions that I answered by writing SQL against the Lahman baseball database (SQLite) from Python. The questions covered which BYU–Idaho alumni earned the highest salaries, who had the best batting averages at different at-bat thresholds, and how two franchises compare over time.

import pandas as pd 
import numpy as np
import sqlite3
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Learn morea about Code Cells: https://quarto.org/docs/reference/cells/cells-jupyter.html

# Include and execute your code here
sqlite_file = 'data/lahmansbaseballdb.sqlite'
# this file must be in the same location as your .qmd or .py file
con = sqlite3.connect(sqlite_file)

1. Highest-paid BYU–Idaho alumni

Write an SQL query to create a new dataframe about baseball players who attended BYU-Idaho. The new table should contain five columns: playerID, schoolID, salary, and the yearID/teamID associated with each salary. Order the table by salary (highest to lowest) and print out the table in your report.

The highest salary player from byui has the player ID of lindsma01.

# Include and execute your code here


q = """
    SELECT DISTINCT cp.playerID,
            cp.schoolID,
            sal.salary,
            sal.yearID,
            sal.teamID
        FROM collegeplaying AS cp
        LEFT OUTER JOIN salaries AS sal ON cp.playerID = sal.playerID
        WHERE cp.schoolID = 'idbyuid'
        ORDER BY sal.salary DESC
    """
results = pd.read_sql_query(q,con)
results

	playerID	schoolID	salary	yearID	teamID
0	lindsma01	idbyuid	4000000.0	2014.0	CHA
1	lindsma01	idbyuid	3600000.0	2012.0	BAL
2	lindsma01	idbyuid	2800000.0	2011.0	COL
3	lindsma01	idbyuid	2300000.0	2013.0	CHA
4	lindsma01	idbyuid	1625000.0	2010.0	HOU
5	stephga01	idbyuid	1025000.0	2001.0	SLN
6	stephga01	idbyuid	900000.0	2002.0	SLN
7	stephga01	idbyuid	800000.0	2003.0	SLN
8	stephga01	idbyuid	550000.0	2000.0	SLN
9	lindsma01	idbyuid	410000.0	2009.0	FLO
10	lindsma01	idbyuid	395000.0	2008.0	FLO
11	lindsma01	idbyuid	380000.0	2007.0	FLO
12	stephga01	idbyuid	215000.0	1999.0	SLN
13	stephga01	idbyuid	185000.0	1998.0	PHI
14	stephga01	idbyuid	150000.0	1997.0	PHI
15	catetr01	idbyuid	NaN	NaN	NaN

2. Batting averages at different at-bat thresholds

This three-part question requires you to calculate batting average (number of hits divided by the number of at-bats)
a. Write an SQL query that provides playerID, yearID, and batting average for players with at least 1 at bat that year. Sort the table from highest batting average to lowest, and then by playerid alphabetically. Show the top 5 results in your report.
a. Use the same query as above, but only include players with at least 10 at bats that year. Print the top 5 results.
a. Now calculate the batting average for players over their entire careers (all years combined). Only include players with at least 100 at bats, and print the top 5 results.

1. alberal01 has the highest batting average in the year 1957
2. nymanny01 had the highest batting average of 0.643
3. Meyerle01 had the highest overall batting average at 0.492

# Include and execute your code here
q = """
    SELECT playerID,
            yearID,
            round((H*1.0 / AB), 3) AS 'batting_avg'
        FROM batting
        WHERE AB > 0
        ORDER BY batting_avg DESC, playerID
        LIMIT 5
    """
results = pd.read_sql_query(q,con)
results

	playerID	yearID	batting_avg
0	aberal01	1957	1.0
1	abernte02	1960	1.0
2	abramge01	1923	1.0
3	acklefr01	1964	1.0
4	alanirj01	2019	1.0

# Include and execute your code here
q = """
    SELECT playerID,
            yearID,
            round((H*1.0 / AB), 3) AS 'batting_avg'
        FROM batting
        WHERE AB > 10
        ORDER BY batting_avg DESC, playerID
        LIMIT 5
    """
results = pd.read_sql_query(q,con)
results

	playerID	yearID	batting_avg
0	nymanny01	1974	0.643
1	carsoma01	2013	0.636
2	silvech01	1948	0.571
3	puccige01	1930	0.563
4	applepe01	1927	0.545

# Include and execute your code here

q = """
    SELECT DISTINCT playerID,
            yearID,
            round((H*1.0 / AB), 3) AS 'batting_avg'
        FROM batting
        WHERE AB > 100
        ORDER BY batting_avg DESC, playerID
        LIMIT 5
    """
results = pd.read_sql_query(q,con)
results

	playerID	yearID	batting_avg
0	meyerle01	1871	0.492
1	duffyhu01	1894	0.440
2	oneilti01	1887	0.435
3	barnero01	1873	0.431
4	mcveyca01	1871	0.431

3. Yankees vs. Dodgers

Pick any two baseball teams and compare them using a metric of your choice (average salary, home runs, number of wins, etc). Write an SQL query to get the data you need, then make a graph using Lets-Plot to visualize the comparison. What do you learn?

I can see that both the New York Yankees and the Los Angeles Dodgers have been pretty close during the length of their careers but from about 2000 onward, the Dodgers have had a very steady incline while the Yankees have still had some stagnation and even a little decrease.

# Include and execute your code here

q = """
    SELECT yearID,
            franchID,
            W
        FROM teams
        WHERE franchID = 'LAD' OR franchID = 'NYY'
        ORDER BY w DESC
        """
results = pd.read_sql_query(q,con)
results

(
    ggplot(data = results, mapping = aes(x = 'yearID', y = 'W', color = 'franchID')) +
    geom_line() +
    scale_x_continuous(format = ".0f")
)

---

4. Salary distribution by position (CASE statements)

Advanced Salary Distribution by Position (with Case Statement):

* Write an SQL query that provides a summary table showing the average salary for players in each position (e.g., pitcher, catcher, outfielder) across all years. Include the following columns:

    * position
    * average_salary
    * total_players
    * highest_salary  

* The highest_salary column should display the highest salary ever earned by a player in that position. If no player in that position has a recorded salary, display “N/A” for the highest salary.  

* Additionally, create a new column called salary_category using a case statement:  

    * If the average salary is above $1 million, categorize it as “High Salary.”  
    * If the average salary is between $500,000 and $1 million, categorize it as “Medium Salary.”  
    * Otherwise, categorize it as “Low Salary.”  

* Order the table by average salary in descending order.
* Print the top 10 rows of this summary table.  

Using the subquery and the case function we are able to make a new table to call upon in our query which allows us to do further calculations that are not in the data. It seems that first baseman make the most money of anyone on the team.

# Include and execute your code here
q = """
    SELECT t.POS AS position,
        ROUND(AVG(s.salary), 2) AS average_salary,
        COUNT(DISTINCT s.playerID) AS total_players,
        MAX(s.salary) AS highest_salary,
        CASE   
            WHEN AVG(s.salary) > 1000000 THEN "High Salary"
            WHEN AVG(s.salary) > 500000 THEN "Medium Salary"
            ELSE "Low Salary"
        END AS salary_category
    FROM (
        WITH Ranked AS (
            SELECT yearID, playerID, POS, G,
                ROW_NUMBER() OVER (PARTITION BY yearID, playerID ORDER BY G DESC, POS) AS rn
            FROM fielding
        )
        SELECT yearID, playerID, POS, G
        FROM Ranked
        WHERE rn = 1
    ) t
    FULL OUTER JOIN salaries AS s
        ON t.playerID = s.playerID
        AND t.yearID = s.yearID
    GROUP BY t.POS
    ORDER BY AVG(s.salary) DESC
    LIMIT 10
    """
results = pd.read_sql_query(q,con)
results

	position	average_salary	total_players	highest_salary	salary_category
0	1B	3334894.21	472	28000000.0	High Salary
1	OF	2404355.25	1121	27328046.0	High Salary
2	3B	2320823.94	492	33000000.0	High Salary
3	SS	2008501.44	369	22600000.0	High Salary
4	P	1938130.80	2556	33000000.0	High Salary
5	2B	1798912.03	484	24000000.0	High Salary
6	NaN	1600665.84	780	24000000.0	High Salary
7	C	1430814.47	404	23000000.0	High Salary

5. Career longevity (subqueries)

Advanced Career Longevity and Performance (with Subqueries):

* Calculate the average career length (in years) for players who have played at least one game. Then, identify the top 10 players with the longest careers (based on the number of years they played). Include their:  

    * playerID
    * first_name
    * last_name
    * career_length

* The career_length should be calculated as the difference between the maximum and minimum yearID for each player. 

The average career length of players who have played at least 1 year of baseball is 9 years. The longest career of the league is 35 years held by Nick Altrock.

# Include and execute your code here
q = """
    SELECT 
        AVG(julianday(p.finalgame_date) - julianday(p.debut_date)) / 365 AS avg_career_length
    FROM people AS p
    JOIN batting as b
        ON p.playerID = b.playerID
    """
results = pd.read_sql_query(q,con)
results

	avg_career_length
0	9.209668

q = """
    SELECT 
        p.playerID,
        p.nameFirst AS first_name,
        p.nameLast AS last_name,
        (julianday(p.finalgame_date) - julianday(p.debut_date)) / 365 AS career_length
    FROM people AS p
    JOIN batting AS b 
        ON p.playerID = b.playerID
    GROUP BY p.playerID
    ORDER BY career_length DESC
    LIMIT 10
    """
results = pd.read_sql_query(q,con)
results

	playerID	first_name	last_name	career_length
0	altroni01	Nick	Altrock	35.238356
1	orourji01	Jim	O'Rourke	32.427397
2	minosmi01	Minnie	Minoso	31.484932
3	olearch01	Charley	O'Leary	30.482192
4	lathaar01	Arlie	Latham	29.254795
5	mcguide01	Deacon	McGuire	27.923288
6	jennihu01	Hughie	Jennings	27.271233
7	eversjo01	Johnny	Evers	27.115068
8	ryanno01	Nolan	Ryan	27.049315
9	streega01	Gabby	Street	27.035616

---