AFL Warehouse Training Guide

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 & Code	Squad	Id & Code
Adelaide Crows	10 - ADEL	Brisbane Lions	20 - BRIS
Carlton	30 - CARL	Collingwood	40 - COLL
Essendon	50 - ESS	Fremantle	60 - FREM
GWS GIANTS	1010 - GWS	Geelong Cats	70 - GEEL
Gold Coast SUNS	1000 - GCS	Hawthorn	80 - HAW
Melbourne	90 - MELB	North Melbourne	100 - NM
Port Adelaide	110 - PORT	Richmond	120 - RICH
St Kilda	130 - STK	Sydney Swans	160 - SYD
West Coast Eagles	150 - WCE	Western Bulldogs	140 - WB

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,
    S.PLAYER_DISPLAYNAME,
    
    --  Zone and Period dimensions
    S.PERIOD,
    S.ZONE_LOGICAL,
    
    --  Metric and aggregated stats for the zone & period combination
    S.METRIC,
    SUM(S.TOTAL) AS TOTAL
FROM
    AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
WHERE
    --  League filter
    S.LEAGUE_ID = 1
    
    --  Squad filter
    AND S.SQUAD_CODE = 'GCS'
    
    --  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,
    S.PLAYER_DISPLAYNAME,
    S.ZONE_LOGICAL,
    S.PERIOD,
    S.METRIC
 
--  Sorting for readability
ORDER BY
    S.MATCH_ID,
    S.PLAYER_DISPLAYNAME,
    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,
    S.PLAYER_DISPLAYNAME,
    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
    AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
WHERE
    S.LEAGUE_ID = 1
    AND S.SQUAD_CODE = 'GCS'
    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,
    S.PLAYER_DISPLAYNAME,
    S.ZONE_LOGICAL,
    S.PERIOD
ORDER BY
    S.MATCH_ID,
    S.PLAYER_DISPLAYNAME,
    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,             
        S.SQUAD_CODE,
        S.PLAYER_ID,           
        S.PLAYER_DISPLAYNAME,
        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 TOTAL,
        MT.OPPONENT_CODE AS OPPONENT
    FROM AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
    JOIN AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD 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,
        S.SQUAD_CODE,
        MT.OPPONENT_CODE,
        S.MATCH_ID,
        HALF_LOGICAL,
        S.PLAYER_ID,
        S.PLAYER_DISPLAYNAME,
        S.PERIOD,
        S.METRIC
 
 ORDER BY MATCH_ID, SQUAD_CODE, PLAYER_DISPLAYNAME, 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,
    S.SQUAD_CODE,
    S.PLAYER_ID,
    S.PLAYER_DISPLAYNAME,
    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
    AFL_WAREHOUSE.STATISTICS.MATCH_PLAYER_STATS AS S
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,
    S.SQUAD_CODE,
    S.MATCH_ID,
    HALF_LOGICAL,
    S.PERIOD,
    S.PLAYER_ID,
    S.PLAYER_DISPLAYNAME
ORDER BY
    S.MATCH_ID,
    S.PERIOD,
    PLAYER_DISPLAYNAME;

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.MATCH_PERSONS 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
        s.SEASON_ID, 
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.PLAYER_DISPLAYNAME,
        s.METRIC,
        SUM(s.TOTAL) AS TOTAL
    FROM
        AFL_WAREHOUSE.STATISTICS.PLAYER_STATS_BY_MATCH s
    WHERE
        s.LEAGUE_ID = 1 
        AND s.squad_code = 'GCS' 
        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.SEASON_ID, 
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.PLAYER_DISPLAYNAME,
        s.METRIC
),

matches_played AS (
    SELECT
        m.SEASON_ID,
        pp.SQUAD_ID,
        pp.PERSON_ID AS PLAYER_ID,
        pp.PERSON_DISPLAYNAME AS PLAYER_DISPLAYNAME,
        COUNT(pp.MATCH_ID) AS MATCHES_PLAYED
    FROM
        AFL_WAREHOUSE.FIXTURES.MATCH_PERSONS pp
    JOIN 
        AFL_WAREHOUSE.FIXTURES.MATCHES m
    ON  m.Match_id = pp.Match_id
    WHERE
        pp.SQUAD_CODE = 'GCS'
    GROUP BY
        m.SEASON_ID,
        pp.SQUAD_ID,
        pp.PERSON_ID,
        pp.PERSON_DISPLAYNAME
)


SELECT
    mt.SEASON_ID,
    mt.PLAYER_ID,
    mt.PLAYER_DISPLAYNAME,
    COALESCE(mp.MATCHES_PLAYED, 0) AS MATCHES_PLAYED,
    mt.METRIC,
    mt.TOTAL
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
ORDER BY
    mt.SEASON_ID,
    PLAYER_DISPLAYNAME,
    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
        s.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.PLAYER_DISPLAYNAME,
        s.METRIC,
        SUM(s.TOTAL) AS TOTAL
    FROM
        AFL_WAREHOUSE.STATISTICS.PLAYER_STATS_BY_MATCH s 
    WHERE
        s.LEAGUE_ID = 1 
        AND s.SQUAD_CODE = 'GCS'
    
        --  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.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.PLAYER_ID,
        s.PLAYER_DISPLAYNAME,
        s.METRIC
),
matches_played AS (
    SELECT
        m.SEASON_ID,
        pp.SQUAD_ID,
        pp.PERSON_ID AS PLAYER_ID,
        pp.PERSON_DISPLAYNAME AS PLAYER_DISPLAYNAME,
        COUNT(DISTINCT pp.MATCH_ID) AS MATCHES_PLAYED
    FROM
        AFL_WAREHOUSE.FIXTURES.MATCH_PERSONS pp
    JOIN 
        AFL_WAREHOUSE.FIXTURES.MATCHES m
    ON  m.Match_id = pp.Match_id

    WHERE
        pp.SQUAD_CODE = 'GCS' 
    GROUP BY
        m.SEASON_ID,
        pp.SQUAD_ID,
        pp.PERSON_ID,
        pp.person_displayname
)
SELECT
    mt.SEASON_ID,
    mt.PLAYER_ID,
    mt.PLAYER_DISPLAYNAME,
    mt.METRIC,
    SUM(mt.TOTAL) AS TOTAL,
    COALESCE(MAX(mp.MATCHES_PLAYED), 0) AS MT,
    
    ROUND(SUM(mt.TOTAL) / 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
GROUP BY
    mt.SEASON_ID,
    mt.SQUAD_ID,
    mt.PLAYER_ID,
    mt.PLAYER_DISPLAYNAME,
    mt.METRIC
ORDER BY
    mt.SEASON_ID,
    mt.PLAYER_DISPLAYNAME,
    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
        s.SEASON_ID,
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.SQUAD_CODE,
        s.PLAYER_ID,
        s.PLAYER_DISPLAYNAME,
        s.MATCH_ID,
        s.METRIC,
        s.TOTAL
    FROM AFL_WAREHOUSE.STATISTICS.PLAYER_STATS_BY_MATCH s
    WHERE s.LEAGUE_ID = 1
      AND s.SQUAD_CODE  = 'GCS'  
),


matches_played AS (
    SELECT
        m.SEASON_ID,
        pp.SQUAD_ID,
        pp.PERSON_ID AS PLAYER_ID,
        COUNT(DISTINCT pp.MATCH_ID) AS MATCHES_PLAYED
    FROM AFL_WAREHOUSE.FIXTURES.MATCH_PERSONS pp
    JOIN AFL_WAREHOUSE.FIXTURES.MATCHES m
    ON   m.Match_id = pp.match_id
    WHERE pp.SQUAD_CODE = 'GCS'     -- <-- parameterize as required
    GROUP BY m.SEASON_ID, pp.SQUAD_ID, PLAYER_ID
),


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

        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, pm.PLAYER_DISPLAYNAME
)


SELECT
    st.SEASON_ID,
    st.PLAYER_ID,
    st.PLAYER_DISPLAYNAME,
    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
ORDER BY
    st.SEASON_ID,
    PLAYER_DISPLAYNAME;

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.SQUAD_CODE,
        m.SQUAD_RESULT,
        m.MATCH_START,
        m.OPPONENT_ID,
        m.OPPONENT_CODE,
        m.VENUE_CODE,
        m.SQUAD_ISHOME
    FROM AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD m
    WHERE m.MATCH_STATUS = 'Complete'


)


SELECT
    s.MATCH_ID,
    s.SQUAD_ID,
    m.SQUAD_CODE,
    m.SQUAD_RESULT,          
    s.METRIC,
    SUM(s.TOTAL) AS TOTAL,
    m.SQUAD_ISHOME
FROM AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.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'
)


GROUP BY
    s.LEAGUE_ID, s.MATCH_ID, s.SQUAD_ID, s.METRIC,
    m.SQUAD_CODE, m.SQUAD_RESULT, m.SQUAD_ISHOME
ORDER BY
    s.MATCH_ID, SQUAD_ISHOME 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.SQUAD_RESULT,
        m.MATCH_START, 
        m.OPPONENT_ID,
        m.OPPONENT_CODE,
        m.ROUND_CODE,
        m.VENUE_CODE
    FROM AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD m
    WHERE m.MATCH_STATUS = 'Complete'    
)




-- STEP2: Join match stats and squad names; enforce league & metric filters.
SELECT
     s.SQUAD_ID,
     s.SQUAD_CODE,
     m.SQUAD_RESULT,                        
     
     -- ===== Occurrences of result (count of matches) =====
     COUNT(DISTINCT s.MATCH_ID) AS MATCHES,
     s.METRIC,
     SUM(s.TOTAL) AS TOTAL
     
FROM AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.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'
  )
GROUP BY
    s.SQUAD_ID,
    s.SQUAD_CODE,
    m.SQUAD_RESULT,
    s.METRIC
ORDER BY
    s.SQUAD_CODE,
    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.SQUAD_CODE,
        m.SQUAD_RESULT,  
        m.MATCH_START
    FROM AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD m
    WHERE m.MATCH_STATUS = 'Complete'


),


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


-- STEP3: Join match-level squad stats; compute zero-safe averages per result
SELECT
    s.SQUAD_ID,
    s.SQUAD_CODE,
    m.SQUAD_RESULT,
    s.METRIC,
    SUM(s.TOTAL) AS TOTAL,
    
    -- 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) / NULLIF(COALESCE(ro.MATCHES_RESULT, 0), 0), 2) AS AVG_PER_MATCH


FROM 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


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


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,
    s.SQUAD_CODE,
    m.SQUAD_RESULT,
    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.SQUAD_CODE,
        m.SQUAD_RESULT,
        m.MATCH_START
    FROM AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD m
    WHERE m.MATCH_STATUS = 'Complete'
),


-- STEP2: Collect squad stats
base AS (
    SELECT
        s.LEAGUE_ID,
        s.SQUAD_ID,
        s.SQUAD_CODE,
        m.SQUAD_RESULT,
        s.METRIC,
        s.TOTAL_FOR,
        s.TOTAL_AGAINST,
        s.TOTAL_DIFF,
        s.MATCH_ID
    FROM AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS_DIFF_LOGICAL s
    JOIN matches m
      ON m.MATCH_ID = s.MATCH_ID
     AND m.SQUAD_ID = s.SQUAD_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_CODE,
        SQUAD_RESULT,
        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_CODE, SQUAD_RESULT, METRIC
)


-- STEP4: Pivot to wide columns (general + percentage)
SELECT
    UPPER('AFL Premiership') AS COMPETITION_NAME,
    a.SQUAD_ID,
    a.SQUAD_CODE,
    a.SQUAD_RESULT,
    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_CODE, a.SQUAD_RESULT
ORDER BY
    a.SQUAD_CODE;

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.SQUAD_CODE,
        m.SQUAD_RESULT,
        m.MATCH_START,
        m.OPPONENT_ID,
        m.OPPONENT_CODE,
        m.ROUND_CODE,
        m.VENUE_CODE
    FROM AFL_WAREHOUSE.FIXTURES.MATCHES_BY_SQUAD m
    WHERE m.MATCH_STATUS = 'Complete'
)
SELECT
    s.MATCH_ID,
    s.SQUAD_ID,
    s.SQUAD_CODE,
    m.SQUAD_RESULT,
    s.METRIC,
    SUM(s.TOTAL) AS TOTAL
FROM AFL_WAREHOUSE.STATISTICS.MATCH_SQUAD_STATS s
JOIN matches m
  ON m.MATCH_ID = s.MATCH_ID
 AND m.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'
)
GROUP BY
    s.LEAGUE_ID, s.MATCH_ID, s.SQUAD_ID, s.METRIC,
    s.SQUAD_CODE, m.SQUAD_RESULT, m.MATCH_START;

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_CODE,
    SQUAD_RESULT,


--  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(TOTAL)     AS TOTAL
FROM DATABASE.PUBLIC.VW_TEAM_SPLITS_MATCH_ALL


--  Group by squad, result, and metric for consolidated view 
GROUP BY
    SQUAD_ID,
    SQUAD_CODE,
    SQUAD_RESULT,
    METRIC


ORDER BY
    SQUAD_CODE,
    METRIC;