AFL Warehouse Training Guide

Data Dictionary

Metric Set

The queries in this training guide return the following metrics, being a set of "general" player performance metrics.

Metric	Metric	Metric	Metric
DISPOSAL	KICK	KICK_EFFECTIVE	HANDBALL
METRES_GAINED	INSIDE_50	REBOUND_50	POSSESSION_CONTESTED
POSSESSION_UNCONTESTED	GROUNDBALL_GET	MARK	MARK_CONTESTED
MARK_UNCONTESTED

The Warehouse also contains a REFERENCE.GLOSSARY table which lists all the metrics with their descriptions. This will be expanded on next year to match the detail of the AFL API's metric glossary, crucially including each metric's formula where they are generated as a product of other metrics.

Squad Id Lookup

The Warehouse has a REFERENCE.SQUADS table that lists all the squads. For ease of reference while using the queries, the squad Ids are listed here. Where a query is filtering by squad, feel free to change the squad id to your own club or another.

Squad	Id	Squad	Id
Adelaide Crows	10	Brisbane Lions	20
Carlton	30	Collingwood	40
Essendon	50	Fremantle	60
GWS GIANTS	1010	Geelong Cats	70
Gold Coast SUNS	1000	Hawthorn	80
Melbourne	90	North Melbourne	100
Port Adelaide	110	Richmond	120
St Kilda	130	Sydney Swans	160
West Coast Eagles	150	Western Bulldogs	140

Query 1a - Consolidating Match Dimensions to Zone and Period

This query consolidates all of the available dimensions down to Logical Zone and Period, returning for a player their stats attained in each combination of zone and period.

note

If a player didn't perform any of these metrics in a zone during a quarter, then they will have no rows returned for that combination.

Query 1a

/***********************************************************
Query 1a - Consolidating Match Dimensions to Zone and Period
***********************************************************/
SELECT
    S.MATCH_ID,
    
    --  Player
    S.PLAYER_ID,
    P.PLAYERFIRSTNAME || ' ' || P.PLAYERSURNAME AS PLAYER_NAME,
    
    --  Zone and Period dimensions
    S.PERIOD,
    S.ZONE_LOGICAL,
    
    --  Metric and aggregated stats for the zone & period combination
    S.METRIC,
    SUM(S.TOTAL) AS VALUE
FROM
    SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S 
    --  Join to get player name
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS AS P ON S.PLAYER_ID = P.PLAYER_ID
WHERE
    --  League filter
    S.LEAGUE_ID = 1
    
    --  Squad filter
    AND S.SQUAD_ID = 1000
    
    --  Metric Set “General”
    AND S.METRIC IN ('DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 
    
--  Grouping by retained dimensions & context
GROUP BY
    S.LEAGUE_ID,
    S.MATCH_ID,
    S.PLAYER_ID,
    PLAYER_NAME,
    S.ZONE_LOGICAL,
    S.PERIOD,
    S.METRIC
 
--  Sorting for readability
ORDER BY
    S.MATCH_ID,
    PLAYER_NAME,
    S.PERIOD,
    S.ZONE_LOGICAL,
    S.METRIC;

Query 1b - Pivoting Query 1a displaying player totals

This query produces the same results as Query 1a, but pivots it so each player receives one row per zone, period and match, and each metric is returned as a column. This makes it easier to export or display in a report as a single table.

Query 1b

/****************************************************
Query 1b: Pivot of Query 1a - Player Totals per Match
*****************************************************/
SELECT
    S.MATCH_ID,
    S.PLAYER_ID,
    P.PLAYERFIRSTNAME || ' ' || P.PLAYERSURNAME AS PLAYER_NAME,
    S.PERIOD,
    S.ZONE_LOGICAL,
    
    --  Pivot: Metrics as columns (totals per dimension combination)
    SUM(CASE WHEN S.METRIC = 'DISPOSAL'               THEN S.TOTAL ELSE 0 END) AS DISPOSAL,
    SUM(CASE WHEN S.METRIC = 'KICK'                   THEN S.TOTAL ELSE 0 END) AS KICK,
    SUM(CASE WHEN S.METRIC = 'HANDBALL'               THEN S.TOTAL ELSE 0 END) AS HANDBALL,
    SUM(CASE WHEN S.METRIC = 'KICK_EFFECTIVE'         THEN S.TOTAL ELSE 0 END) AS KICK_EFFECTIVE,
    SUM(CASE WHEN S.METRIC = 'METRES_GAINED'          THEN S.TOTAL ELSE 0 END) AS METRES_GAINED,
    SUM(CASE WHEN S.METRIC = 'INSIDE_50'              THEN S.TOTAL ELSE 0 END) AS INSIDE_50,
    SUM(CASE WHEN S.METRIC = 'REBOUND_50'             THEN S.TOTAL ELSE 0 END) AS REBOUND_50,
    SUM(CASE WHEN S.METRIC = 'POSSESSION_CONTESTED'   THEN S.TOTAL ELSE 0 END) AS POSSESSION_CONTESTED,
    SUM(CASE WHEN S.METRIC = 'POSSESSION_UNCONTESTED' THEN S.TOTAL ELSE 0 END) AS POSSESSION_UNCONTESTED,
    SUM(CASE WHEN S.METRIC = 'GROUNDBALL_GET'         THEN S.TOTAL ELSE 0 END) AS GROUNDBALL_GET,
    SUM(CASE WHEN S.METRIC = 'MARK'                   THEN S.TOTAL ELSE 0 END) AS MARK,
    SUM(CASE WHEN S.METRIC = 'MARK_CONTESTED'         THEN S.TOTAL ELSE 0 END) AS MARK_CONTESTED,
    SUM(CASE WHEN S.METRIC = 'MARK_UNCONTESTED'       THEN S.TOTAL ELSE 0 END) AS MARK_UNCONTESTED
FROM
    SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS AS P ON S.PLAYER_ID = P.PLAYER_ID
WHERE
    S.LEAGUE_ID = 1
    AND S.SQUAD_ID = 1000   
    AND S.METRIC IN (
        'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 
GROUP BY
    S.MATCH_ID,
    S.PLAYER_ID,
    P.PLAYERFIRSTNAME,
    P.PLAYERSURNAME,
    S.ZONE_LOGICAL,
    S.PERIOD
ORDER BY
    S.MATCH_ID,
    PLAYER_NAME,
    S.PERIOD,
    S.ZONE_LOGICAL;

Query 2a - Forward & Defensive Half Statistics

This query rolls up the F50 & AM zones into 'FH' (Forward Half), and D50 & DM into 'DH' (Defensive Half), providing match stats by period and half.

Query 2a

/*********************************************
Query 2a - Forward & Defensive Half Statistics
*********************************************/

    SELECT
        S.MATCH_ID,       
        S.SQUAD_ID,             
        MT.SQUADCODE AS SQUAD_CODE,
        S.PLAYER_ID,           
        P.PLAYERFIRSTNAME || ' ' || P.PLAYERSURNAME AS PLAYER_NAME,
        S.PERIOD,              
        
        --  Zones rolled up into to halves 
        CASE
            WHEN S.ZONE_LOGICAL IN ('D50','DM') THEN 'DH'
            WHEN S.ZONE_LOGICAL IN ('F50','AM') THEN 'FH'  
            ELSE NULL
        END AS HALF_LOGICAL,  

        S.METRIC,             
        SUM(S.TOTAL) AS VALUE,
        MT.OPPONENTCODE AS OPPONENT
    FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS AS P
      ON P.PLAYER_ID = S.PLAYER_ID
    JOIN SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES AS MT
      ON MT.MATCH_ID = S.MATCH_ID
     AND MT.SQUAD_ID = S.SQUAD_ID    
    WHERE S.LEAGUE_ID = 1
    AND S.METRIC IN (
   'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 

    -- The Centre Bounce Zone is intentionally omitted from this query
    AND S.ZONE_LOGICAL IN ('D50','DM','F50','AM')

    GROUP BY
        S.SQUAD_ID,
        MT.SQUADCODE,
        MT.OPPONENTCODE,
        S.MATCH_ID,
        HALF_LOGICAL,
        S.PLAYER_ID,
        P.PLAYERFIRSTNAME,
        P.PLAYERSURNAME,
        S.PERIOD,
        S.METRIC
 
 ORDER BY MATCH_ID, SQUAD_CODE, PLAYER_NAME, PERIOD,  METRIC, HALF_LOGICAL;

Query 2b - Pivot Query 2a so metrics are columns

Similar to Query 1b, this query pivots the Half dimension results to display the metrics as columns.

Query 2b

/************************************
Query 2b - Pivot Query 2a so metrics are columns
************************************/
SELECT
    S.MATCH_ID,
    S.PERIOD,    
    S.SQUAD_ID,
    MT.SQUADCODE,
    S.PLAYER_ID,
    P.PLAYERFIRSTNAME || ' ' || P.PLAYERSURNAME AS PLAYER_NAME,
    CASE
        WHEN S.ZONE_LOGICAL IN ('D50', 'DM') THEN 'DH'
        WHEN S.ZONE_LOGICAL IN ('F50', 'AM') THEN 'FH'
    END AS HALF_LOGICAL,
    
    --  ===== Pivot: Metric Set “General” into Columns =====
    SUM(CASE WHEN S.METRIC = 'DISPOSAL'               THEN S.TOTAL ELSE 0 END) AS DISPOSAL,
    SUM(CASE WHEN S.METRIC = 'KICK'                   THEN S.TOTAL ELSE 0 END) AS KICK,
    SUM(CASE WHEN S.METRIC = 'HANDBALL'               THEN S.TOTAL ELSE 0 END) AS HANDBALL,
    SUM(CASE WHEN S.METRIC = 'KICK_EFFECTIVE'         THEN S.TOTAL ELSE 0 END) AS KICK_EFFECTIVE,
    SUM(CASE WHEN S.METRIC = 'METRES_GAINED'          THEN S.TOTAL ELSE 0 END) AS METRES_GAINED,
    SUM(CASE WHEN S.METRIC = 'INSIDE_50'              THEN S.TOTAL ELSE 0 END) AS INSIDE_50,
    SUM(CASE WHEN S.METRIC = 'REBOUND_50'             THEN S.TOTAL ELSE 0 END) AS REBOUND_50,
    SUM(CASE WHEN S.METRIC = 'POSSESSION_CONTESTED'   THEN S.TOTAL ELSE 0 END) AS POSSESSION_CONTESTED,
    SUM(CASE WHEN S.METRIC = 'POSSESSION_UNCONTESTED' THEN S.TOTAL ELSE 0 END) AS POSSESSION_UNCONTESTED,
    SUM(CASE WHEN S.METRIC = 'GROUNDBALL_GET'         THEN S.TOTAL ELSE 0 END) AS GROUNDBALL_GET,
    SUM(CASE WHEN S.METRIC = 'MARK'                   THEN S.TOTAL ELSE 0 END) AS MARK,
    SUM(CASE WHEN S.METRIC = 'MARK_CONTESTED'         THEN S.TOTAL ELSE 0 END) AS MARK_CONTESTED,
    SUM(CASE WHEN S.METRIC = 'MARK_UNCONTESTED'       THEN S.TOTAL ELSE 0 END) AS MARK_UNCONTESTED
FROM
    SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
    JOIN SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES AS MT
      ON MT.MATCH_ID = S.MATCH_ID
     AND MT.SQUAD_ID = S.SQUAD_ID
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS AS P
      ON P.PLAYER_ID = S.PLAYER_ID
WHERE
    S.LEAGUE_ID = 1
    AND S.ZONE_LOGICAL IN ('D50', 'DM', 'F50', 'AM') 
    
    --  Metric Set “General”
    AND S.METRIC IN ('DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 

GROUP BY
    S.SQUAD_ID,
    SQUADCODE,
    S.MATCH_ID,
    HALF_LOGICAL,
    S.PERIOD,
    S.PLAYER_ID,
    PLAYER_NAME
ORDER BY
    S.MATCH_ID,
    S.PERIOD,
    PLAYER_NAME;

Query 3 - Season Totals for Players

This query obtains the season stat totals for all the players in a given squad. They are returned without any dimensions so cover the activity over a whole match played in each season. The FIXTURES.PLAYER_PARTICIPATION table is also used to count up how many matches each player played in every season to drive the Matches Played column.

Query 3

/**********************************
Query 3 - Season Totals for Players
**********************************/

WITH match_player_totals AS (
    SELECT
        pp.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.METRIC,
        SUM(s.TOTAL) AS VALUE
    FROM
        SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS s
        JOIN SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp ON pp.MATCH_ID = s.MATCH_ID
        AND pp.PLAYER_ID = s.PLAYER_ID
        AND pp.SQUAD_ID = s.SQUAD_ID
    WHERE
        s.LEAGUE_ID = 1 
        AND s.SQUAD_ID = 1000 
        AND S.METRIC IN ('DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 
      
    GROUP BY
        pp.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.METRIC
),
matches_played AS (
    SELECT
        pp.SEASON_ID,
        pp.SQUAD_ID,
        pp.PLAYER_ID,
        COUNT(DISTINCT CASE WHEN pp.MATCH_PLAYED THEN pp.MATCH_ID END) AS MATCHES_PLAYED
    FROM
        SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp
    WHERE
        pp.SQUAD_ID = 1000
    GROUP BY
        pp.SEASON_ID,
        pp.SQUAD_ID,
        pp.PLAYER_ID
)

SELECT
    mt.SEASON_ID,
    mt.PLAYER_ID,
    ppref.PLAYERFIRSTNAME || ' ' || ppref.PLAYERSURNAME AS PLAYER_NAME,
    COALESCE(mp.MATCHES_PLAYED, 0) AS MATCHES_PLAYED,
    mt.METRIC,
    mt.VALUE
FROM
    match_player_totals mt
    JOIN matches_played mp ON mp.SEASON_ID = mt.SEASON_ID
    AND mp.SQUAD_ID = mt.SQUAD_ID
    AND mp.PLAYER_ID = mt.PLAYER_ID
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS ppref ON ppref.PLAYER_ID = mt.PLAYER_ID
ORDER BY
    mt.SEASON_ID,
    PLAYER_NAME,
    mt.METRIC;

Query 4 - Season Totals & Averages for Players

This query improves on Query 3 by adding a metric average column, determined by dividing the metric total by the number of matches played (by each player) in each season.

Query 4

/*********************************************
Query 4 - Season Totals & Averages for Players
*********************************************/

WITH match_player_totals AS (
    SELECT
        pp.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.METRIC,
        SUM(s.TOTAL) AS VALUE
    FROM
        SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS s 
        JOIN SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp ON pp.MATCH_ID = s.MATCH_ID
        AND pp.PLAYER_ID = s.PLAYER_ID
        AND pp.SQUAD_ID = s.SQUAD_ID
    WHERE
        s.LEAGUE_ID = 1 
        AND s.SQUAD_ID = 1000 
    
        --  Metric Set “General”
        AND S.METRIC IN ('DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE','METRES_GAINED','INSIDE_50','REBOUND_50','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED','GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED') 
    GROUP BY
        pp.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.METRIC
),
matches_played AS (
    SELECT
        pp.SEASON_ID,
        pp.SQUAD_ID,
        pp.PLAYER_ID,
        COUNT(DISTINCT CASE WHEN pp.MATCH_PLAYED THEN pp.MATCH_ID END) AS MATCHES_PLAYED
    FROM
        SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp
    WHERE
        pp.SQUAD_ID = 1000 
    GROUP BY
        pp.SEASON_ID,
        pp.SQUAD_ID,
        pp.PLAYER_ID
)
SELECT
    mt.SEASON_ID,
    mt.PLAYER_ID,
    p.PLAYERFIRSTNAME || ' ' || p.PLAYERSURNAME AS PLAYER_NAME,
    mt.METRIC,
    SUM(mt.VALUE) AS VALUE,
    COALESCE(MAX(mp.MATCHES_PLAYED), 0) AS MT,
    
    ROUND(SUM(mt.VALUE) / NULLIF(MAX(mp.MATCHES_PLAYED), 0),2) AS AVERAGE
FROM
    match_player_totals mt
    JOIN matches_played mp ON mp.SEASON_ID = mt.SEASON_ID
    AND mp.SQUAD_ID = mt.SQUAD_ID
    AND mp.PLAYER_ID = mt.PLAYER_ID
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS p ON p.PLAYER_ID = mt.PLAYER_ID
GROUP BY
    mt.SEASON_ID,
    mt.SQUAD_ID,
    mt.PLAYER_ID,
    PLAYER_NAME,
    mt.METRIC
ORDER BY
    mt.SEASON_ID,
    PLAYER_NAME,
    mt.METRIC;

Query 5 - Season Average with Percentages

This query calculates Disposal, Kicking and Contested Possession effectiveness in the context of the season averages being returned. To achieve this the query pivots the results to present each player in a season as one row, providing their stats for that season as columns.

Query 5

/****************************************    
Query 5 - Season Averages & Percentages
****************************************/

WITH per_match AS (
    SELECT
        pp.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.MATCH_ID,
        s.METRIC,
        SUM(s.TOTAL) AS TOTAL
    FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS s
    JOIN SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp
      ON pp.MATCH_ID  = s.MATCH_ID
     AND pp.PLAYER_ID = s.PLAYER_ID
     AND pp.SQUAD_ID  = s.SQUAD_ID
    WHERE s.LEAGUE_ID = 1
      AND s.SQUAD_ID  = 1000  
    GROUP BY
        pp.SEASON_ID, s.LEAGUE_ID, s.SQUAD_ID, s.PLAYER_ID, s.MATCH_ID, s.METRIC
),

matches_played AS (
    SELECT
        pp.SEASON_ID,
        pp.SQUAD_ID,
        pp.PLAYER_ID,
        COUNT(DISTINCT CASE WHEN pp.MATCH_PLAYED THEN pp.MATCH_ID END) AS MATCHES_PLAYED
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.PLAYER_PARTICIPATION pp
    WHERE pp.SQUAD_ID = 1000     -- <-- parameterize as required
    GROUP BY pp.SEASON_ID, pp.SQUAD_ID, pp.PLAYER_ID
),

season_totals AS (
    SELECT
        pm.SEASON_ID,
        pm.SQUAD_ID,
        pm.PLAYER_ID,

        SUM(CASE WHEN pm.METRIC = 'DISPOSAL_EFFECTIVE'        THEN pm.TOTAL ELSE 0 END) AS DISPOSAL_EFFECTIVE_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'DISPOSAL'                  THEN pm.TOTAL ELSE 0 END) AS DISPOSAL_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'KICK_EFFECTIVE'            THEN pm.TOTAL ELSE 0 END) AS KICK_EFFECTIVE_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'KICK'                      THEN pm.TOTAL ELSE 0 END) AS KICK_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'HANDBALL'                  THEN pm.TOTAL ELSE 0 END) AS HANDBALL_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'METRES_GAINED'             THEN pm.TOTAL ELSE 0 END) AS METRES_GAINED_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'INSIDE_50'                 THEN pm.TOTAL ELSE 0 END) AS INSIDE_50_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'REBOUND_50'                THEN pm.TOTAL ELSE 0 END) AS REBOUND_50_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'POSSESSION_CONTESTED'      THEN pm.TOTAL ELSE 0 END) AS POSSESSION_CONTESTED_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'POSSESSION'                THEN pm.TOTAL ELSE 0 END) AS POSSESSION_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'POSSESSION_UNCONTESTED'    THEN pm.TOTAL ELSE 0 END) AS POSSESSION_UNCONTESTED_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'GROUNDBALL_GET'            THEN pm.TOTAL ELSE 0 END) AS GROUNDBALL_GET_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'MARK'                      THEN pm.TOTAL ELSE 0 END) AS MARK_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'MARK_CONTESTED'            THEN pm.TOTAL ELSE 0 END) AS MARK_CONTESTED_TOTAL,
        SUM(CASE WHEN pm.METRIC = 'MARK_UNCONTESTED'          THEN pm.TOTAL ELSE 0 END) AS MARK_UNCONTESTED_TOTAL,

        -- Percentage metrics
        SUM(CASE WHEN pm.METRIC = 'DISPOSAL_EFFECTIVE'   THEN pm.TOTAL ELSE 0 END)
        / NULLIF(SUM(CASE WHEN pm.METRIC = 'DISPOSAL'    THEN pm.TOTAL ELSE 0 END), 0) AS DISPOSAL_EFFECTIVE_PCT,

        SUM(CASE WHEN pm.METRIC = 'KICK_EFFECTIVE'       THEN pm.TOTAL ELSE 0 END)
        / NULLIF(SUM(CASE WHEN pm.METRIC = 'KICK'        THEN pm.TOTAL ELSE 0 END), 0) AS KICK_EFFECTIVE_PCT,

        SUM(CASE WHEN pm.METRIC = 'POSSESSION_CONTESTED' THEN pm.TOTAL ELSE 0 END)
        / NULLIF(SUM(CASE WHEN pm.METRIC = 'POSSESSION'  THEN pm.TOTAL ELSE 0 END), 0) AS POSSESSION_CONTESTED_PCT

    FROM per_match pm
    GROUP BY pm.SEASON_ID, pm.SQUAD_ID, pm.PLAYER_ID
)

SELECT
    st.SEASON_ID,
    st.PLAYER_ID,
    p.PLAYERFIRSTNAME || ' ' || p.PLAYERSURNAME AS PLAYER_NAME,
    COALESCE(mp.MATCHES_PLAYED, 0) AS MT,

    ROUND(st.DISPOSAL_EFFECTIVE_TOTAL     / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS DISPOSAL_EFFECTIVE,
    ROUND(st.DISPOSAL_TOTAL               / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS DISPOSAL,
    ROUND(st.DISPOSAL_EFFECTIVE_PCT, 4) AS DISPOSAL_EFFECTIVE_PCT,
    
    ROUND(st.KICK_EFFECTIVE_TOTAL         / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS KICK_EFFECTIVE,
    ROUND(st.KICK_TOTAL                   / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS KICK,
    ROUND(st.KICK_EFFECTIVE_PCT, 4)      AS KICK_EFFECTIVE_PCT,
    
    ROUND(st.HANDBALL_TOTAL               / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS HANDBALL,
    ROUND(st.METRES_GAINED_TOTAL          / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS METRES_GAINED,
    ROUND(st.INSIDE_50_TOTAL              / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS INSIDE_50,
    ROUND(st.REBOUND_50_TOTAL             / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS REBOUND_50,
    
    ROUND(st.POSSESSION_CONTESTED_TOTAL   / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS POSSESSION_CONTESTED,
    ROUND(st.POSSESSION_TOTAL             / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS POSSESSION,
    ROUND(st.POSSESSION_CONTESTED_PCT, 4)    AS POSSESSION_CONTESTED_PCT,
    
    ROUND(st.POSSESSION_UNCONTESTED_TOTAL / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS POSSESSION_UNCONTESTED,
    ROUND(st.GROUNDBALL_GET_TOTAL         / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS GROUNDBALL_GET,
    ROUND(st.MARK_TOTAL                   / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS MARK,
    ROUND(st.MARK_CONTESTED_TOTAL         / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS MARK_CONTESTED,
    ROUND(st.MARK_UNCONTESTED_TOTAL       / NULLIF(mp.MATCHES_PLAYED, 0), 2) AS MARK_UNCONTESTED


FROM season_totals st
JOIN matches_played mp
       ON mp.SEASON_ID = st.SEASON_ID
      AND mp.SQUAD_ID  = st.SQUAD_ID
      AND mp.PLAYER_ID = st.PLAYER_ID
JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.PLAYERS p
       ON p.PLAYER_ID = st.PLAYER_ID
ORDER BY
    st.SEASON_ID,
    PLAYER_NAME;

Query 6a - Team Splits, Wins & Losses

This query returns, for each match, the squad stat totals and whether the squad won, lost or tied the match.

Query 6a

/************************************
Query 6a - Team Splits, Wins & Losses
************************************/

WITH matches AS (
    SELECT
        m.MATCH_ID,
        m.SQUAD_ID,
        m.SQUADCODE,
        m.SQUADRESULT,
        m.MATCHSTART,
        m.OPPONENT_ID,
        m.OPPONENTCODE,
        m.VENUECODE,
        m.SQUADISHOME
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES m
    WHERE m.MATCHSTATUS = 'Complete'

)


SELECT
    s.MATCH_ID,
    s.SQUAD_ID,
    m.SQUADCODE,
    m.SQUADRESULT,          
    s.METRIC,
    SUM(s.total_for)     AS VALUE,
    m.SQUADISHOME
FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.SQUAD_ID = s.SQUAD_ID
JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.SQUADS sq
  ON sq.SQUAD_ID = s.SQUAD_ID
 AND sq.LEAGUE_ID = s.LEAGUE_ID
WHERE s.LEAGUE_ID = 1
  AND s.METRIC IN (
    'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE',
    'METRES_GAINED','INSIDE_50','REBOUND_50',
    'POSSESSION_CONTESTED','POSSESSION_UNCONTESTED',
    'GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED'
)

GROUP BY
    s.LEAGUE_ID, s.MATCH_ID, s.SQUAD_ID, s.METRIC,
    m.SQUADCODE, m.SQUADRESULT, m.SQUADISHOME
ORDER BY
    s.MATCH_ID, SQUADISHOME DESC;

Query 6b - Team Splits, Wins & Losses, All Seasons

This query consolidates the win/loss record stats from Query 6a across the entire data set (2019-2025), returning the squad stats for all the wins, losses and tiers for each squad.

Query 6b

/*************************************************
Query 6b - Team Splits, Wins & Losses, All Seasons
*************************************************/

-- STEP1: Collect all completed matches
WITH matches AS (
    SELECT
        m.MATCH_ID,
        m.SQUAD_ID,
        m.SQUADRESULT,
        m.MATCHSTART, 
        m.OPPONENT_ID,
        m.OPPONENTCODE,
        m.ROUNDCODE,
        m.VENUECODE
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES m
    WHERE m.MATCHSTATUS = 'Complete'    
)


-- STEP2: Join match stats and squad names; enforce league & metric filters.
SELECT
     s.SQUAD_ID,
     sq.SQUAD AS SQUAD_NAME,
     m.SQUADRESULT,                        
     
     -- ===== Occurrences of result (count of matches) =====
     COUNT(DISTINCT s.MATCH_ID) AS MATCHES,
     s.METRIC,
     SUM(s.TOTAL_FOR)     AS VALUE
     
FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.SQUAD_ID = s.SQUAD_ID
JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.SQUADS sq
  ON sq.SQUAD_ID  = s.SQUAD_ID
 AND sq.LEAGUE_ID = s.LEAGUE_ID

WHERE s.LEAGUE_ID = 1                        
  AND s.METRIC IN (
      'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE',
      'METRES_GAINED','INSIDE_50','REBOUND_50',
      'POSSESSION_CONTESTED','POSSESSION_UNCONTESTED',
      'GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED'
  )
GROUP BY
    s.SQUAD_ID,
    sq.SQUAD,
    m.SQUADRESULT,
    s.METRIC
ORDER BY
    sq.SQUAD,
    s.METRIC;

Query 7 - Team Splits, Wins & Losses Average Stats

This query returns the match average squad stats for wins, losses and ties.

Query 7

/*************************************************
Query 7 - Team Splits, Wins & Losses Average Stats
*************************************************/

-- STEP1: Collect completed matches
WITH matches AS (
    SELECT
        m.MATCH_ID,
        m.SQUAD_ID,
        m.SQUADRESULT,  
        m.MATCHSTART
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES m
    WHERE m.MATCHSTATUS = 'Complete'

),

-- STEP2: Count occurrences of each match result per squad
result_occurrences AS (
    SELECT
        m.SQUAD_ID,
        m.SQUADRESULT,
        COUNT(DISTINCT m.MATCH_ID) AS MATCHES_RESULT
    FROM matches m 
    GROUP BY
        m.SQUAD_ID,
        m.SQUADRESULT
)

-- STEP3: Join match-level squad stats; compute zero-safe averages per result
SELECT
    s.SQUAD_ID,
    sq.SQUAD  AS SQUAD_NAME,
    m.SQUADRESULT,
    s.METRIC,
    SUM(s.TOTAL_FOR) AS VALUE,
    
    -- Occurrences of this result for the squad (zero-safe)
    COALESCE(ro.MATCHES_RESULT, 0) AS MT,

    -- Averages: totals divided by occurrences of result (zero-safe)
    ROUND(SUM(s.TOTAL_FOR) / NULLIF(COALESCE(ro.MATCHES_RESULT, 0), 0), 2) AS AVG_PER_MATCH

FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s

-- Align stats to the (completed) matches by MATCH_ID/SQUAD_ID
JOIN matches m
  ON m.SQUAD_ID = s.SQUAD_ID
 AND m.MATCH_ID = s.MATCH_ID

-- Squad name (league-aware)
JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.SQUADS sq
  ON sq.SQUAD_ID  = s.SQUAD_ID
 AND sq.LEAGUE_ID = s.LEAGUE_ID

-- Attach per-squad result occurrence counts
JOIN result_occurrences ro
  ON ro.SQUAD_ID    = s.SQUAD_ID
 AND ro.SQUADRESULT = m.SQUADRESULT

WHERE s.LEAGUE_ID = 1                           
AND s.METRIC IN (
    'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE',
    'METRES_GAINED','INSIDE_50','REBOUND_50',
    'POSSESSION_CONTESTED','POSSESSION_UNCONTESTED',
    'GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED'
  )
GROUP BY
    s.SQUAD_ID,
    sq.SQUAD,
    m.SQUADRESULT,
    s.METRIC,
    ro.MATCHES_RESULT
ORDER BY
    s.SQUAD_ID,
    s.METRIC;

Query 8 - Team Splits, Wins & Losses Average Effectiveness Stats

This query returns match average squad stats by wins, losses and ties, and includes the Disposal, Kick and Contested Possession Effectiveness on each average.

Query 8

/***************************************************************
Query 8 - Team Splits, Wins & Losses Average Effectiveness Stats 
***************************************************************/

-- STEP1: Collect completed matches
WITH matches AS (
    SELECT
        m.MATCH_ID,
        m.SQUAD_ID,
        m.SQUADRESULT,
        m.MATCHSTART
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES m
    WHERE m.MATCHSTATUS = 'Complete'
),

-- STEP2: Collect squad stats
base AS (
    SELECT
        s.LEAGUE_ID,
        s.SQUAD_ID,
        UPPER(sq.SQUAD) AS SQUAD_NAME,
        m.SQUADRESULT,
        s.METRIC,
        s.TOTAL_FOR,
        s.TOTAL_AGAINST,
        s.TOTAL_DIFF,
        s.MATCH_ID
    FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
    JOIN matches m
      ON m.MATCH_ID = s.MATCH_ID
     AND m.SQUAD_ID = s.SQUAD_ID
    JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.SQUADS sq
      ON sq.SQUAD_ID  = s.SQUAD_ID
     AND sq.LEAGUE_ID = s.LEAGUE_ID
    WHERE s.LEAGUE_ID = 1

      AND s.METRIC IN (
          'DISPOSAL','DISPOSAL_EFFECTIVE','KICK','HANDBALL','KICK_EFFECTIVE',
          'METRES_GAINED','INSIDE_50','REBOUND_50',
          'POSSESSION','POSSESSION_CONTESTED','POSSESSION_UNCONTESTED',
          'GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED'
      )
),

-- STEP3: Per-metric averages per (squad, result)
avg_per_metric AS (
    SELECT
        LEAGUE_ID,
        SQUAD_ID,
        SQUAD_NAME,
        SQUADRESULT,
        METRIC,
        COUNT(DISTINCT MATCH_ID) AS MATCHES,
        ROUND(SUM(TOTAL_FOR)     / NULLIF(COUNT(DISTINCT MATCH_ID), 0), 2)     AS AVG_FOR
    FROM base
    GROUP BY LEAGUE_ID, SQUAD_ID, SQUAD_NAME, SQUADRESULT, METRIC
)

-- STEP4: Pivot to wide columns (general + percentage)
SELECT
    UPPER('AFL Premiership') AS COMPETITION_NAME,
    a.SQUAD_ID,
    a.SQUAD_NAME,
    a.SQUADRESULT,
    MAX(a.MATCHES) AS MATCHES,  -- per (squad, result)


    COALESCE(SUM(CASE WHEN a.METRIC = 'DISPOSAL_EFFECTIVE'     THEN a.AVG_FOR     END), 0) AS DISPOSAL_EFFECTIVE,
    COALESCE(SUM(CASE WHEN a.METRIC = 'DISPOSAL'               THEN a.AVG_FOR     END), 0) AS DISPOSAL,
        ROUND(
        SUM(CASE WHEN a.METRIC = 'DISPOSAL_EFFECTIVE' THEN a.AVG_FOR END)
            / NULLIF(SUM(CASE WHEN a.METRIC = 'DISPOSAL'    THEN a.AVG_FOR END), 0)
    , 2) AS DISPOSAL_EFFECTIVE_PCT,


    COALESCE(SUM(CASE WHEN a.METRIC = 'KICK_EFFECTIVE'         THEN a.AVG_FOR     END), 0) AS KICK_EFFECTIVE,
    COALESCE(SUM(CASE WHEN a.METRIC = 'KICK'                   THEN a.AVG_FOR     END), 0) AS KICK,
        ROUND(
        SUM(CASE WHEN a.METRIC = 'KICK_EFFECTIVE' THEN a.AVG_FOR END)
            / NULLIF(SUM(CASE WHEN a.METRIC = 'KICK'    THEN a.AVG_FOR END), 0), 2) AS KICK_EFFECTIVE_PCT,
    

    COALESCE(SUM(CASE WHEN a.METRIC = 'HANDBALL'               THEN a.AVG_FOR     END), 0) AS HANDBALL,

    COALESCE(SUM(CASE WHEN a.METRIC = 'METRES_GAINED'          THEN a.AVG_FOR     END), 0) AS METRES_GAINED,

    COALESCE(SUM(CASE WHEN a.METRIC = 'INSIDE_50'              THEN a.AVG_FOR     END), 0) AS INSIDE_50,

    COALESCE(SUM(CASE WHEN a.METRIC = 'REBOUND_50'             THEN a.AVG_FOR     END), 0) AS REBOUND_50,
    
    COALESCE(SUM(CASE WHEN a.METRIC = 'POSSESSION_CONTESTED'   THEN a.AVG_FOR     END), 0) AS POSSESSION_CONTESTED,
    COALESCE(SUM(CASE WHEN a.METRIC = 'POSSESSION'             THEN a.AVG_FOR     END), 0) AS POSSESSION,

    ROUND(
        100 * SUM(CASE WHEN a.METRIC = 'POSSESSION_CONTESTED' THEN a.AVG_FOR END)
            / NULLIF(SUM(CASE WHEN a.METRIC = 'POSSESSION'    THEN a.AVG_FOR END), 0)
    , 2) AS POSSESSION_CONTESTED_PCT,

    COALESCE(SUM(CASE WHEN a.METRIC = 'POSSESSION_UNCONTESTED' THEN a.AVG_FOR     END), 0) AS POSSESSION_UNCONTESTED,

    COALESCE(SUM(CASE WHEN a.METRIC = 'GROUNDBALL_GET'         THEN a.AVG_FOR     END), 0) AS GROUNDBALL_GET,

    COALESCE(SUM(CASE WHEN a.METRIC = 'MARK'                   THEN a.AVG_FOR     END), 0) AS MARK,

    COALESCE(SUM(CASE WHEN a.METRIC = 'MARK_CONTESTED'         THEN a.AVG_FOR     END), 0) AS MARK_CONTESTED,

    COALESCE(SUM(CASE WHEN a.METRIC = 'MARK_UNCONTESTED'       THEN a.AVG_FOR     END), 0) AS MARK_UNCONTESTED,

FROM avg_per_metric a
GROUP BY a.SQUAD_ID, a.SQUAD_NAME, a.SQUADRESULT
ORDER BY
    a.SQUAD_NAME;

Query 9 - Create a view from query 6a

This SQL statement creates a view in a database that returns the same results as Query 6b. The view remains in place for all to use and query directly, making it far simpler to build queries upon it.

note

Views are created in a database. Change the DATABASE portion of the view name to the name of your club's DB. Gold Coast for example should enter SUNS_DB.

Query 9

/************************************
Query 9 - Create a view from query 6a
************************************/

CREATE OR REPLACE VIEW DATABASE.PUBLIC.VW_TEAM_SPLITS_MATCH_ALL AS
WITH matches AS (
    SELECT
        m.MATCH_ID,
        m.SQUAD_ID,
        m.SQUADRESULT,
        m.MATCHSTART,
        m.OPPONENT_ID,
        m.OPPONENTCODE,
        m.ROUNDCODE,
        m.VENUECODE
    FROM SHARE_CD_AFL_WAREHOUSE.FIXTURES.MATCHES m
    WHERE m.MATCHSTATUS = 'Complete'
)
SELECT
    s.MATCH_ID,
    s.SQUAD_ID,
    sq.SQUAD AS SQUAD_NAME,
    m.SQUADRESULT,
    s.METRIC,
    SUM(s.TOTAL_FOR)     AS VALUE
FROM SHARE_CD_AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.SQUAD_ID = s.SQUAD_ID
JOIN SHARE_CD_AFL_WAREHOUSE.REFERENCE.SQUADS sq
  ON sq.SQUAD_ID  = s.SQUAD_ID
 AND sq.LEAGUE_ID = s.LEAGUE_ID
WHERE s.LEAGUE_ID = 1
  AND s.METRIC IN (
    'DISPOSAL','KICK','HANDBALL','KICK_EFFECTIVE',
    'METRES_GAINED','INSIDE_50','REBOUND_50',
    'POSSESSION_CONTESTED','POSSESSION_UNCONTESTED',
    'GROUNDBALL_GET','MARK','MARK_CONTESTED','MARK_UNCONTESTED'
)
GROUP BY
    s.LEAGUE_ID, s.MATCH_ID, s.SQUAD_ID, s.METRIC,
    sq.SQUAD, m.SQUADRESULT, m.MATCHSTART;

Query 10 - Query view, reproduce results from query 6b

This query then uses the view created in Query 9, cutting down on complexity for future querying along these lines.

note

Again replace DATABASE with the name of your DB.

Query 10

/*****************************************************
Query 10 - Query view, reproduce results from query 6b
*****************************************************/

SELECT
    SQUAD_ID,
    SQUAD_NAME,
    SQUADRESULT,

--  Count of distinct matches per squad-result-metric 
    COUNT(DISTINCT MATCH_ID) AS MATCHES,

--  Metric Name (e.g., DISPOSAL, KICK, HANDBALL, etc.) 
    METRIC,

--  Aggregated totals for the metric 
    SUM(VALUE)     AS VALUE
FROM DATABASE.PUBLIC.VW_TEAM_SPLITS_MATCH_ALL

--  Group by squad, result, and metric for consolidated view 
GROUP BY
    SQUAD_ID,
    SQUAD_NAME,
    SQUADRESULT,
    METRIC

ORDER BY
    SQUAD_NAME,
    METRIC;