# 12 variables x 159,610 # Primary Key is a combination of (PlayerID,GameID,Start_Second) # Dimensional Analysis: Game, Half, Player, Action, Duration # # Single Dimensions # GroupBY Game (11 rows) select GameID, count(*) from action_dataset group by GameID # GroupBy Player (35 rows) # Effectively provides a list of actions for each player across all games select playerid, count(*) from action_dataset group by playerid order by count(*) DESC # GroupBy Action (6 rows) select action, count(*) from action_dataset group by action order by count(*) DESC # GroupBy Duration (6 rows) select duration, count(*) AS Duration_Sum from action_dataset where duration >= 5 group by duration having duration_sum > 10 order by duration_sum desc # Dimensional Analysis: Game, Half, Player, Action, Speed # # Multiple Dimensions # List every action on a game/player basis (1166 rows) select gameid, playerid, action, count(*) from action_dataset group by gameid, playerid, action order by count(*) DESC # Same query for high intensity actions and eliminate players with less than 5 of these actions (138 rows) select gameid, playerid, action, count(*) from action_dataset where action = 'sprint' or action = 'high intensity' group by gameid, playerid, action having count(*) >= 5 order by count(*) DESC # Same query but drop the game dimension so that all games are agregated (29 rows) select playerid, action, count(*) from action_dataset where action = 'sprint' or action = 'high intensity' group by playerid, action having count(*) >= 5 order by count(*) DESC