Skip to content

Instantly share code, notes, and snippets.

@yllus

yllus/QUERIES.md

Last active May 9, 2016 15:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save yllus/2531d9dac43f591ec5d61ab6b579f464 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save yllus/2531d9dac43f591ec5d61ab6b579f464 to your computer and use it in GitHub Desktop.
Download ZIP
PowerPivot queries against CFL Connect
Raw
QUERIES.md

To get game-by-game Passing data:

SELECT Player.CFLCentralID, 
Game.Season AS season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGamePass.Attempts) AS pass_attempts, 
SUM(StatGamePass.Completions) AS pass_completions, 
SUM(StatGamePass.Yards) AS pass_net_yards, 
MAX(StatGamePass.Long) AS pass_long, 
SUM(StatGamePass.TDs) AS pass_touchdowns, 
MAX(StatGamePass.TDLong) AS pass_touchdowns_long, 
SUM(StatGamePass.Interceptions) AS pass_interceptions 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
JOIN StatGamePass ON StatGamePass.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID

To get game-by-game Rushing data:

SELECT Player.CFLCentralID, 
Game.Season AS season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGameRush.Attempts) AS rush_attempts, 
SUM(StatGameRush.Yards) AS rush_yards, 
MAX(StatGameRush.Long) AS rush_long, 
SUM(StatGameRush.TDs) AS rush_touchdowns, 
MAX(StatGameRush.TDLong) AS rush_touchdowns_long, 
SUM(StatGameRush.Min10) AS rush_min_10, 
SUM(StatGameRush.Min20) AS rush_min_20 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
JOIN StatGameRush ON StatGameRush.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID 
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID

To get game-by-game Receiving data:

SELECT Player.CFLCentralID,  
Game.Season AS season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGameReceive.Targets) AS receieve_attempts, 
SUM(StatGameReceive.Caught) AS receieve_caught, 
SUM(StatGameReceive.Yards) AS receieve_yards, 
MAX(StatGameReceive.Long) AS receieve_long, 
SUM(StatGameReceive.TDs) AS receieve_touchdowns, 
MAX(StatGameReceive.TDLong) AS receieve_touchdowns_long, 
SUM(StatGameReceive.SecondDownConversions) AS receieve_second_down_conversions, 
SUM(StatGameReceive.YardsAfterCatch) AS receieve_yards_after_catch, 
SUM(StatGameReceive.Min30) AS receieve_min_30 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
JOIN StatGameReceive ON StatGameReceive.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID 
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID

To get game-by-game Field Goal data:

SELECT Player.CFLCentralID, 
Game.Season as season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGameFieldGoal.[Field Goals]) AS field_goals_attempts, 
SUM(StatGameFieldGoal.Made) AS field_goals_made, 
MAX(StatGameFieldGoal.Long) AS field_goals_long, 
SUM(StatGameFieldGoal.Singles) AS field_goals_singles, 
SUM(StatGameFieldGoal.Blocked) AS field_goals_blocked, 
SUM(StatGameFieldGoal.Made0119) AS field_goals_made_01_19, 
SUM(StatGameFieldGoal.Made2029) AS field_goals_made_20_29, 
SUM(StatGameFieldGoal.Made3039) AS field_goals_made_30_39, 
SUM(StatGameFieldGoal.Made4049) AS field_goals_made_40_49, 
SUM(StatGameFieldGoal.Made50Plus) AS field_goals_made_50_plus, 
SUM(StatGameConvert.C1Attempts) AS extra_point_attempts, 
SUM(StatGameConvert.C1Made) AS extra_point_made, 
SUM(StatGameConvert.Yards) AS extra_point_yards 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
JOIN StatGameFieldGoal ON StatGameFieldGoal.GameRosterID = GameRoster.GameRosterID 
JOIN StatGameConvert ON StatGameConvert.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID

To get game-by-game Punts data:

SELECT Player.CFLCentralID,
Game.Season as season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGamePunt.Punts) AS punts, 
SUM(StatGamePunt.Yards) AS punts_yards, 
SUM(StatGamePunt.NetYards) AS punts_net_yards, 
MAX(StatGamePunt.Long) AS punts_long, 
SUM(StatGamePunt.Singles) AS punts_singles, 
SUM(StatGamePunt.Blocked) AS punts_blocked, 
SUM(StatGamePunt.In10) AS punts_in_10, 
SUM(StatGamePunt.In20) AS punts_in_20, 
SUM(StatGamePunt.Returned) AS punts_returned 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
JOIN StatGamePunt ON StatGamePunt.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID

To get game-by-game Defensive data:

SELECT Player.CFLCentralID, 
Game.Season as season, 
Game.GameID AS game_id, 
MAX(Game.GameTypeID) AS game_type_id, 
MAX(Game.GameDay) AS game_date, 
MAX(Game.WeekNum) AS week, 
MAX(HomeClub.Code) AS home_team_abbreviation, 
MAX(VisitorClub.Code) AS visitor_team_abbreviation,  
SUM(StatGameTackle.TotalTackles) AS tackles_total, 
SUM(StatGameTackle.Yards) AS tackles_yards, 
SUM(StatGameTackle.Defensive) AS tackles_defensive, 
SUM(StatGameTackle.[Special Teams]) AS tackles_specialteams, 
SUM(StatGameTackle.[Tackles For Loss]) AS tackles_for_loss, 
SUM(StatGameSackMade.SacksMade) AS sacks_qb_made, 
SUM(StatGameSackMade.Yards) AS sacks_qb_yards, 
SUM(StatGameInterception.Interceptions) AS interceptions_made, 
SUM(StatGameInterception.Yards) AS interceptions_yards, 
MAX(StatGameInterception.Long) AS interceptions_long, 
SUM(StatGameInterception.TDs) AS interceptions_touchdowns, 
MAX(StatGameInterception.TDLong) AS interceptions_touchdowns_long, 
SUM(StatGameFumblesForced.FumblesForced) AS fumbles_forced, 
SUM(StatGameFumbleReturn.FumbleReturns) AS fumble_returns, 
SUM(StatGameFumbleReturn.Yards) AS fumble_returns_yards, 
MAX(StatGameFumbleReturn.Long) AS fumble_returns_long, 
SUM(StatGameFumbleReturn.TDs) AS fumble_returns_touchdowns, 
MAX(StatGameFumbleReturn.TDLong) AS fumble_returns_touchdowns_long, 
SUM(StatGamePassKnockDown.PassesKnockedDown) AS passes_knocked_down 
FROM Player 
JOIN GameRoster ON GameRoster.PlayerID = Player.PlayerID 
LEFT JOIN StatGameTackle ON StatGameTackle.GameRosterID = GameRoster.GameRosterID 
LEFT JOIN StatGameSackMade ON StatGameSackMade.GameRosterID = GameRoster.GameRosterID 
LEFT JOIN StatGameInterception ON StatGameInterception.GameRosterID = GameRoster.GameRosterID 
LEFT JOIN StatGameFumblesForced ON StatGameFumblesForced.GameRosterID = GameRoster.GameRosterID 
LEFT JOIN StatGameFumbleReturn ON StatGameFumbleReturn.GameRosterID = GameRoster.GameRosterID 
LEFT JOIN StatGamePassKnockDown ON StatGamePassKnockDown.GameRosterID = GameRoster.GameRosterID 
JOIN Game ON Game.GameID = GameRoster.GameID 
JOIN Club ON Club.ClubID = GameRoster.ClubID 
JOIN Club HomeClub ON HomeClub.ClubID = Game.HomeClubID 
JOIN Club VisitorClub ON VisitorClub.ClubID = Game.VisitorClubID 
WHERE 1=1 
GROUP BY Player.CFLCentralID, Game.GameID, Game.Season, Club.Code 
ORDER BY Game.GameID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment