Unit 2 Task 4: Highest Paid Positions

Published

April 2, 2025

Background

When you hear the word “relationship” what is the first thing that comes to mind? Probably not baseball. But a relationship is simply a way to describe how two or more objects are connected. There are many relationships in baseball such as those between teams and managers, players and salaries, even stadiums and concession prices.

The graphs on Data Visualizations from Best Tickets show many other relationships that exist in baseball.

Client Request

For this project, the Client wants SQL queries that they can use to retrieve data for use on their website without needing Python. They would also like to see the results in Lets-Plot charts.

Data

Note

This project will use the Lahman Baseball Database. In order to complete this project, you will need to download the database and save it inside the DS250 folder (hopefully you have a folder dedicated to this class) next to the .qmd file you will be using for this assignment. Note: Right click the ‘Download’ link and select “Save Link As” to download the data to your computer.

Download: lahmansbaseballdb
Information: Lahman Data Dictionary
Setup Instructions: See SQL Setup

Readings

Complete these readings before we cover the material in class. This will help you retain the material and make the class period more engaging - not less.

In other words, if an assignment is due on Wednesday, we will cover the material needed to complete the assignment on Tuesday. Therefore, you should complete the readings on Monday (or anytime before class on Tuesday).

There are many flavors of SQL but most flavors have the same base commands. SQL queries are typed in the following pattern;

SELECT -- <columns> and <column calculations>
FROM -- <table name>
  JOIN -- <table name>
  ON -- <columns to join>
WHERE -- <filter condition on rows>
GROUP BY -- <subsets for column calculations>
HAVING -- <filter conditions on groups>
ORDER BY -- <how the output is returned in sequence>
LIMIT -- <number of rows to return>

Reading you may find useful (depending on how you approach the assignment):

Question 1

  • Write an SQL query that provides a summary table showing the average salary for each position (e.g., pitcher, catcher, outfielder). Position information can be found in the fielding table in the POS column.

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.

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

    • If the average salary is above $3 million, categorize it as “High Salary.”
    • If the average salary is between $2 million and $3 million, categorize it as “Medium Salary.”
    • Otherwise, categorize it as “Low Salary.”

  • Order the table by average salary in descending order.

Hint: Beware, it is common for a player to play multiple positions in a single year. For this analysis, each player’s salary should only be counted toward one position in a given year: the position at which they played the most games that year. This will likely require a (sub-query)

Submission:

When you have completed the report, you will need to follow this process to submit your work:

  1. Have this assignment’s template/quarto file open in VS Code and nothing else
  2. Click Preview Button in VS Code in the top right of the screen
    1. This will render the project but also entire course work portfolio into HTML files for review
    2. Confirm everything displas as you would like it to
    3. How you see it will be how it is viewed for grading
    4. If there is an error in any cell of the quarto files, the rendering will stop and you will need to fix the error before rendering again (if you get stuck post your error in Slack)
  3. Once the report is confirmed close the preview and open a VS Code Terminal
    1. click Terminal in the top menu bar and then New Terminal
  4. Type the following in the terminal quarto render
    1. Then drag and drop this assignment into the terminal. This will add the file path to the terminal command
    2. Press Enter
  5. This will render the project into a HTML file in the same location as the .qmd file
  6. To locate the file in VS Code
    1. Right click on the file in the file explorer and select Reveal in File Explorer(Win) or Reveal in Finder(Mac)
  7. Upload HTML file into Canvas

Deliverables:

No template is provided. Create a new quarto file for this assignment. It is part of stretching yourself. But don’t forget to include a 1 paragraph write-up / summary description of your results!

Note

Your report should be written in quarto markdown files and rendered to an HTML File. Upload the HTML file in Canvas. (Do not submit the .qmd file)

Back to top