---
title: "Client Report - Late Flights & Missing Data (JSON)"
subtitle: "Course DS 250"
author: "[STUDENT NAME]"
format:
  html:
    self-contained: true
    page-layout: full
    title-block-banner: true
    toc: true
    toc-depth: 3
    toc-location: body
    number-sections: false
    html-math-method: katex
    code-fold: true
    code-summary: "Show the code"
    code-overflow: wrap
    code-copy: hover
    code-tools:
        source: false
        toggle: true
        caption: See code
execute: 
  warning: false
    
---

```{python}
import pandas as pd 
import numpy as np
import sqlite3
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)
```


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

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

## Elevator pitch
_A SHORT (2-3 SENTENCES) PARAGRAPH THAT `DESCRIBES KEY INSIGHTS` TAKEN FROM METRICS IN THE PROJECT RESULTS THINK TOP OR MOST IMPORTANT RESULTS._ (Note: this is not a summary of the project, but a summary of the results.)

_A Client has requested this analysis and this is your one shot of what you would say to your boss in a 2 min elevator ride before he takes your report and hands it to the client._

## QUESTION|TASK 1

__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.__  

_type your results and analysis here_

```{python}
# Include and execute your code here


```


## QUESTION|TASK 2

__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.  

_type your results and analysis here_

```{python}
# Include and execute your code here


```

```{python}
# Include and execute your code here


```

```{python}
# Include and execute your code here


```


## QUESTION|TASK 3

__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?__

_type your results and analysis here_

```{python}
# Include and execute your code here

```

---

## STRETCH QUESTION|TASK 1

__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.  

_type your results and analysis here_

```{python}
# Include and execute your code here


```


## STRETCH QUESTION|TASK 2

__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. 

_type your results and analysis here_

```{python}
# Include and execute your code here


```

---

