# 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