# Example: Lahman Data

The [Lahman data set](http://www.seanlahman.com/baseball-archive/statistics/) is
compiled yearly into a SQLite-variant.

<https://github.com/jknecht/baseball-archive-sqlite>

For simplicity, I've opted to store a copy of the data inside my own raw data
collection repositories.

## Download Lahman

Let's download the Lahman data set using shell with `curl`

```{bash}
#| cache: true
#| echo: true
curl -o lahman2019.sqlite -LJ https://coatless.github.io/raw-data/lahman2019.sqlite
```

We'll assume that we just need the `lahman2019.sqlite` file.

## Initialize Database Connection

Next, we'll setup a database connection into the Lahman database from _R_.

```{r}
#| label: link-to-sqlite
library(DBI)

# Setup a connection into a file-backed SQLite database
lahman_db = DBI::dbConnect(RSQLite::SQLite(), "lahman2019.sqlite")
```

## Observing ER

The ER Diagram for the Lahman 2019 database should look similar to: 

![](er-diagram-lahman.png)

As we can see, there are quite a few tables present.

## View All Tables

How can we see the names of all tables in the Lahman database if we did not have an ER Diagram?

```{sql}
#| label: table-overview-lahman
#| connection: lahman_db
SELECT name FROM sqlite_master WHERE type='table'
```

## View Table Schema 

Let's peak into the `batting` table schema:

```{sql}
#| label: table-schema-lahman
#| connection: lahman_db
PRAGMA table_info(batting)
```


# Single-table SQL Queries

For these example queries, we focus on only looking at a single table within
Lahman to obtain our results.

## SELECT: Fields with MAX

To begin, let's obtain the information about a player with the maximum home runs (HRs) hit
by using the `Batting` table.

```{sql}
#| label: max-hrs-hit
#| connection: lahman_db
SELECT playerID, yearID, teamID, MAX(HR) as MAX_HR FROM Batting
```

## SELECT: Derived Variables with Average

We could then build queries such as the average number of home runs and hits.

```{sql}
#| label: average-home-run-hits
#| connection: lahman_db
SELECT 
AVG(HR) as avg_hr,
AVG(H) as avg_h
FROM batting
```

## SELECT-WHERE: Subset data to identify single player 

We could specify a single player and look at the arc of their career. 

```{sql}
#| label: id-player
#| connection: lahman_db
SELECT playerID, HR, yearID
FROM Batting 
WHERE playerID = "barkele01"
```

## SELECT-WHERE: Subset data with fuzzy logic.

We could even identify players born in the USA within the 1990's decade.
The "fuzzy" part of the 1990's is possible using the `LIKE` keyword alongside of
a year, e.g. `199%`. 

```{sql}
#| label: identify-players-born-in-us-in-1990
#| connection: lahman_db
SELECT nameGiven as Given, nameLast AS Last, birthYear AS Born, birthCountry as Place
FROM people
WHERE birthCountry == "USA" AND birthYear LIKE "199%"
LIMIT 5
```

## SELECT-WHERE: Fuzzy Logic with Names

Let's say another application would be to identify the possible awards given yearly,
e.g. finding awards with the phrase of `% of the Year`. 

This would give us: 

```{sql}
#| label: identify-yearly-awards
#| connection: lahman_db

SELECT DISTINCT(aps.awardID) awards
FROM awardsplayers aps
WHERE aps.awardID LIKE "% of the Year"
```


## GROUP-BY and SELECT: Average data by team with

We could apply techniques to view the same information by team and year.

```{sql}
#| label: year-information-by-team
#| connection: lahman_db
SELECT teamID, yearID, AVG(HR) as avg_hr, AVG(H) as avg_h
FROM batting
WHERE yearID == 2018 OR yearID == 2019
GROUP BY teamID, yearID
ORDER BY AVG_HR DESC
LIMIT 15
```


## GROUP-BY with HAVING: Subset data by group

Or, obtain a list of where the players played in College.

```{sql}
#| label: identify-where-players-played
#| connection: lahman_db
SELECT *
FROM collegeplaying
GROUP BY playerID
HAVING yearID == max(YearID)
ORDER BY yearID DESC
LIMIT 5
```


# Two-or-more Tables SQL Queries

Within this section, we switch away from using only a single table in favor
of joining _multiple_ tables together. 

## Warning: Be wary of joining on non-unique data!

By performing a `JOIN`, which performs an `INNER JOIN` by default, we could 
see a player's performance alongside of their salary. 
However, joining on a single key yields odd results...

```{sql}
#| label: problematic-join-not-unique
#| connection: lahman_db
SELECT playerID, HR, salary
FROM Batting JOIN Salaries USING(playerID)
LIMIT 10
```

Repeating this `JOIN` statement using two keys performs much better:

```{sql}
#| label: improved-join-unique
#| connection: lahman_db
SELECT yearID, playerID, HR, salary
FROM Batting JOIN Salaries USING(yearID, playerID)
LIMIT 10
```

### (Aside) Alternate: Inner Join

We could be more explicit using `INNER JOIN ... ON ...`

```{sql}
#| label: explicit-join-unique
#| connection: lahman_db

SELECT Salaries.yearID, Salaries.playerID, HR, salary
FROM Batting 
INNER JOIN Salaries ON 
Salaries.playerID = Batting.playerID AND
Salaries.yearID = Batting.yearID
LIMIT 10
```

Notice that here was are specifically stating what table we are retrieving a field from.

## INNER JOIN: Intersection of Tables

Let's list the players by the number of "Runs Batted In" (RBI) over the last 10 years.

```{sql}
#| label: runs-batted-in-across-years
#| connection: lahman_db

SELECT p.nameFirst AS First, p.nameLast AS Last, sum(RBI) AS RBI_TOTAL
FROM batting b
INNER JOIN people p ON b.playerID = p.playerID 
WHERE yearID >= 2010
GROUP BY b.playerID
HAVING RBI_TOTAL >= 500
ORDER BY RBI_TOTAL DESC
```

## LEFT JOIN: Merging on condition

For the next query, let's try to identify the total number of allstar appearances
by players who are baseball's hall of fame.

```{sql}
#| label: identifying-allstars-in-hof
#| connection: lahman_db

SELECT nameFirst, nameLast, hof.yearid AS induction_year, count(asf.yearID) AS allstar_appearances
FROM halloffame AS hof
JOIN people USING (playerID)
LEFT JOIN allstarfull AS asf USING (playerID)
WHERE inducted='Y' AND category='Player'
GROUP BY playerID, nameFirst, nameLast
ORDER BY allstar_appearances DESC
```

# Subquerying Joins

We can merge two queries together into a single query through the use of a join.

## Counting the Number of Players with More than 99 Hits

Consider an example of needing to count players that have more than 
99 hits in a single year. For the inner query, we aim to identify players 
that have more than 99 hits by year:

```{sql}
#| label: identify-players
#| connection: lahman_db

 SELECT playerID, yearID, H
 FROM Batting 
 WHERE yearID > 2009 and H > 99
 GROUP BY playerID, yearID
```

Then, we aim to obtain a count of the players in the outer query:

```{sql}
#| label: count-players
#| connection: lahman_db

-- Outer query
SELECT yearID as year, COUNT(playerID) as N
FROM 
(
 -- Inner query
 
 SELECT playerID, yearID, H
 FROM Batting 
 WHERE yearID > 2009 and H > 99
 GROUP BY playerID, yearID

) 
GROUP BY yearID
ORDER BY yearID
```

## What school did the person who won the hank aaron award attend?

Let's consider another example where a subquery might be helpful. Across the
years the Hank Aaron award is given out, what school did each winner attend 
before making it big in the MLB? 

In this question, there are two subquestions: 

1. What was the school that each player attended?
2. Who won the award? 

Combining the answer to both questions will ultimately lead us to the
answer that we desire.

Here, we first identify the players schools and, then, opt to subset the
players by who won the award. 

```{sql}
#| label: final-college-attend
#| connection: lahman_db

SELECT aps.playerID playerID, aps.yearID as year, lgID as league, schoolID
FROM awardsplayers aps
LEFT JOIN 
  (SELECT *  -- Final College Attended
    FROM CollegePlaying
    GROUP BY playerID
    HAVING yearID == max(YearID)
  ) c
ON c.playerID = aps.playerID
WHERE aps.awardID = "Hank Aaron Award"
```

## Counting Award Winners by School

We could obtain further insights by obtaining a count of the award by 
school. To avoid a long list of schools with just a _single_ winner, we could
subset the final results using `HAVING` to require each school having
at least 2 or more winners.

```{sql}
#| label: aaron-award-winner-by-school
#| connection: lahman_db

SELECT schoolID, count(c.playerID) as Hank_Aaron_Awards
FROM awardsplayers ap
LEFT JOIN 
  (SELECT *  --Last College Attended
    FROM CollegePlaying
    GROUP BY playerID
    HAVING yearID == max(yearID)
  ) c
ON c.playerID = ap.playerID
WHERE awardID = "Hank Aaron Award" AND schoolID IS NOT NULL
GROUP BY schoolID
HAVING Hank_Aaron_Awards > 1  --- Require two or more winners
ORDER BY Hank_Aaron_Awards DESC
```





# Your Turn: SQL Queries

## Top 5 Salaries by Top 5 Average HRs
Retrieve the salaries of the top 5 Average Home Run (HR) hitting players across the years.

Feel free to modify the query below:

```{sql}
#| label: base-query-ex1
#| connection: lahman_db
#| eval: false

SELECT 
yearID, playerID, HR, Salary 
FROM Batting
JOIN Salaries USING(yearID, playerID)
```


## Top Players born in the State of IL playing in 2019

Determine all the players born in the State of Illinois who played in 2019

*Hint*: The `batting` and `people` tables will be useful.

```{sql}
#| label: top-players-in-il
#| connection: lahman_db
#| eval: false

--- Fill me in!
```

## How many awards are available to be given?

Find all distinct awards in the `awardplayers` table

```{sql}
#| label: number-of-awards
#| connection: lahman_db
#| eval: false

--- Fill me in!
```

## Rookie of the Year

Finding all "Rookie of the Year Awards" in the `awardplayers` Table.

```{sql}
#| label: identify-rookie-of-the-year-awards
#| connection: lahman_db
#| eval: false


--- Fill me in!
```
