View Definitions
This article contains the SQL queries that creates all of the views in the AFL Warehouse. They are presented here because they are Snowflake 'secure views' which prevents consumers accessing the definitions via a data share.
Why? Some views contain licensing mechanisms to ensure that unlicensed data and Champion Data IP content is protected.
The view definitions are presented here for your own use to modify and create in your databases.
Publish Date: 03/03/2026​
The view definitions published on this date were compatible with the warehouse at the time. Please contact Champion Data if you are experiencing issues successfully running them.
FIXTURES Views​
FIXTURES.MATCHES BY SQUAD​
This view reproduces the FIXTURES.MATCHES table with two rows per match - one for the home squad, the other for the away squad. This allows you to collect match details for any squad without having to know if they were the home or away team for that match. Each row also presents the opposite squad as the 'OPPONENT'.
BYE matches are not duplicated (no opponent!) so they only get one row in this view
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.MATCHES_BY_SQUAD(
LEAGUE_ID,
LEVEL_ID,
COMPETITION_ID,
SEASON_ID,
ROUND_CODE,
ROUND_NAME,
ROUND_PHASENUMBER,
ROUND_NUMBER,
MATCH_ID,
MATCH_NAME,
MATCH_CODE,
ROUND_ORDER,
MATCH_TYPE_ID,
MATCH_TYPE_CODE,
MATCH_START,
MATCH_STARTDATE_UTC,
MATCH_STATUS,
MATCH_STATUS_CODE,
MATCH_TYPE,
SQUAD_ID,
SQUAD_CODE,
SQUAD_ISHOME,
SQUAD_RESULT,
SQUAD_TRAVEL,
OPPONENT_ID,
OPPONENT_CODE,
OPPONENT_TRAVEL,
VENUE_ID,
VENUE_CODE,
VENUE_LENGTH,
VENUE_WIDTH,
DATE_STATISTICS_UPDATED
) AS (
SELECT
LEAGUE_ID,
LEVEL_ID,
COMPETITION_ID,
SEASON_ID,
ROUND_CODE,
ROUND_NAME,
ROUND_PHASENUMBER,
ROUND_NUMBER,
MATCH_ID,
MATCH_NAME,
MATCH_CODE,
ROUND_ORDER,
MATCH_TYPE_ID,
MATCH_TYPE_CODE,
MATCH_START,
MATCH_STARTDATE_UTC,
MATCH_STATUS,
MATCH_STATUS_CODE,
MATCH_TYPE,
mt.HOME_SQUAD_ID AS SQUAD_ID,
mt.HOME_SQUAD_CODE AS SQUAD_CODE,
TRUE AS SQUAD_ISHOME,
mt.HOME_SQUAD_RESULT AS SQUAD_RESULT,
mt.HOME_SQUAD_TRAVEL AS SQUAD_TRAVEL,
mt.AWAY_SQUAD_ID AS OPPONENT_ID,
mt.AWAY_SQUAD_CODE AS OPPONENT_CODE,
mt.AWAY_SQUAD_TRAVEL AS OPPONENT_TRAVEL,
VENUE_ID,
VENUE_CODE,
VENUE_LENGTH,
VENUE_WIDTH,
DATE_STATISTICS_UPDATED
FROM ADQ.FIXTURES.MATCHES mt
UNION
SELECT
LEAGUE_ID,
LEVEL_ID,
COMPETITION_ID,
SEASON_ID,
ROUND_CODE,
ROUND_NAME,
ROUND_PHASENUMBER,
ROUND_NUMBER,
MATCH_ID,
MATCH_NAME,
MATCH_CODE,
ROUND_ORDER,
MATCH_TYPE_ID,
MATCH_TYPE_CODE,
MATCH_START,
MATCH_STARTDATE_UTC,
MATCH_STATUS,
MATCH_STATUS_CODE,
MATCH_TYPE,
mt.AWAY_SQUAD_ID AS SQUAD_ID,
mt.AWAY_SQUAD_CODE AS SQUAD_CODE,
FALSE AS SQUAD_ISHOME,
mt.AWAY_SQUAD_RESULT AS SQUAD_RESULT,
mt.AWAY_SQUAD_TRAVEL AS SQUAD_TRAVEL,
mt.HOME_SQUAD_ID AS OPPONENT_ID,
mt.HOME_SQUAD_CODE AS OPPONENT_CODE,
mt.HOME_SQUAD_TRAVEL AS OPPONENT_TRAVEL,
VENUE_ID,
VENUE_CODE,
VENUE_LENGTH,
VENUE_WIDTH,
DATE_STATISTICS_UPDATED
FROM <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES mt
WHERE mt.MATCH_TYPE_CODE != 'BYE'
)
STATISTICS Player Stats Views​
STATISTICS.PLAYER STATS BY MATCH​
This view returns each player's stats for a whole match, where the MATCH_PLAYER_STATS table breaks them down to period, zone and pre/post clearance. It also joins to the FIXTURES.MATCHES_BY_SQUAD view to obtain the Season Id column for ease of reference.
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.PLAYER_STATS_BY_MATCH(
LEAGUE_ID,
LEVEL_ID,
MATCH_ID,
SEASON_ID,
SQUAD_ID,
SQUAD_CODE,
PLAYER_ID,
PLAYER_DISPLAYNAME,
METRIC,
TOTAL
) AS (
SELECT
PS.LEAGUE_ID,
PS.LEVEL_ID,
PS.MATCH_ID,
MT.SEASON_ID,
PS.SQUAD_ID,
PS.SQUAD_CODE,
PS.PLAYER_ID,
PS.PLAYER_DISPLAYNAME,
PS.METRIC,
SUM(TOTAL) AS TOTAL
FROM <AFL_WAREHOUSE_DB_NAME>.STATISTICS.MATCH_PLAYER_STATS PS
JOIN <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES_BY_SQUAD MT
ON PS.MATCH_ID = MT.MATCH_ID
AND PS.SQUAD_ID = MT.SQUAD_ID
GROUP BY
PS.LEAGUE_ID,
PS.LEVEL_ID,
PS.MATCH_ID,
MT.SEASON_ID,
PS.SQUAD_ID,
PS.SQUAD_CODE,
PS.PLAYER_ID,
PS.PLAYER_DISPLAYNAME,
PS.METRIC
)
STATISTICS.PLAYER STATS BY MATCH PERIOD ZONE​
This view returns each player's stats for a match broken down by period and (logical) zone and is a good starting point to generate a match summary report.
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.PLAYER_STATS_BY_MATCH_PERIOD_ZONE(
LEAGUE_ID,
LEVEL_ID,
MATCH_ID,
SEASON_ID,
SQUAD_ID,
SQUAD_CODE,
PLAYER_ID,
PLAYER_DISPLAYNAME,
PERIOD,
ZONE_LOGICAL,
METRIC,
TOTAL
) AS
SELECT
PS.LEAGUE_ID,
PS.LEVEL_ID,
PS.MATCH_ID,
MT.SEASON_ID,
PS.SQUAD_ID,
PS.SQUAD_CODE,
PS.PLAYER_ID,
PS.PLAYER_DISPLAYNAME,
PS.PERIOD,
PS.ZONE_LOGICAL,
PS.METRIC,
SUM(TOTAL) AS TOTAL
FROM <AFL_WAREHOUSE_DB_NAME>.STATISTICS.MATCH_PLAYER_STATS PS
JOIN <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES_BY_SQUAD MT
ON PS.MATCH_ID = MT.MATCH_ID
AND PS.SQUAD_ID = MT.SQUAD_ID
GROUP BY
PS.LEAGUE_ID,
PS.LEVEL_ID,
PS.MATCH_ID,
MT.SEASON_ID,
PS.SQUAD_ID,
PS.SQUAD_CODE,
PS.PLAYER_ID,
PS.PLAYER_DISPLAYNAME,
PS.PERIOD,
PS.ZONE_LOGICAL,
PS.METRIC;
STATISTICS.PLAYER STATS BY SEASON​
This view returns each player's stats for a season. This includes the number of matches played by each player and a match average in addition to the season total.
These stats include finals matches but exclude pre-season matches (being in their own league).
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.PLAYER_STATS_BY_SEASON(
SEASON_ID,
PLAYER_ID,
LEAGUE_ID,
LEVEL_ID,
SQUAD_ID,
SQUAD_CODE,
PLAYER_DISPLAYNAME,
METRIC,
TOTAL,
MATCHES_PLAYED,
AVG_MATCH)
AS (
SELECT *,
A.TOTAL/B.MATCHES_PLAYED AS AVG_MATCH
FROM (
SELECT LEAGUE_ID,
LEVEL_ID,
SEASON_ID,
SQUAD_ID,
SQUAD_CODE,
PLAYER_ID,
PLAYER_DISPLAYNAME,
METRIC,
SUM(TOTAL) AS TOTAL
FROM <AFL_WAREHOUSE_DB_NAME>.STATISTICS.PLAYER_STATS_BY_MATCH
GROUP BY
LEAGUE_ID,
LEVEL_ID,
SEASON_ID,
SQUAD_ID,
SQUAD_CODE,
PLAYER_ID,
PLAYER_DISPLAYNAME,
METRIC
) A
LEFT JOIN (
SELECT MT.SEASON_ID,
MP.PERSON_ID AS PLAYER_ID,
COALESCE(COUNT(DISTINCT MT.MATCH_ID),0) AS MATCHES_PLAYED
FROM <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCH_PERSONS MP
JOIN <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES MT
ON MT.MATCH_ID = MP.MATCH_ID
GROUP BY 1,2
) B
USING (SEASON_ID, PLAYER_ID)
)
STATISTICS Squad Stats Views​
STATISTICS.SQUAD STATS BY MATCH​
This view returns each squad's stats for a whole match, where the MATCH_SQUAD_STATS table breaks them down to period, zone and pre/post clearance. It also joins to the FIXTURES.MATCHES_BY_SQUAD view to obtain the Season Id column for ease of reference.
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.SQUAD_STATS_BY_MATCH(
SEASON_ID,
MATCH_ID,
SQUAD_ID,
LEAGUE_ID,
LEVEL_ID,
SQUAD_CODE,
METRIC,
TOTAL
)
AS (
SELECT
MT.SEASON_ID,
MS.MATCH_ID,
MS.SQUAD_ID,
MS.LEAGUE_ID,
MS.LEVEL_ID,
MS.SQUAD_CODE,
MS.METRIC,
SUM(MS.TOTAL) AS TOTAL
FROM <AFL_WAREHOUSE_DB_NAME>.STATISTICS.MATCH_SQUAD_STATS MS
JOIN <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES_BY_SQUAD MT
ON MT.MATCH_ID = MS.MATCH_ID
AND MS.SQUAD_ID = MT.SQUAD_ID
GROUP BY
MT.SEASON_ID,
MS.MATCH_ID,
MS.SQUAD_ID,
MS.LEAGUE_ID,
MS.LEVEL_ID,
MS.SQUAD_CODE,
MS.METRIC
)
STATISTICS.MATCH SQUAD STATS DIFF LOGICAL​
This view produces squad stats for every match, but combines with their opponent's stat for the same metric, period and logical zone so the differential can be calculated.
CREATE OR REPLACE VIEW <YOUR_DB_NAME>.PUBLIC.MATCH_SQUAD_STATS_DIFF_LOGICAL(
SQUAD_ID,
SQUAD_CODE,
OPP_SQUAD_ID,
OPP_SQUAD_CODE,
LEVEL_ID,
LEAGUE_ID,
METRIC,
MATCH_ID,
PERIOD,
ZONE_LOGICAL,
TOTAL_FOR,
TOTAL_AGAINST,
TOTAL_DIFF
)
AS (
WITH STATS AS (
WITH PREAGG AS
(
SELECT MSS.LEAGUE_ID,
MSS.LEVEL_ID,
MSS.SQUAD_ID,
MT.OPPONENT_ID,
MSS.MATCH_ID,
MSS.PERIOD,
MSS.ZONE_LOGICAL,
MSS.METRIC,
SUM(MSS.TOTAL) AS TOTAL
FROM <AFL_WAREHOUSE_DB_NAME>.STATISTICS.MATCH_SQUAD_STATS MSS
JOIN <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES_BY_SQUAD MT
ON MT.MATCH_ID = MSS.MATCH_ID
AND MT.SQUAD_ID = MSS.SQUAD_ID
GROUP BY MSS.LEAGUE_ID,
MSS.LEVEL_ID,
MSS.SQUAD_ID,
MT.OPPONENT_ID,
MSS.MATCH_ID,
MSS.PERIOD,
MSS.ZONE_LOGICAL,
MSS.METRIC
),
FORAGAINST AS
(
SELECT * EXCLUDE(SQUAD_ID,OPPONENT_ID),
'FOR' AS STATUS,
SQUAD_ID,
OPPONENT_ID
FROM PREAGG
UNION ALL
SELECT * EXCLUDE(SQUAD_ID,OPPONENT_ID),
'AGAINST' AS STATUS,
OPPONENT_ID AS SQUAD_ID,
SQUAD_ID AS OPPONENT_ID
FROM PREAGG
)
SELECT LEAGUE_ID,
SQUAD_ID,
OPPONENT_ID,
METRIC,
MATCH_ID,
PERIOD,
ZONE_LOGICAL,
COALESCE("'FOR'",0) AS "TOTAL_FOR",
COALESCE("'AGAINST'",0) AS "TOTAL_AGAINST",
COALESCE("'FOR'",0) - COALESCE("'AGAINST'",0) AS "TOTAL_DIFF"
FROM FORAGAINST
PIVOT (SUM(TOTAL) FOR STATUS IN ('FOR','AGAINST'))
),
SQUADS AS (
SELECT DISTINCT SQUAD_ID,
SQUAD_CODE,
LEVEL_ID
FROM <AFL_WAREHOUSE_DB_NAME>.FIXTURES.MATCHES_BY_SQUAD
)
SELECT S.SQUAD_ID,
S.SQUAD_CODE,
OS.SQUAD_ID AS OPP_SQUAD_ID,
OS.SQUAD_CODE AS OPP_SQUAD_CODE,
OS.LEVEL_ID,
STATS.* EXCLUDE (SQUAD_ID, OPPONENT_ID)
FROM STATS
LEFT JOIN SQUADS S
ON STATS.SQUAD_ID = S.SQUAD_ID
LEFT JOIN SQUADS OS
ON STATS.OPPONENT_ID = OS.SQUAD_ID
WHERE COALESCE(LENGTH(STATS.ZONE_LOGICAL), 0) > 0
)
More Coming Soon!