Joins with Lahman

Example: Lahman Data

The Lahman data set 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

curl -o lahman2019.sqlite -LJ https://coatless.github.io/raw-data/lahman2019.sqlite

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed

  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
 14 62.9M   14 9216k    0     0  22.4M      0  0:00:02 --:--:--  0:00:02 22.7M
 67 62.9M   67 42.3M    0     0  30.2M      0  0:00:02  0:00:01  0:00:01 30.3M
100 62.9M  100 62.9M    0     0  31.5M      0  0:00:01  0:00:01 --:--:-- 31.6M

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.

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:

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?

SELECT name FROM sqlite_master WHERE type='table'

29 records

name
allstarfull
appearances
awardsmanagers
awardsplayers
awardssharemanagers
awardsshareplayers
batting
battingpost
collegeplaying
divisions
fielding
fieldingof
fieldingofsplit
fieldingpost
halloffame
homegames
leagues
managers
managershalf
parks
people
pitching
pitchingpost
salaries
schools
seriespost
teams
teamsfranchises
teamshalf

View Table Schema

Let’s peak into the batting table schema:

PRAGMA table_info(batting)

25 records

cid	name	type	notnull	dflt_value	pk
0	ID	INTEGER	1	NA	1
1	playerID	VARCHAR(9)	1	NA	0
2	yearID	SMALLINT	1	NA	0
3	stint	SMALLINT	1	NA	0
4	teamID	CHARACTER(3)	0	NA	0
5	team_ID	INTEGER	0	NA	0
6	lgID	CHARACTER(2)	0	NA	0
7	G	SMALLINT	0	NA	0
8	G_batting	SMALLINT	0	NA	0
9	AB	SMALLINT	0	NA	0
10	R	SMALLINT	0	NA	0
11	H	SMALLINT	0	NA	0
12	2B	SMALLINT	0	NA	0
13	3B	SMALLINT	0	NA	0
14	HR	SMALLINT	0	NA	0
15	RBI	SMALLINT	0	NA	0
16	SB	SMALLINT	0	NA	0
17	CS	SMALLINT	0	NA	0
18	BB	SMALLINT	0	NA	0
19	SO	SMALLINT	0	NA	0
20	IBB	SMALLINT	0	NA	0
21	HBP	SMALLINT	0	NA	0
22	SH	SMALLINT	0	NA	0
23	SF	SMALLINT	0	NA	0
24	GIDP	SMALLINT	0	NA	0

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.

SELECT playerID, yearID, teamID, MAX(HR) as MAX_HR FROM Batting

1 records

playerID	yearID	teamID	MAX_HR
bondsba01	2001	SFN	73

SELECT: Derived Variables with Average

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

SELECT 
AVG(HR) as avg_hr,
AVG(H) as avg_h
FROM batting

1 records

avg_hr	avg_h
2.864785	36.71486

SELECT-WHERE: Subset data to identify single player

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

SELECT playerID, HR, yearID
FROM Batting 
WHERE playerID = "barkele01"

12 records

playerID	HR	yearID
barkele01	0	1976
barkele01	0	1977
barkele01	0	1978
barkele01	0	1979
barkele01	0	1980
barkele01	0	1981
barkele01	0	1982
barkele01	0	1983
barkele01	0	1983
barkele01	0	1984
barkele01	0	1985
barkele01	0	1987

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

SELECT nameGiven as Given, nameLast AS Last, birthYear AS Born, birthCountry as Place
FROM people
WHERE birthCountry == "USA" AND birthYear LIKE "199%"
LIMIT 5

5 records

Given	Last	Born	Place
Jason Kendall	Adam	1991	USA
Austin Lance	Adams	1991	USA
Chance	Adams	1994	USA
Nicholas Mark	Ahmed	1990	USA
Ruben	Alaniz	1991	USA

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:

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

7 records

awards
TSN Player of the Year
TSN Fireman of the Year
Rookie of the Year
TSN Major League Player of the Year
TSN Pitcher of the Year
TSN Reliever of the Year
Comeback Player 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.

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

15 records

teamID	yearID	avg_hr	avg_h
HOU	2019	6.400000	34.17778
MIN	2019	6.140000	30.94000
LAN	2019	6.065217	30.73913
NYA	2019	5.666667	27.64815
NYA	2018	5.340000	27.48000
BOS	2019	5.212766	33.06383
OAK	2019	5.140000	27.68000
COL	2018	5.121951	34.58537
HOU	2018	5.000000	33.90244
MIL	2019	5.000000	27.32000
ATL	2019	4.980000	28.64000
CHN	2019	4.923077	26.50000
ARI	2019	4.888889	31.53333
SLN	2019	4.883721	31.06977
CIN	2019	4.829787	28.25532

GROUP-BY with HAVING: Subset data by group

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

SELECT *
FROM collegeplaying
GROUP BY playerID
HAVING yearID == max(YearID)
ORDER BY yearID DESC
LIMIT 5

5 records

ID	playerID	schoolID	yearID
4922	finnebr01	txchrist	2014
3411	crockky01	virginia	2013
4743	farmebu01	gatech	2013
5808	gonzama02	gonzaga	2013
5939	graveke01	missst	2013

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…

SELECT playerID, HR, salary
FROM Batting JOIN Salaries USING(playerID)
LIMIT 10

10 records

playerID	HR	salary
lawrebr01	0	500000
lawrebr01	0	500000
lawrebr01	0	500000
lawrebr01	0	500000
lawrebr01	0	500000
lawrebr01	0	500000
lawrebr01	0	500000
johnto01	0	600000
johnto01	0	440000
johnto01	0	60000

Repeating this JOIN statement using two keys performs much better:

SELECT yearID, playerID, HR, salary
FROM Batting JOIN Salaries USING(yearID, playerID)
LIMIT 10

10 records

yearID	playerID	HR	salary
1985	barkele01	0	870000
1985	bedrost01	0	550000
1985	benedbr01	0	545000
1985	campri01	1	633333
1985	ceronri01	3	625000
1985	chambch01	3	800000
1985	dedmoje01	0	150000
1985	forstte01	0	483333
1985	garbege01	0	772000
1985	harpete01	17	250000

(Aside) Alternate: Inner Join

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

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

10 records

yearID	playerID	HR	salary
1985	barkele01	0	870000
1985	bedrost01	0	550000
1985	benedbr01	0	545000
1985	campri01	1	633333
1985	ceronri01	3	625000
1985	chambch01	3	800000
1985	dedmoje01	0	150000
1985	forstte01	0	483333
1985	garbege01	0	772000
1985	harpete01	17	250000

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.

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

96 records

First	Last	RBI_TOTAL
Albert	Pujols	963
Nelson	Cruz	961
Edwin	Encarnacion	956
Miguel	Cabrera	941
Robinson	Cano	878
Jay	Bruce	824
Evan	Longoria	817
Ryan	Braun	811
Paul	Goldschmidt	807
Adam	Jones	806
Freddie	Freeman	805
Adrian	Beltre	801
Justin	Upton	798
Giancarlo	Stanton	785
Adrian	Gonzalez	778
Matt	Kemp	768
Carlos	Santana	766
Andrew	McCutchen	765
Jose	Bautista	764
Joey	Votto	759
Mike	Trout	752
Nolan	Arenado	734
Eric	Hosmer	734
Carlos	Gonzalez	730
Anthony	Rizzo	729
Hunter	Pence	712
J. D.	Martinez	711
David	Ortiz	700
Ian	Desmond	699
Buster	Posey	673
Nick	Markakis	669
Kyle	Seager	666
Chris	Davis	665
Asdrubal	Cabrera	659
Yadier	Molina	653
Ryan	Zimmerman	651
Josh	Donaldson	645
Daniel	Murphy	639
Curtis	Granderson	638
Starlin	Castro	636
Ian	Kinsler	636
Bryce	Harper	635
Brian	McCann	629
Mark	Trumbo	629
Matt	Holliday	628
Melky	Cabrera	626
Todd	Frazier	624
Ben	Zobrist	620
Victor	Martinez	619
Jose	Abreu	611
Mark	Reynolds	610
Michael	Brantley	607
Neil	Walker	606
Hanley	Ramirez	604
Manny	Machado	598
Alex	Gordon	597
Elvis	Andrus	589
Shin-Soo	Choo	587
Kendrys	Morales	587
Jason	Heyward	579
Prince	Fielder	575
Khris	Davis	570
Brandon	Phillips	568
Mike	Napoli	563
Brian	Dozier	561
Mike	Moustakas	561
Mitch	Moreland	559
Justin	Smoak	555
Ryan	Howard	554
Carlos	Beltran	552
Anthony	Rendon	546
Jonathan	Lucroy	545
Howie	Kendrick	543
Jose	Altuve	538
Marcell	Ozuna	538
Troy	Tulowitzki	537
Brandon	Crawford	536
Matt	Carpenter	531
Jason	Kipnis	529
Billy	Butler	528
David	Freese	528
Josh	Reddick	527
Joe	Mauer	526
Yoenis	Cespedes	524
Martin	Prado	516
Adam	Lind	515
Dustin	Pedroia	513
Kurt	Suzuki	513
Torii	Hunter	512
Charlie	Blackmon	511
Pablo	Sandoval	508
Xander	Bogaerts	503
Salvador	Perez	503
Matt	Wieters	503
Mark	Teixeira	500
Christian	Yelich	500

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.

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

256 records

nameFirst	nameLast	induction_year	allstar_appearances
Hank	Aaron	1982	24
Willie	Mays	1979	24
Stan	Musial	1969	24
Mickey	Mantle	1974	19
Cal	Ripken	2007	19
Ted	Williams	1966	19
Yogi	Berra	1972	18
Rod	Carew	1991	18
Al	Kaline	1980	18
Brooks	Robinson	1983	18
Carl	Yastrzemski	1989	18
Warren	Spahn	1973	16
Roberto	Clemente	1973	15
Nellie	Fox	1997	15
Tony	Gwynn	2007	15
Ozzie	Smith	2002	15
Ernie	Banks	1977	14
Johnny	Bench	1989	14
Reggie	Jackson	1993	14
Frank	Robinson	1982	14
Ivan	Rodriguez	2017	14
Luis	Aparicio	1984	13
George	Brett	1999	13
Joe	DiMaggio	1955	13
Ken	Griffey	2016	13
Harmon	Killebrew	1984	13
Roberto	Alomar	2011	12
Wade	Boggs	2005	12
Barry	Larkin	2012	12
Eddie	Mathews	1978	12
Mel	Ott	1951	12
Mike	Piazza	2016	12
Mike	Schmidt	1995	12
Tom	Seaver	1992	12
Dave	Winfield	2001	12
Gary	Carter	2003	11
Orlando	Cepeda	1999	11
Bill	Dickey	1954	11
Carlton	Fisk	2000	11
Steve	Carlton	1994	10
Whitey	Ford	1974	10
Tom	Glavine	2014	10
Rickey	Henderson	2009	10
Billy	Herman	1975	10
Randy	Johnson	2015	10
George	Kell	1983	10
Juan	Marichal	1983	10
Bill	Mazeroski	2001	10
Joe	Medwick	1968	10
Johnny	Mize	1981	10
Joe	Morgan	1990	10
Kirby	Puckett	2001	10
Pee Wee	Reese	1984	10
Ryne	Sandberg	2005	10
Red	Schoendienst	1989	10
Enos	Slaughter	1985	10
Bobby	Doerr	1986	9
Don	Drysdale	1984	9
Jimmie	Foxx	1951	9
Joe	Gordon	2009	9
Rich	Gossage	2008	9
Vladimir	Guerrero	2018	9
Carl	Hubbell	1947	9
Ron	Santo	2012	9
Arky	Vaughan	1985	9
Early	Wynn	1972	9
Lou	Boudreau	1970	8
Jim	Bunning	1996	8
Roy	Campanella	1969	8
Andre	Dawson	2010	8
Bob	Feller	1962	8
Rick	Ferrell	1984	8
Bob	Gibson	1981	8
Catfish	Hunter	1987	8
Chipper	Jones	2018	8
Ernie	Lombardi	1986	8
Greg	Maddux	2014	8
Pedro	Martinez	2015	8
Eddie	Murray	2003	8
Jim	Rice	2009	8
Nolan	Ryan	1999	8
John	Smoltz	2015	8
Duke	Snider	1980	8
Luke	Appling	1964	7
Craig	Biggio	2015	7
Joe	Cronin	1956	7
Larry	Doby	1998	7
Rollie	Fingers	1992	7
Lou	Gehrig	1939	7
Lefty	Gomez	1972	7
Trevor	Hoffman	2018	7
Sandy	Koufax	1972	7
Bob	Lemon	1976	7
Paul	Molitor	2004	7
Hal	Newhouser	1992	7
Tony	Perez	2000	7
Tim	Raines	2017	7
Robin	Roberts	1976	7
Willie	Stargell	1988	7
Hoyt	Wilhelm	1985	7
Earl	Averill	1975	6
Lou	Brock	1985	6
Dennis	Eckersley	2004	6
Charlie	Gehringer	1949	6
Lefty	Grove	1947	6
Gabby	Hartnett	1955	6
Ralph	Kiner	1975	6
Willie	McCovey	1986	6
Jim	Palmer	1990	6
Jackie	Robinson	1962	6
Red	Ruffing	1967	6
Bruce	Sutter	2006	6
Alan	Trammell	2018	6
Billy	Williams	1987	6
Richie	Ashburn	1995	5
Hank	Greenberg	1956	5
Jack	Morris	2018	5
Phil	Niekro	1997	5
Gaylord	Perry	1991	5
Phil	Rizzuto	1994	5
Frank	Thomas	2014	5
Jim	Thome	2018	5
Jeff	Bagwell	2017	4
Dizzy	Dean	1953	4
Don	Sutton	1998	4
Paul	Waner	1952	4
Frankie	Frisch	1947	3
Fergie	Jenkins	1991	3
Al	Simmons	1953	3
Bill	Terry	1954	3
Robin	Yount	1999	3
Bert	Blyleven	2011	2
Mickey	Cochrane	1947	2
Chuck	Klein	1980	2
Satchel	Paige	1971	2
Babe	Ruth	1936	2
Pie	Traynor	1948	2
Kiki	Cuyler	1968	1
Goose	Goslin	1968	1
Chick	Hafey	1971	1
Monte	Irvin	1973	1
Travis	Jackson	1982	1
Tony	Lazzeri	1991	1
Ted	Lyons	1955	1
Heinie	Manush	1964	1
Lloyd	Waner	1967	1
Pete	Alexander	1938	0
Cap	Anson	1939	0
Home Run	Baker	1955	0
Dave	Bancroft	1971	0
Jake	Beckley	1971	0
Cool Papa	Bell	1974	0
Chief	Bender	1953	0
Jim	Bottomley	1974	0
Roger	Bresnahan	1945	0
Dan	Brouthers	1945	0
Mordecai	Brown	1949	0
Ray	Brown	2006	0
Willard	Brown	2006	0
Jesse	Burkett	1946	0
Max	Carey	1961	0
Frank	Chance	1946	0
Oscar	Charleston	1976	0
Jack	Chesbro	1946	0
Fred	Clarke	1945	0
John	Clarkson	1963	0
Ty	Cobb	1936	0
Eddie	Collins	1939	0
Jimmy	Collins	1945	0
Earle	Combs	1970	0
Roger	Connor	1976	0
Andy	Cooper	2006	0
Stan	Coveleski	1969	0
Sam	Crawford	1957	0
Ray	Dandridge	1987	0
George	Davis	1998	0
Leon	Day	1995	0
Ed	Delahanty	1945	0
Martin	Dihigo	1977	0
Hugh	Duffy	1945	0
Johnny	Evers	1946	0
Buck	Ewing	1939	0
Red	Faber	1964	0
Elmer	Flick	1963	0
Bill	Foster	1996	0
Pud	Galvin	1965	0
Josh	Gibson	1972	0
Frank	Grant	2006	0
Clark	Griffith	1946	0
Burleigh	Grimes	1964	0
Jesse	Haines	1970	0
Billy	Hamilton	1961	0
Harry	Heilmann	1952	0
Pete	Hill	2006	0
Harry	Hooper	1971	0
Rogers	Hornsby	1942	0
Waite	Hoyt	1969	0
Hughie	Jennings	1945	0
Judy	Johnson	1975	0
Walter	Johnson	1936	0
Addie	Joss	1978	0
Tim	Keefe	1964	0
Willie	Keeler	1939	0
Joe	Kelley	1971	0
High Pockets	Kelly	1973	0
King	Kelly	1945	0
Nap	Lajoie	1937	0
Buck	Leonard	1972	0
Freddie	Lindstrom	1976	0
Pop	Lloyd	1977	0
Biz	Mackey	2006	0
Rabbit	Maranville	1954	0
Rube	Marquard	1971	0
Christy	Mathewson	1936	0
Tommy	McCarthy	1946	0
Joe	McGinnity	1946	0
Bid	McPhee	2000	0
Jose	Mendez	2006	0
Kid	Nichols	1949	0
Jim	O’Rourke	1945	0
Herb	Pennock	1948	0
Eddie	Plank	1946	0
Old Hoss	Radbourn	1939	0
Sam	Rice	1963	0
Eppa	Rixey	1963	0
Bullet	Rogan	1998	0
Edd	Roush	1962	0
Amos	Rusie	1977	0
Louis	Santop	2006	0
Ray	Schalk	1955	0
Joe	Sewell	1977	0
George	Sisler	1939	0
Hilton	Smith	2001	0
Tris	Speaker	1937	0
Turkey	Stearnes	2000	0
Mule	Suttles	2006	0
Ben	Taylor	2006	0
Sam	Thompson	1974	0
Joe	Tinker	1946	0
Cristobal	Torriente	2006	0
Dazzy	Vance	1955	0
Rube	Waddell	1946	0
Honus	Wagner	1936	0
Bobby	Wallace	1953	0
Ed	Walsh	1946	0
John	Ward	1964	0
Mickey	Welch	1973	0
Willie	Wells	1997	0
Zack	Wheat	1959	0
Deacon	White	2013	0
Smokey Joe	Williams	1999	0
Vic	Willis	1995	0
Hack	Wilson	1979	0
Jud	Wilson	2006	0
Cy	Young	1937	0
Ross	Youngs	1972	0

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:

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

1,746 records

playerID	yearID	H
abreubo01	2010	146
abreubo01	2011	127
abreujo02	2014	176
abreujo02	2015	178
abreujo02	2016	183
abreujo02	2017	189
abreujo02	2018	132
abreujo02	2019	180
ackledu01	2012	137
ackledu01	2014	123
acunaro01	2018	127
acunaro01	2019	175
adamewi01	2019	135
adamsma01	2014	152
aguilje01	2018	135
ahmedni01	2018	121
ahmedni01	2019	141
alberha01	2019	160
albieoz01	2018	167
albieoz01	2019	189
alfarjo01	2019	113
almoral01	2018	127
alonspe01	2019	155
alonsyo01	2012	150
alonsyo01	2015	100
alonsyo01	2016	122
alonsyo01	2018	129
altheaa01	2017	101
altuvjo01	2012	167
altuvjo01	2013	177
altuvjo01	2014	225
altuvjo01	2015	200
altuvjo01	2016	216
altuvjo01	2017	204
altuvjo01	2018	169
altuvjo01	2019	149
alvarpe01	2012	128
alvarpe01	2013	130
alvarpe01	2015	106
amarial01	2014	101
anderbr06	2018	161
anderbr06	2019	120
anderti01	2016	116
anderti01	2017	151
anderti01	2018	136
anderti01	2019	167
andinro01	2011	120
andruel01	2010	156
andruel01	2011	164
andruel01	2012	180
andruel01	2013	168
andruel01	2014	163
andruel01	2015	154
andruel01	2016	153
andruel01	2017	191
andruel01	2018	101
andruel01	2019	165
andujmi01	2018	170
aokino01	2012	150
aokino01	2013	171
aokino01	2014	140
aokino01	2015	102
aokino01	2016	118
arciaor01	2017	140
arciaor01	2019	110
arenano01	2013	130
arenano01	2014	124
arenano01	2015	177
arenano01	2016	182
arenano01	2017	187
arenano01	2018	175
arenano01	2019	185
arraelu01	2019	109
ascheco01	2014	100
ascheco01	2015	104
avilaal01	2011	137
avilemi01	2010	129
avilemi01	2012	128
aybarer01	2010	135
aybarer01	2011	155
aybarer01	2012	150
aybarer01	2013	149
aybarer01	2014	164
aybarer01	2015	161
baderha01	2018	100
baezja01	2016	115
baezja01	2017	128
baezja01	2018	176
baezja01	2019	149
barmecl01	2011	109
barmecl01	2012	104
barneda01	2011	146
barneda01	2012	139
barneda01	2013	104
barnhtu01	2017	100
barnhtu01	2018	114
bartlja01	2010	119
bartlja01	2011	136
bartoda02	2010	152
bautijo02	2010	148
bautijo02	2011	155
bautijo02	2013	117
bautijo02	2014	158
bautijo02	2015	136
bautijo02	2017	119
bayja01	2011	109
beckhgo01	2010	112
beckhgo01	2011	115
beckhgo01	2012	123
bellico01	2017	128
bellico01	2018	145
bellico01	2019	170
belljo02	2017	140
belljo02	2018	131
belljo02	2019	146
beltbr01	2012	113
beltbr01	2013	147
beltbr01	2015	138
beltbr01	2016	149
beltbr01	2018	101
beltbr01	2019	123
beltrad01	2010	189
beltrad01	2011	144
beltrad01	2012	194
beltrad01	2013	199
beltrad01	2014	178
beltrad01	2015	163
beltrad01	2016	175
beltrad01	2017	106
beltrad01	2018	118
beltrca01	2011	102
beltrca01	2012	147
beltrca01	2013	164
beltrca01	2015	132
beltrca01	2016	109
beltrca01	2017	108
beninan01	2017	155
beninan01	2018	168
beninan01	2019	144
berkmla01	2011	147
bernaro01	2010	102
betanyu01	2010	144
betanyu01	2011	140
bettsmo01	2015	174
bettsmo01	2016	214
bettsmo01	2017	166
bettsmo01	2018	180
bettsmo01	2019	176
blackch02	2014	171
blackch02	2015	176
blackch02	2016	187
blackch02	2017	213
blackch02	2018	182
blackch02	2019	182
blakeca01	2010	126
blancgr01	2013	120
blancgr01	2014	102
boescbr01	2010	119
boescbr01	2011	121
boescbr01	2012	113
bogaexa01	2014	129
bogaexa01	2015	196
bogaexa01	2016	192
bogaexa01	2017	156
bogaexa01	2018	148
bogaexa01	2019	190
bonifem01	2011	167
borboju01	2010	121
bourjpe01	2011	136
bourju01	2015	107
bourju01	2017	109
bournmi01	2010	142
bournmi01	2011	130
bournmi01	2012	171
bournmi01	2013	138
bournmi01	2014	114
bradlja02	2016	149
bradlja02	2017	118
bradlja02	2018	111
bradlja02	2019	111
brantmi02	2011	120
brantmi02	2012	159
brantmi02	2013	158
brantmi02	2014	200
brantmi02	2015	164
brantmi02	2017	101
brantmi02	2018	176
brantmi02	2019	179
braunry02	2010	188
braunry02	2011	187
braunry02	2012	191
braunry02	2014	141
braunry02	2015	144
braunry02	2016	156
braunry02	2017	102
braunry02	2018	103
braunry02	2019	131
bregmal01	2017	158
bregmal01	2018	170
bregmal01	2019	164
browndo01	2013	135
browndo01	2014	111
bruceja01	2010	143
bruceja01	2011	150
bruceja01	2012	141
bruceja01	2013	164
bruceja01	2014	107
bruceja01	2015	131
bruceja01	2017	104
bryankr01	2015	154
bryankr01	2016	176
bryankr01	2017	162
bryankr01	2018	106
bryankr01	2019	153
buckjo01	2010	115
buckjo01	2011	106
burnsbi02	2015	153
butlebi03	2010	189
butlebi03	2011	174
butlebi03	2012	192
butlebi03	2013	168
butlebi03	2014	149
butlebi03	2015	135
buxtoby01	2017	117
byrdma01	2010	170
byrdma01	2011	123
byrdma01	2013	121
byrdma01	2014	156
cabreas01	2010	105
cabreas01	2011	165
cabreas01	2012	150
cabreas01	2013	123
cabreas01	2015	134
cabreas01	2016	146
cabreas01	2017	134
cabreas01	2018	104
cabreev01	2013	108
cabreme01	2010	117
cabreme01	2011	201
cabreme01	2012	159
cabreme01	2014	171
cabreme01	2015	172
cabreme01	2016	175
cabreme01	2017	117
cabreme01	2019	106
cabremi01	2010	180
cabremi01	2011	197
cabremi01	2012	205
cabremi01	2013	193
cabremi01	2014	191
cabremi01	2015	145
cabremi01	2016	188
cabremi01	2017	117
cabremi01	2019	139
cabreor01	2010	130
cainlo01	2013	100
cainlo01	2014	142
cainlo01	2015	169
cainlo01	2016	114
cainlo01	2017	175
cainlo01	2018	166
cainlo01	2019	146
calhoko01	2014	134
calhoko01	2015	161
calhoko01	2016	161
calhoko01	2017	139
calhoko01	2018	102
calhoko01	2019	128
callaal01	2011	137
callaal01	2012	115
camarjo01	2018	126
candeje01	2018	121
canhama01	2015	112
canhama01	2019	112
canoro01	2010	200
canoro01	2011	188
canoro01	2012	196
canoro01	2013	190
canoro01	2014	187
canoro01	2015	179
canoro01	2016	195
canoro01	2017	166
canoro01	2019	100
carpema01	2013	199
carpema01	2014	162
carpema01	2015	156
carpema01	2016	128
carpema01	2017	120
carpema01	2018	145
carroja01	2010	102
carroja01	2011	131
carroja01	2012	126
cartech02	2013	113
cartech02	2014	115
cartech02	2016	122
casteni01	2014	138
casteni01	2015	140
casteni01	2016	117
casteni01	2017	167
casteni01	2018	185
casteni01	2019	110
castiwe01	2013	104
castiwe01	2016	110
castrha01	2019	103
castrja01	2013	120
castrja01	2014	103
castrst01	2010	139
castrst01	2011	207
castrst01	2012	183
castrst01	2013	163
castrst01	2014	154
castrst01	2015	145
castrst01	2016	156
castrst01	2017	133
castrst01	2018	165
castrst01	2019	172
cedenro02	2010	120
cedenro02	2011	103
cervefr01	2015	133
cespeyo01	2012	142
cespeyo01	2013	127
cespeyo01	2014	102
cespeyo01	2015	118
cespeyo01	2016	134
chapmma01	2018	152
chapmma01	2019	145
chiselo01	2014	134
chiselo01	2016	110
choiji01	2019	107
choosh01	2010	165
choosh01	2012	169
choosh01	2013	162
choosh01	2014	110
choosh01	2015	153
choosh01	2017	142
choosh01	2018	148
choosh01	2019	149
coghlch01	2014	109
coghlch01	2015	110
colabch01	2015	107
colvity01	2012	122
confomi01	2017	104
confomi01	2018	132
confomi01	2019	141
contrwi01	2017	104
contrwi01	2018	118
coopega03	2019	107
correca01	2015	108
correca01	2016	158
correca01	2017	133
cozarza01	2012	138
cozarza01	2013	144
cozarza01	2014	112
cozarza01	2016	117
cozarza01	2017	130
craigal01	2012	144
craigal01	2013	160
crawfbr01	2012	108
crawfbr01	2013	124
crawfbr01	2014	121
crawfbr01	2015	130
crawfbr01	2016	152
crawfbr01	2017	131
crawfbr01	2018	135
crawfbr01	2019	114
crawfca02	2010	184
crawfca02	2011	129
crawfca02	2013	123
crawfca02	2014	103
crispco01	2011	140
crispco01	2012	118
crispco01	2013	134
crispco01	2014	114
croncj01	2016	113
croncj01	2018	127
croncj01	2019	116
crowetr01	2010	111
cruzne02	2010	127
cruzne02	2011	125
cruzne02	2012	152
cruzne02	2013	110
cruzne02	2014	166
cruzne02	2015	178
cruzne02	2016	169
cruzne02	2017	160
cruzne02	2018	133
cruzne02	2019	141
cuddymi01	2010	165
cuddymi01	2011	150
cuddymi01	2013	162
cuthbch01	2016	130
dahlda01	2019	113
damonjo01	2010	146
damonjo01	2011	152
davisch02	2012	139
davisch02	2013	167
davisch02	2015	150
davisch02	2016	125
davisik02	2010	138
davisik02	2012	118
davisjd01	2019	126
daviskh01	2014	122
daviskh01	2016	137
daviskh01	2017	140
daviskh01	2018	142
daviskh01	2019	106
davisra01	2010	149
davisra01	2012	115
davisra01	2014	130
davisra01	2016	113
deazaal01	2012	147
deazaal01	2013	160
dejesda01	2010	112
dejesda01	2011	106
dejesda01	2012	133
dejonpa01	2017	119
dejonpa01	2018	105
dejonpa01	2019	136
denorch01	2012	102
denorch01	2013	132
deshide02	2015	111
deshide02	2017	101
desmoia01	2010	141
desmoia01	2011	148
desmoia01	2012	150
desmoia01	2013	168
desmoia01	2014	151
desmoia01	2015	136
desmoia01	2016	178
desmoia01	2018	131
desmoia01	2019	113
deverra01	2018	108
deverra01	2019	201
diazal02	2016	121
diazal02	2018	111
dickeco01	2014	136
dickeco01	2016	125
dickeco01	2017	166
dickeco01	2018	151
dietrde01	2017	101
dietrde01	2018	132
dirksan01	2012	101
dirksan01	2013	112
dobbsgr01	2011	113
dominma01	2013	131
dominma01	2014	121
donaljo02	2013	174
donaljo02	2014	155
donaljo02	2015	184
donaljo02	2016	164
donaljo02	2017	112
donaljo02	2019	142
doumiry01	2010	102
doumiry01	2012	133
doumiry01	2013	120
doziebr01	2013	136
doziebr01	2014	145
doziebr01	2015	148
doziebr01	2016	165
doziebr01	2017	167
doziehu01	2019	146
drewjd01	2010	122
drewst01	2010	157
drewst01	2013	112
drurybr01	2016	130
drurybr01	2017	119
dudalu01	2014	130
dudalu01	2015	115
duffyma01	2015	169
duffyma01	2018	148
dunnad01	2010	145
dunnad01	2012	110
dunnad01	2013	115
duvalad01	2016	133
duvalad01	2017	146
eatonad02	2014	146
eatonad02	2015	175
eatonad02	2016	176
eatonad02	2019	158
eckstda01	2010	118
ellisaj01	2012	114
ellisma01	2010	127
ellisma01	2012	107
ellisma01	2013	117
ellsbja01	2011	212
ellsbja01	2013	172
ellsbja01	2014	156
ellsbja01	2015	116
ellsbja01	2016	145
encared01	2011	131
encared01	2012	152
encared01	2013	144
encared01	2014	128
encared01	2015	146
encared01	2016	158
encared01	2017	143
encared01	2018	123
engelad01	2018	101
escobal02	2010	119
escobal02	2011	139
escobal02	2012	177
escobal02	2013	142
escobal02	2014	165
escobal02	2015	157
escobal02	2016	166
escobal02	2017	150
escobal02	2018	112
escobed01	2014	119
escobed01	2015	107
escobed01	2017	116
escobed01	2018	101
escobed01	2019	171
escobyu01	2011	149
escobyu01	2012	141
escobyu01	2013	130
escobyu01	2014	123
escobyu01	2015	168
escobyu01	2016	157
espinda01	2011	135
espinda01	2012	147
espinda01	2016	108
ethiean01	2010	151
ethiean01	2011	142
ethiean01	2012	158
ethiean01	2013	131
ethiean01	2015	116
fieldpr01	2010	151
fieldpr01	2011	170
fieldpr01	2012	182
fieldpr01	2013	174
fieldpr01	2015	187
figgich01	2010	156
fletcda02	2019	173
florewi01	2015	127
florewi01	2018	103
forsylo01	2015	152
forsylo01	2016	135
fowlede01	2010	114
fowlede01	2011	128
fowlede01	2012	136
fowlede01	2013	109
fowlede01	2014	120
fowlede01	2015	149
fowlede01	2016	126
fowlede01	2017	111
fowlede01	2019	116
francje02	2011	171
francje02	2012	132
francma02	2016	148
francma02	2017	132
francma02	2018	117
fraziad01	2017	112
fraziad01	2019	154
frazito01	2012	115
frazito01	2013	124
frazito01	2014	163
frazito01	2015	158
frazito01	2016	133
frazito01	2019	112
freemfr01	2011	161
freemfr01	2012	140
freemfr01	2013	176
freemfr01	2014	175
freemfr01	2015	115
freemfr01	2016	178
freemfr01	2017	135
freemfr01	2018	191
freemfr01	2019	176
freesda01	2012	147
freesda01	2013	121
freesda01	2014	120
freesda01	2015	109
freesda01	2016	118
freesda01	2017	112
furcara01	2010	115
furcara01	2012	126
gallojo01	2018	103
galvifr01	2015	147
galvifr01	2016	141
galvifr01	2017	155
galvifr01	2018	149
galvifr01	2019	120
gamelbe01	2017	140
garciad01	2016	145
garciav01	2015	142
garciav01	2016	101
garciav01	2017	171
garciav01	2019	138
garcile02	2019	161
gardnbr01	2010	132
gardnbr01	2011	132
gardnbr01	2013	147
gardnbr01	2014	142
gardnbr01	2015	148
gardnbr01	2016	143
gardnbr01	2017	157
gardnbr01	2018	125
gardnbr01	2019	123
gattiev01	2015	139
gattiev01	2016	112
gennesc01	2014	127
gennesc01	2016	131
gennesc01	2017	136
gennesc01	2018	181
giavojo01	2015	123
gillaco01	2013	100
gillaco01	2014	131
goldspa01	2012	147
goldspa01	2013	182
goldspa01	2014	122
goldspa01	2015	182
goldspa01	2016	172
goldspa01	2017	166
goldspa01	2018	172
goldspa01	2019	155
gomesjo01	2010	136
gomesya01	2014	135
gomesya01	2018	107
gomezca01	2012	108
gomezca01	2013	152
gomezca01	2014	163
gonzaad01	2010	176
gonzaad01	2011	213
gonzaad01	2012	145
gonzaad01	2013	171
gonzaad01	2014	163
gonzaad01	2015	157
gonzaad01	2016	162
gonzaal02	2011	136
gonzaca01	2010	197
gonzaca01	2011	142
gonzaca01	2012	157
gonzaca01	2013	118
gonzaca01	2015	150
gonzaca01	2016	174
gonzaca01	2017	123
gonzaca01	2018	128
gonzama01	2016	123
gonzama01	2017	138
gonzama01	2018	121
gonzama01	2019	112
goodrni01	2018	109
goodrni01	2019	105
goodwbr01	2019	108
gordoal01	2011	185
gordoal01	2012	189
gordoal01	2013	168
gordoal01	2014	150
gordoal01	2018	124
gordoal01	2019	148
gordode01	2014	176
gordode01	2015	205
gordode01	2017	201
gordode01	2018	149
gordode01	2019	108
gosean01	2015	123
gourryu01	2017	158
gourryu01	2018	156
gourryu01	2019	168
grandcu01	2010	115
grandcu01	2011	153
grandcu01	2012	138
grandcu01	2014	128
grandcu01	2015	150
grandcu01	2016	129
grandya01	2017	108
grandya01	2018	106
grandya01	2019	126
gregodi01	2015	139
gregodi01	2016	155
gregodi01	2017	153
gregodi01	2018	135
grichra01	2016	107
grichra01	2018	104
grichra01	2019	136
grossro01	2018	108
grossro01	2019	101
guerrvl01	2010	178
guerrvl01	2011	163
guerrvl02	2019	126
guilljo01	2010	101
gutiefr01	2010	139
gyorkje01	2013	121
gyorkje01	2015	104
gyorkje01	2017	116
hafnetr01	2010	110
hairsje02	2010	105
hamilbi02	2014	141
hamilbi02	2016	107
hamilbi02	2017	144
hamilbi02	2018	119
hamiljo03	2010	186
hamiljo03	2011	145
hamiljo03	2012	160
hamiljo03	2013	144
hanigmi01	2017	104
hanigmi01	2018	170
hardyjj01	2011	142
hardyjj01	2012	158
hardyjj01	2013	158
hardyjj01	2014	142
hardyjj01	2016	109
harpebr03	2012	144
harpebr03	2013	116
harpebr03	2015	172
harpebr03	2016	123
harpebr03	2017	134
harpebr03	2018	137
harpebr03	2019	149
harrijo05	2014	164
harrijo05	2015	120
harrijo05	2016	138
harrijo05	2017	132
hartco01	2010	158
hartco01	2011	140
hartco01	2012	152
headlch01	2010	161
headlch01	2011	110
headlch01	2012	173
headlch01	2013	130
headlch01	2015	150
headlch01	2016	118
headlch01	2017	140
healyry01	2017	156
healyry01	2018	116
hechaad01	2013	123
hechaad01	2014	148
hechaad01	2015	132
hechaad01	2016	120
heltoto01	2010	102
heltoto01	2011	127
hernace02	2015	110
hernace02	2016	161
hernace02	2017	150
hernace02	2018	153
hernace02	2019	171
hernaen02	2018	103
hernate01	2018	114
herreod01	2015	147
herreod01	2016	167
herreod01	2017	148
herreod01	2018	140
heywaja01	2010	144
heywaja01	2012	158
heywaja01	2014	155
heywaja01	2015	160
heywaja01	2016	122
heywaja01	2017	112
heywaja01	2018	119
heywaja01	2019	129
hicksaa01	2018	119
hillaa01	2010	108
hillaa01	2012	184
hillaa01	2014	122
hollima01	2010	186
hollima01	2011	132
hollima01	2012	177
hollima01	2013	156
hollima01	2014	156
holtbr01	2014	126
holtbr01	2015	127
hoskirh01	2018	137
hoskirh01	2019	129
hosmeer01	2011	153
hosmeer01	2012	124
hosmeer01	2013	188
hosmeer01	2014	136
hosmeer01	2015	178
hosmeer01	2016	161
hosmeer01	2017	192
hosmeer01	2018	155
hosmeer01	2019	164
howarry01	2010	152
howarry01	2011	141
howarry01	2014	127
howarry01	2015	107
hudsoor01	2010	133
huffau01	2010	165
huffau01	2011	128
hundlni01	2015	110
hunteto01	2010	161
hunteto01	2011	152
hunteto01	2012	167
hunteto01	2013	184
hunteto01	2014	157
hunteto01	2015	125
ibanera01	2010	154
ibanera01	2011	131
ibanera01	2013	110
iglesjo01	2015	125
iglesjo01	2016	119
iglesjo01	2017	118
iglesjo01	2018	116
iglesjo01	2019	145
inciaen01	2014	116
inciaen01	2015	159
inciaen01	2016	152
inciaen01	2017	201
inciaen01	2018	158
infanom01	2010	151
infanom01	2011	160
infanom01	2013	144
infanom01	2014	133
ingebr01	2010	127
izturce01	2010	109
izturma01	2011	124
jacksau01	2010	181
jacksau01	2011	147
jacksau01	2012	163
jacksau01	2013	150
jacksau01	2014	102
jacksau01	2015	114
jasojo01	2016	102
jayjo02	2011	135
jayjo02	2012	135
jayjo02	2013	151
jayjo02	2014	125
jayjo02	2016	101
jayjo02	2017	112
jennide01	2012	124
jennide01	2013	133
jennide01	2014	117
jeterde01	2010	179
jeterde01	2011	162
jeterde01	2012	216
jeterde01	2014	149
jimenel02	2019	125
johnsch05	2010	105
johnsch05	2013	165
johnsch05	2014	153
johnske05	2010	166
johnske05	2012	114
jonesad01	2010	165
jonesad01	2011	159
jonesad01	2012	186
jonesad01	2013	186
jonesad01	2014	181
jonesad01	2015	147
jonesad01	2016	164
jonesad01	2017	170
jonesad01	2018	163
jonesad01	2019	126
jonesch06	2011	125
jonesch06	2012	111
jonesga02	2010	146
jonesga02	2011	103
jonesga02	2012	130
jonesga02	2014	122
josepto01	2017	119
joycema01	2011	128
joycema01	2014	106
joycema01	2017	114
judgeaa01	2017	154
judgeaa01	2018	115
judgeaa01	2019	103
kangju01	2015	121
kempma01	2010	150
kempma01	2011	195
kempma01	2012	122
kempma01	2014	155
kempma01	2015	158
kempma01	2016	107
kempma01	2017	121
kempma01	2018	134
kendaja01	2010	111
kendrho01	2010	172
kendrho01	2011	153
kendrho01	2012	158
kendrho01	2013	142
kendrho01	2014	181
kendrho01	2015	137
kendrho01	2016	124
kendrho01	2019	115
keplema01	2017	124
keplema01	2018	119
keplema01	2019	132
keppije01	2010	148
keppije01	2012	125
keppije01	2013	107
kiermke01	2015	133
kiermke01	2017	105
kiermke01	2019	102
kingesc01	2018	102
kingesc01	2019	118
kinslia01	2010	112
kinslia01	2011	158
kinslia01	2012	168
kinslia01	2013	151
kinslia01	2014	188
kinslia01	2015	185
kinslia01	2016	178
kinslia01	2017	130
kipnija01	2012	152
kipnija01	2013	160
kipnija01	2014	120
kipnija01	2015	171
kipnija01	2016	168
kipnija01	2018	122
kipnija01	2019	112
konerpa01	2010	171
konerpa01	2011	163
konerpa01	2012	159
konerpa01	2013	114
kotchca01	2011	153
kotchca01	2012	106
kouzmke01	2010	136
kubelja01	2010	129
kubelja01	2011	100
kubelja01	2012	128
lagarju01	2014	117
lagarju01	2015	114
lambja01	2016	130
lambja01	2017	133
larocad01	2010	146
larocad01	2012	155
larocad01	2013	121
larocad01	2014	128
laurera01	2019	125
lawribr01	2012	135
lawribr01	2013	102
lawribr01	2015	146
leeca01	2010	149
leeca01	2011	161
leede02	2010	105
lemahdj01	2013	113
lemahdj01	2014	132
lemahdj01	2015	170
lemahdj01	2016	192
lemahdj01	2017	189
lemahdj01	2018	147
lemahdj01	2019	197
lewisfr02	2010	112
lindad01	2010	135
lindad01	2011	125
lindad01	2013	134
lindad01	2015	139
lindofr01	2015	122
lindofr01	2016	182
lindofr01	2017	178
lindofr01	2018	183
lindofr01	2019	170
lombast02	2012	105
loneyja01	2010	157
loneyja01	2011	153
loneyja01	2013	164
loneyja01	2014	174
loneyja01	2015	101
longoev01	2010	169
longoev01	2011	118
longoev01	2013	165
longoev01	2014	158
longoev01	2015	163
longoev01	2016	173
longoev01	2017	160
longoev01	2018	117
longoev01	2019	115
lopezjo01	2010	142
lowrije01	2013	175
lowrije01	2014	125
lowrije01	2017	157
lowrije01	2018	159
lucrojo01	2011	114
lucrojo01	2012	101
lucrojo01	2013	146
lucrojo01	2014	176
lucrojo01	2016	101
lucrojo01	2018	100
ludwiry01	2012	116
machama01	2013	189
machama01	2015	181
machama01	2016	188
machama01	2017	163
machama01	2018	115
machama01	2019	150
mancitr01	2017	159
mancitr01	2018	141
mancitr01	2019	175
margoma01	2017	128
margoma01	2018	117
markani01	2010	187
markani01	2011	182
markani01	2012	125
markani01	2013	172
markani01	2014	177
markani01	2015	181
markani01	2016	161
markani01	2017	163
markani01	2018	185
markani01	2019	118
marteke01	2016	113
marteke01	2018	135
marteke01	2019	187
martest01	2013	143
martest01	2014	144
martest01	2015	166
martest01	2016	152
martest01	2018	155
martest01	2019	159
martijd02	2014	139
martijd02	2015	168
martijd02	2016	141
martijd02	2018	188
martijd02	2019	175
martijo08	2018	163
martile01	2013	119
martile01	2014	146
martile01	2016	128
martiru01	2014	110
martiru01	2015	106
martiru01	2016	105
martivi01	2010	149
martivi01	2011	178
martivi01	2013	182
martivi01	2014	188
martivi01	2015	108
martivi01	2016	160
martivi01	2017	100
martivi01	2018	117
matsuhi01	2010	132
matsuhi01	2011	130
mauerjo01	2010	167
mauerjo01	2012	174
mauerjo01	2013	144
mauerjo01	2014	126
mauerjo01	2015	157
mauerjo01	2016	129
mauerjo01	2017	160
mauerjo01	2018	137
maybejo02	2012	108
maybica01	2011	136
maybica01	2012	123
maybica01	2015	135
maybica01	2016	110
mazarno01	2016	137
mazarno01	2017	140
mazarno01	2018	126
mazarno01	2019	115
mccanbr01	2010	129
mccanbr01	2011	126
mccanbr01	2012	101
mccanbr01	2014	115
mccanbr01	2015	108
mccanbr01	2016	104
mccanja02	2015	106
mccanja02	2019	120
mccutan01	2010	163
mccutan01	2011	148
mccutan01	2012	194
mccutan01	2013	185
mccutan01	2014	172
mccutan01	2015	165
mccutan01	2016	153
mccutan01	2017	159
mccutan01	2018	123
mcgehca01	2010	174
mcgehca01	2011	122
mcgehca01	2014	177
mclouna01	2013	137
mcmahry01	2019	120
mcneije01	2019	162
meadoau01	2019	154
mercaos01	2019	118
mercejo03	2014	129
mercejo03	2016	133
mercejo03	2017	128
merriwh01	2017	169
merriwh01	2018	192
merriwh01	2019	206
mesorde01	2014	105
milesaa01	2011	125
millebr02	2015	113
millebr02	2016	133
millela02	2010	105
molinya01	2010	122
molinya01	2011	145
molinya01	2012	159
molinya01	2013	161
molinya01	2014	114
molinya01	2015	132
molinya01	2016	164
molinya01	2017	137
molinya01	2018	120
molinya01	2019	113
moncayo01	2018	136
moncayo01	2019	161
mondera02	2019	109
monteje01	2012	134
montemi01	2011	139
montemi01	2012	139
montemi01	2014	119
moralke01	2012	132
moralke01	2013	167
moralke01	2015	165
moralke01	2016	147
moralke01	2017	139
moralke01	2018	103
moranco01	2018	115
moranco01	2019	129
morelbr01	2011	101
morelmi01	2011	120
morelmi01	2013	107
morelmi01	2015	131
morelmi01	2016	107
morelmi01	2017	125
morgany01	2010	129
morgany01	2011	115
morneju01	2010	102
morneju01	2012	135
morneju01	2013	128
morneju01	2014	160
morrilo01	2011	114
morrilo01	2015	103
morrilo01	2017	126
morsemi01	2011	158
morsemi01	2012	118
morsemi01	2014	122
mossbr01	2013	114
mossbr01	2014	117
moustmi01	2012	136
moustmi01	2013	110
moustmi01	2015	156
moustmi01	2017	151
moustmi01	2019	133
muncyma01	2018	104
muncyma01	2019	122
murphda07	2010	122
murphda07	2011	111
murphda07	2012	139
murphda07	2014	109
murphda08	2011	125
murphda08	2012	166
murphda08	2013	188
murphda08	2014	172
murphda08	2015	140
murphda08	2016	184
murphda08	2017	172
murphda08	2019	122
myerswi01	2016	155
myerswi01	2017	138
myerswi01	2019	104
napolmi01	2010	108
napolmi01	2011	118
napolmi01	2013	129
napolmi01	2014	103
napolmi01	2016	133
narvaom01	2019	119
navada01	2013	139
navardi01	2014	132
nelsoch01	2012	104
newmake01	2019	152
nimmobr01	2018	114
norride01	2014	104
norride01	2015	129
nunezed02	2016	110
nunezed02	2018	127
nunezre01	2019	132
odorro01	2014	100
odorro01	2015	111
odorro01	2016	164
odorro01	2017	124
odorro01	2018	120
odorro01	2019	107
ohtansh01	2019	110
olivomi01	2010	106
olivomi01	2011	107
olsonma02	2018	143
olsonma02	2019	129
orlanpa01	2016	138
ortizda01	2010	140
ortizda01	2011	162
ortizda01	2012	103
ortizda01	2013	160
ortizda01	2014	136
ortizda01	2015	144
ortizda01	2016	169
overbly01	2010	130
overbly01	2013	107
owingch01	2015	117
owingch01	2016	121
ozunama01	2014	152
ozunama01	2015	119
ozunama01	2016	148
ozunama01	2017	191
ozunama01	2018	163
ozunama01	2019	117
pachejo01	2012	147
paganan01	2010	168
paganan01	2011	125
paganan01	2012	174
paganan01	2014	115
paganan01	2015	134
paganan01	2016	137
palkada01	2018	100
panikjo01	2015	119
panikjo01	2016	111
panikjo01	2017	147
paredji01	2015	100
parrage01	2011	130
parrage01	2012	105
parrage01	2013	161
parrage01	2014	105
parrage01	2015	106
parrage01	2017	121
parrage01	2018	114
pederjo01	2015	101
pederjo01	2016	100
pederjo01	2019	112
pedrodu01	2011	195
pedrodu01	2012	163
pedrodu01	2013	193
pedrodu01	2014	153
pedrodu01	2015	111
pedrodu01	2016	201
pedrodu01	2017	119
penaca01	2011	111
pencehu01	2010	173
pencehu01	2011	123
pencehu01	2012	108
pencehu01	2013	178
pencehu01	2014	180
pencehu01	2016	114
pencehu01	2017	128
pennicl01	2010	127
pennicl01	2011	136
peralda01	2015	144
peralda01	2017	154
peralda01	2018	164
peralda01	2019	105
peraljh01	2011	157
peraljh01	2012	127
peraljh01	2013	124
peraljh01	2014	147
peraljh01	2015	159
perazjo01	2017	126
perazjo01	2018	182
perezhe01	2016	110
perezhe01	2017	112
perezsa02	2013	145
perezsa02	2014	150
perezsa02	2015	138
perezsa02	2016	127
perezsa02	2017	126
perezsa02	2018	120
peterja01	2015	126
phamth01	2017	136
phamth01	2019	155
phillbr01	2010	172
phillbr01	2011	183
phillbr01	2012	163
phillbr01	2013	158
phillbr01	2014	123
phillbr01	2015	173
phillbr01	2016	160
phillbr01	2017	137
pierrju01	2010	179
pierrju01	2011	178
pierrju01	2012	121
pierzaj01	2010	128
pierzaj01	2011	133
pierzaj01	2012	133
pierzaj01	2013	137
pierzaj01	2015	122
pillake01	2015	163
pillake01	2016	146
pillake01	2017	150
pillake01	2018	129
pillake01	2019	157
pireljo01	2018	109
piscost01	2016	159
piscost01	2018	146
plouftr01	2013	121
plouftr01	2014	134
plouftr01	2015	140
podsesc01	2010	121
polangr01	2015	152
polangr01	2016	136
polangr01	2018	117
polanjo01	2017	125
polanjo01	2019	186
polanpl01	2010	165
polanpl01	2011	130
polloaj01	2013	119
polloaj01	2015	192
polloaj01	2017	113
polloaj01	2018	106
poseybu01	2010	124
poseybu01	2012	178
poseybu01	2013	153
poseybu01	2014	170
poseybu01	2015	177
poseybu01	2016	155
poseybu01	2017	158
poseybu01	2018	113
poseybu01	2019	104
pradoma01	2010	184
pradoma01	2011	143
pradoma01	2012	186
pradoma01	2013	172
pradoma01	2014	109
pradoma01	2015	144
pradoma01	2016	183
profaju01	2018	133
profaju01	2019	100
puigya01	2013	122
puigya01	2014	165
puigya01	2017	131
puigya01	2018	108
pujolal01	2010	183
pujolal01	2011	173
pujolal01	2012	173
pujolal01	2013	101
pujolal01	2014	172
pujolal01	2015	147
pujolal01	2016	159
pujolal01	2017	143
pujolal01	2018	114
pujolal01	2019	120
quentca01	2010	110
quentca01	2011	107
raburry01	2010	104
ramiral03	2010	165
ramiral03	2011	165
ramiral03	2012	157
ramiral03	2013	181
ramiral03	2014	170
ramiral03	2015	145
ramiral03	2016	101
ramirar01	2010	112
ramirar01	2011	173
ramirar01	2012	171
ramirar01	2014	141
ramirha01	2010	163
ramirha01	2013	105
ramirha01	2014	127
ramirha01	2015	100
ramirha01	2016	157
ramirha01	2017	120
ramirha02	2019	116
ramirjo01	2016	176
ramirjo01	2017	186
ramirjo01	2018	156
ramirjo01	2019	123
ramoswi01	2011	104
ramoswi01	2015	109
ramoswi01	2016	148
ramoswi01	2019	136
rasmuco01	2010	128
rasmuco01	2012	126
rasmuco01	2013	115
rasmuco01	2015	103
realmjt01	2015	114
realmjt01	2016	154
realmjt01	2017	148
realmjt01	2018	132
realmjt01	2019	148
reddijo01	2012	148
reddijo01	2015	143
reddijo01	2017	150
reddijo01	2018	105
reddijo01	2019	138
rendoan01	2014	176
rendoan01	2016	153
rendoan01	2017	153
rendoan01	2018	163
rendoan01	2019	174
renfrhu01	2017	103
renfrhu01	2018	100
reverbe01	2011	120
reverbe01	2012	150
reverbe01	2014	184
reverbe01	2015	109
reyesjo01	2010	159
reyesjo01	2011	181
reyesjo01	2012	184
reyesjo01	2013	113
reyesjo01	2014	175
reyesjo01	2017	123
reynobr01	2019	154
reynoma01	2011	118
reynoma01	2012	101
reynoma01	2016	111
reynoma01	2017	139
riosal01	2010	161
riosal01	2011	122
riosal01	2012	184
riosal01	2013	119
riosal01	2014	138
riverju01	2010	105
rizzoan01	2013	141
rizzoan01	2014	150
rizzoan01	2015	163
rizzoan01	2016	170
rizzoan01	2017	156
rizzoan01	2018	160
rizzoan01	2019	150
roberry01	2011	120
roblevi01	2019	139
rodrial01	2010	141
rodrial01	2011	103
rodrial01	2012	126
rodrial01	2015	131
rodriiv01	2010	106
rojasmi02	2018	123
rojasmi02	2019	137
rolensc01	2010	134
rolliji01	2011	152
rolliji01	2012	158
rolliji01	2013	151
rolliji01	2014	131
rolliji01	2015	116
rosaram01	2018	142
rosaram01	2019	177
rosared01	2015	121
rosared01	2017	157
rosared01	2018	161
rosared01	2019	155
rosarwi01	2012	107
rosarwi01	2013	131
rosarwi01	2014	102
rossco01	2010	120
rossco01	2012	127
ruizca01	2010	112
ruizca01	2011	116
ruizca01	2012	121
russead02	2015	115
russead02	2016	125
russead02	2018	105
ryanbr01	2011	108
saltaja01	2013	116
sanchca01	2017	129
sanchca01	2018	145
sanchca01	2019	125
sanchfr01	2010	126
sanchga01	2010	156
sanchga01	2011	152
sanchga02	2017	131
sandopa01	2010	151
sandopa01	2011	134
sandopa01	2012	112
sandopa01	2013	146
sandopa01	2014	164
sandopa01	2015	115
sanomi01	2016	103
sanomi01	2017	112
santaca01	2011	132
santaca01	2012	128
santaca01	2013	145
santaca01	2014	125
santaca01	2015	127
santaca01	2016	151
santaca01	2017	148
santaca01	2018	128
santaca01	2019	161
santada01	2014	129
santada01	2019	134
santado01	2017	146
santado01	2019	114
saundmi01	2012	125
saundmi01	2016	124
schebsc01	2017	110
schiena01	2013	116
schoojo01	2016	164
schoojo01	2017	182
schoojo01	2019	111
schumsk01	2010	126
schumsk01	2011	104
schwaky01	2018	102
schwaky01	2019	132
scottlu01	2010	127
scutama01	2010	174
scutama01	2011	118
scutama01	2012	102
scutama01	2013	145
seageco01	2016	193
seageco01	2017	159
seageco01	2019	133
seageky01	2012	154
seageky01	2013	160
seageky01	2014	158
seageky01	2015	166
seageky01	2016	166
seageky01	2017	144
seageky01	2018	129
segurje01	2013	173
segurje01	2014	126
segurje01	2015	144
segurje01	2016	203
segurje01	2017	157
segurje01	2018	178
segurje01	2019	161
semiema01	2015	143
semiema01	2016	135
semiema01	2018	161
semiema01	2019	187
shawtr01	2016	116
shawtr01	2017	147
shawtr01	2018	120
shuckja01	2013	128
simmoan01	2013	150
simmoan01	2014	132
simmoan01	2015	142
simmoan01	2016	126
simmoan01	2017	164
simmoan01	2018	162
simmoan01	2019	105
smithma05	2018	142
smithma05	2019	116
smithse01	2011	135
smithse01	2014	118
smoakju01	2011	100
smoakju01	2012	105
smoakju01	2013	108
smoakju01	2017	151
smoakju01	2018	122
solarya01	2015	142
solarya01	2016	116
solarya01	2017	119
solarya01	2018	106
solerjo01	2019	156
soriaal01	2010	128
soriaal01	2011	116
soriaal01	2012	147
sotoju01	2018	121
sotoju01	2019	153
souzast01	2016	106
souzast01	2017	125
spande01	2010	166
spande01	2012	146
spande01	2013	170
spande01	2014	184
spande01	2016	152
spande01	2017	135
spangco01	2017	117
springe01	2015	107
springe01	2016	168
springe01	2017	155
springe01	2018	144
springe01	2019	140
stantmi03	2011	135
stantmi03	2012	130
stantmi03	2013	106
stantmi03	2014	155
stantmi03	2017	168
stantmi03	2018	164
storytr01	2016	101
storytr01	2017	120
storytr01	2018	174
storytr01	2019	173
stubbdr01	2010	131
stubbdr01	2011	147
stubbdr01	2012	105
stubbdr01	2013	100
stubbdr01	2014	112
suareeu01	2015	104
suareeu01	2016	140
suareeu01	2017	139
suareeu01	2018	149
suareeu01	2019	156
suzukic01	2010	214
suzukic01	2011	184
suzukic01	2012	105
suzukic01	2013	136
suzukic01	2014	102
suzukku01	2010	120
suzukku01	2011	109
suzukku01	2014	130
suzukku01	2015	104
swansda01	2017	113
swansda01	2018	114
swansda01	2019	121
swishni01	2010	163
swishni01	2011	137
swishni01	2012	146
swishni01	2013	135
tabatjo01	2010	121
tapiara01	2019	117
tatisfe02	2019	106
tayloch03	2017	148
tayloch03	2018	136
taylomi02	2015	108
taylomi02	2017	108
teixema01	2010	154
teixema01	2011	146
teixema01	2012	113
teixema01	2015	100
tejadmi01	2010	108
tejadru01	2012	134
thameer01	2017	116
theriry01	2010	110
theriry01	2011	120
tomasya01	2015	111
tomasya01	2016	144
torrean02	2010	136
torregl01	2018	117
torregl01	2019	152
torreyo01	2011	108
travide01	2016	123
troutmi01	2012	182
troutmi01	2013	190
troutmi01	2014	173
troutmi01	2015	172
troutmi01	2016	173
troutmi01	2017	123
troutmi01	2018	147
troutmi01	2019	137
trumbma01	2011	137
trumbma01	2012	146
trumbma01	2013	145
trumbma01	2016	157
trumbma01	2017	131
tulowtr01	2010	148
tulowtr01	2011	162
tulowtr01	2013	139
tulowtr01	2014	107
tulowtr01	2016	125
turneju01	2011	113
turneju01	2015	113
turneju01	2016	153
turneju01	2017	147
turneju01	2018	114
turneju01	2019	139
turnetr01	2016	105
turnetr01	2017	117
turnetr01	2018	180
turnetr01	2019	155
ugglada01	2010	169
ugglada01	2011	140
ugglada01	2012	115
uptonbj01	2010	127
uptonbj01	2011	136
uptonbj01	2012	141
uptonbj01	2014	108
uptonju01	2010	135
uptonju01	2011	171
uptonju01	2012	155
uptonju01	2013	147
uptonju01	2014	153
uptonju01	2015	136
uptonju01	2016	140
uptonju01	2017	128
uptonju01	2018	137
uribeju01	2010	129
uribeju01	2013	108
uribeju01	2014	120
urshegi01	2019	139
utleych01	2010	117
utleych01	2011	103
utleych01	2013	135
utleych01	2014	159
utleych01	2016	129
valbulu01	2014	119
valenda01	2011	139
valenda01	2016	135
valenda01	2017	115
vazquch01	2019	133
venabwi01	2012	110
venabwi01	2013	129
verdual01	2019	101
vicieda01	2012	129
vicieda01	2013	117
vicieda01	2014	121
victosh01	2010	152
victosh01	2011	145
victosh01	2012	101
victosh01	2013	140
villajo01	2016	168
villajo01	2019	176
vogtst01	2015	116
vogtst01	2016	123
voitlu01	2019	113
vottojo01	2010	177
vottojo01	2011	185
vottojo01	2012	126
vottojo01	2013	177
vottojo01	2015	171
vottojo01	2016	181
vottojo01	2017	179
vottojo01	2018	143
vottojo01	2019	137
walkech02	2019	137
walkene01	2010	126
walkene01	2011	163
walkene01	2012	132
walkene01	2013	120
walkene01	2014	139
walkene01	2015	146
walkene01	2016	116
weeksje01	2011	123
weeksri01	2010	175
weeksri01	2011	122
weeksri01	2012	135
wellsve01	2010	161
wellsve01	2011	110
wendljo01	2018	146
werthja01	2010	164
werthja01	2011	130
werthja01	2013	147
werthja01	2014	156
werthja01	2016	128
wietema01	2010	111
wietema01	2011	131
wietema01	2012	131
wietema01	2013	123
wietema01	2016	103
wiggity01	2010	144
willijo03	2011	120
willijo03	2012	135
willini01	2018	104
wongko01	2014	100
wongko01	2015	146
wongko01	2017	101
wongko01	2019	136
wrighda03	2010	166
wrighda03	2012	178
wrighda03	2013	132
wrighda03	2014	144
yastrmi01	2019	101
yelicch01	2014	165
yelicch01	2015	143
yelicch01	2016	172
yelicch01	2017	170
yelicch01	2018	187
yelicch01	2019	161
youklke01	2010	111
youklke01	2011	111
youngch04	2010	150
youngch04	2011	134
youngde03	2010	170
youngde03	2012	153
youngmi02	2010	186
youngmi02	2011	213
youngmi02	2012	169
youngmi02	2013	129
zimmery01	2010	161
zimmery01	2011	114
zimmery01	2012	163
zimmery01	2013	156
zimmery01	2017	159
zobribe01	2010	129
zobribe01	2011	158
zobribe01	2012	151
zobribe01	2013	168
zobribe01	2014	155
zobribe01	2016	142
zobribe01	2017	101
zobribe01	2018	139

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

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

10 records

year	N
2010	176
2011	173
2012	170
2013	167
2014	174
2015	179
2016	179
2017	179
2018	175
2019	174

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.

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"

38 records

playerID	year	league	schoolID
altuvjo01	2017	AL	NA
bautijo02	2010	AL	flchipo
bautijo02	2011	AL	flchipo
bondsba01	2001	NL	arizonast
bondsba01	2002	NL	arizonast
bondsba01	2004	NL	arizonast
bryankr01	2016	NL	NA
cabremi01	2012	AL	NA
cabremi01	2013	AL	NA
delgaca01	2000	AL	NA
donaljo02	2015	AL	auburn
fieldpr01	2007	NL	NA
goldspa01	2013	NL	swtexas
harpebr03	2015	ML	NA
heltoto01	2000	NL	tennessee
howarry01	2006	NL	swmost
jeterde01	2006	AL	NA
jeterde01	2009	AL	NA
jonesan01	2005	NL	NA
kempma01	2011	NL	NA
ortizda01	2005	AL	NA
ortizda01	2016	AL	NA
poseybu01	2012	NL	floridast
pujolal01	2003	NL	momaple
pujolal01	2009	NL	momaple
ramirar01	2008	NL	NA
ramirma02	1999	AL	NA
ramirma02	2004	AL	NA
rodrial01	2001	AL	NA
rodrial01	2002	AL	NA
rodrial01	2003	AL	NA
rodrial01	2007	AL	NA
sosasa01	1999	NL	NA
stantmi03	2014	NL	NA
stantmi03	2017	NL	NA
troutmi01	2014	AL	NA
vottojo01	2010	NL	NA
youklke01	2008	AL	cincy

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.

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

3 records

schoolID	Hank_Aaron_Awards
arizonast	3
momaple	2
flchipo	2

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:

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.

--- Fill me in!

How many awards are available to be given?

Find all distinct awards in the awardplayers table

--- Fill me in!

Rookie of the Year

Finding all “Rookie of the Year Awards” in the awardplayers Table.

--- Fill me in!