CREATE EXTERNAL TABLE Games (gid INT, gname STRING, pubname STRING, releaseDate STRING, rating DOUBLE)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\011'
LOCATION '/user/rlawrenc/416/lab4/games';

CREATE EXTERNAL TABLE Players (pid INT, fname STRING, lname STRING, bdate STRING, gender STRING, imagePath STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\011'
LOCATION '/user/rlawrenc/416/lab4/players';

CREATE EXTERNAL TABLE PlayerGames (pid INT, gid INT, score INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\011'
LOCATION '/user/rlawrenc/416/lab4/playergames';

To output:
INSERT OVERWRITE LOCAL DIRECTORY 'q1'

/* Q1 - List All Games */
SELECT * FROM Games;


/* Q2 - Find a Game by Id */
SELECT * FROM Games WHERE gid = 45;


/* Q3 - Players over 18 */
SELECT pid, fname, lname, bdate, year(from_unixtime(unix_timestamp())) - year(bdate) 
- if (month(from_unixtime(unix_timestamp())) < month(bdate) 
OR (month(from_unixtime(unix_timestamp())) = month(bdate) AND day(from_unixtime(unix_timestamp())) < day(bdate)), 1, 0) as age  FROM Players 
WHERE 
year(from_unixtime(unix_timestamp())) - year(bdate) 
- if (month(from_unixtime(unix_timestamp())) < month(bdate) 
OR (month(from_unixtime(unix_timestamp())) = month(bdate) AND day(from_unixtime(unix_timestamp())) < day(bdate)), 1, 0) >= 18
ORDER BY age ASC;


/* Q4 - Number of Players per Game */
SELECT gid, COUNT(pid) FROM PlayerGames GROUP BY gid;


/* Q5 - List the Top 10 Scores for a Game */
SELECT score FROM PlayerGames WHERE gid = 45 ORDER BY score DESC LIMIT 10;


/* Q6 - Number of Players Two Games have in Common */
SELECT G1.gid, G2.gid, COUNT(G1.pid) FROM PlayerGames G1 INNER JOIN PlayerGames G2 ON G1.pid = G2.pid WHERE G1.gid <> G2.gid GROUP BY G1.gid, G2.gid;


/* Q7 - Count Top Players for Each Game */
SELECT G.gid, G.gname, COUNT(pid) FROM Games G LEFT OUTER JOIN PlayerGames PG ON G.gid = PG.gid AND PG.score > 98000 GROUP BY G.gid, G.gname;


/* Q8 - List good players or play 'Electronic Arts' games */
/* Note: Check to make sure 998 player does not appear. */
SELECT P.pid, P.fname, P.lname FROM 
(SELECT DISTINCT pid FROM Games G INNER JOIN PlayerGames PG ON G.gid = PG.gid WHERE G.pubname = 'Electronic Arts' OR score > 90000) P2 INNER JOIN Players P
ON P2.pid = P.pid; 


/* Q9 - Highest Number of Players for each gender of any games for each Publisher */
SELECT pubName, gender, MAX(cnt)
FROM Games G INNER JOIN (SELECT gid, gender, count(PG.pid) as cnt FROM Players P INNER JOIN PlayerGames PG ON P.pid = PG.pid  GROUP BY gid, gender) PG
ON G.gid = PG.gid
GROUP BY pubName, gender;


/* Q10 - Game Breakdown by Gender */
/* Determine player counts by game and gender */
SELECT PG1.gid, PG1.cnt+PG2.cnt, PG1.gender, PG1.cnt, PG1.cnt/(PG1.cnt+PG2.cnt), PG2.gender, PG2.cnt, PG2.cnt/(PG1.cnt+PG2.cnt)
FROM (SELECT gid, gender, count(PG.pid) as cnt FROM Players P INNER JOIN PlayerGames PG ON P.pid = PG.pid  GROUP BY gid, gender) PG1
INNER JOIN 
(SELECT gid, gender, count(PG.pid) as cnt FROM Players P INNER JOIN PlayerGames PG ON P.pid = PG.pid  GROUP BY gid, gender) PG2
ON PG1.gid = PG2.gid
WHERE PG1.gender = 'female' and PG2.gender == 'male';