How To: Query Calculated Metrics

What are Calculated Metrics?

A Calculated Metric is a metric whose statistics are calculated as a product of other metrics. Their stats are not stored in the warehouse, as they often depend on the nature of the query to determine what their values will be.

For example, if you report on Disposal Efficiency (metric DISPOSAL_EFFECTIVE_PCT), being a percentage its value depends on which disposals are being queried. A report of disposals in Q1 will produce a different efficiency percentage to a report of disposals in the F50 zone.

You will not find any statistics for calculated metrics in the AFL Warehouse. Instead, the formula for each calculated metric is provided so you can calculate them yourself in each query that needs them. This means their results will always be relevant to your query, which they could not do if pre-calculated and stored directly.

This article provides a worked example of adding calculated metrics to a query to generate its stats.

note

Calculated Metrics are found in the REFERENCE.GLOSSARY table with a METRIC_DIMENSIONS_TRX column value of FALSE

AFL 2025 Season Report

The goal of this example is to produce a report of several key match average statistics, by club, for the AFL 2025 Season. Six of the columns are percentages generated from calculated metrics:

D50_REBOUND_IN50_PCT (D50 Rebound Inside 50 Percentage - D50R-I50P%)
DISPOSAL_EFFECTIVE_PCT (Disposal Efficiency - DE%)
HANDBALL_EFFECTIVE_PCT (Handball Efficiency - HE%)
INSIDE_50_KICK_RETAIN_PCT (Inside 50 Kick Retention Rate - I50-KRP%)
KICK_EFFECTIVE_PCT (Kick Efficiency - KE%)
MARK_PLAY_ON_PCT (Mark Play On Rate - MPOP%)

This is what this report looks like when created using CIA's Dimensional Custom report. The SQL file below produces a very similar table.

Report Generation process

Copy the SQL queries below into a Snowflake SQL file
Choose the AFL Warehouse Database to target with these queries.
Run Step 1 to collect the calculated metrics from the glossary
Run Steps 2 - 5 together to produce the report data.

This report also uses the SQL PIVOT function to pivot metric rows into columns for aggregation. Its a very useful way to build a stat column-oriented table of results in the database for ease of reporting.

// Goal: Produce an AFL 2025 season report of several key match average statistics
// This report includes some percentage metrics that need to be calculated once the match averages have been calculated
// The following queries demonstrate how to obtain, then use calculated metric formulae to produce the desired results




/* Step 1: Look up calculated metrics you want to report on */
// Returned in a way that it can be copied and pasted into queries below
SELECT CONCAT(METRIC_TECHNICAL_DESCRIPTION, ' AS ', METRIC_CODE) AS CALCULATED_METRIC
FROM REFERENCE.GLOSSARY 
WHERE METRIC_CODE IN ('DISPOSAL_EFFECTIVE_PCT', 'HANDBALL_EFFECTIVE_PCT', 'KICK_EFFECTIVE_PCT', 'INSIDE_50_KICK_RETAIN_PCT', 'MARK_PLAY_ON_PCT', 'D50_REBOUND_IN50_PCT');


/* Step 1b: It helps to drop the Calculated Metric Formulae here for quick reference
CALCULATED_METRIC
=================
D50_REBOUND_IN50/D50_REBOUND AS D50_REBOUND_IN50_PCT
KICK_EFFECTIVE/KICK AS KICK_EFFECTIVE_PCT
INSIDE_50_KICK_RETAIN/INSIDE_50_KICK AS INSIDE_50_KICK_RETAIN_PCT
DISPOSAL_EFFECTIVE/DISPOSAL AS DISPOSAL_EFFECTIVE_PCT
MARK_PLAY_ON/MARK AS MARK_PLAY_ON_PCT
HANDBALL_EFFECTIVE/HANDBALL AS HANDBALL_EFFECTIVE_PCT
*/


/* Step 2: Build query to collect the metrics in each calculated metric formule */
WITH statsForCalculatedMetrics AS (
SELECT DISTINCT 
        SQUAD_CODE,


        MATCHES_PLAYED,


        // Each set of stats need for each calculated metric are generated as their own columns
        // They are divided by MATCHES_PLAYED to get the Match average value


        // D50_REBOUND_IN50_PCT
        (SUM("'D50_REBOUND_IN50'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS D50_REBOUND_IN50,
        (SUM("'D50_REBOUND'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS D50_REBOUND,


        // DISPOSAL_EFFECTIVE_PCT
        (SUM("'DISPOSAL_EFFECTIVE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS DISPOSAL_EFFECTIVE,
        (SUM("'DISPOSAL'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS DISPOSAL,


        // HANDBALL_EFFECTIVE_PCT
        (SUM("'HANDBALL_EFFECTIVE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS HANDBALL_EFFECTIVE,
        (SUM("'HANDBALL'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS HANDBALL,


        // INSIDE_50_KICK_RETAIN_PCT
        (SUM("'INSIDE_50_KICK_RETAIN'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS INSIDE_50_KICK_RETAIN,
        (SUM("'INSIDE_50_KICK'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS INSIDE_50_KICK,


        // KICK_EFFECTIVE_PCT
        (SUM("'KICK_EFFECTIVE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS KICK_EFFECTIVE,
        (SUM("'KICK'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS KICK,


        // MARK_PLAY_ON_PCT
        (SUM("'MARK_PLAY_ON'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK_PLAY_ON,
        (SUM("'MARK'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK


FROM    STATISTICS.SQUAD_STATS_BY_SEASON


// The PIVOT function obtains the values for each
// metric column listed in the SELECT clause
PIVOT(SUM(TOTAL) FOR METRIC IN (
        'D50_REBOUND_IN50',
        'D50_REBOUND',
        'DISPOSAL_EFFECTIVE',
        'DISPOSAL',
        'HANDBALL_EFFECTIVE',
        'HANDBALL',
        'INSIDE_50_KICK_RETAIN',
        'INSIDE_50_KICK',
        'KICK_EFFECTIVE',
        'KICK',
        'MARK_PLAY_ON',
        'MARK'))
WHERE   SEASON_ID = 2025 AND LEAGUE_ID = 1),




/* Step 3: Generate the calculated metric values, pasting the calculated metrics from step 1 */
calculatedStats AS (
SELECT  SQUAD_CODE,
        MATCHES_PLAYED,
        D50_REBOUND_IN50/D50_REBOUND AS D50_REBOUND_IN50_PCT,
        KICK_EFFECTIVE/KICK AS KICK_EFFECTIVE_PCT,
        INSIDE_50_KICK_RETAIN/INSIDE_50_KICK AS INSIDE_50_KICK_RETAIN_PCT,
        DISPOSAL_EFFECTIVE/DISPOSAL AS DISPOSAL_EFFECTIVE_PCT,
        MARK_PLAY_ON/MARK AS MARK_PLAY_ON_PCT,
        HANDBALL_EFFECTIVE/HANDBALL AS HANDBALL_EFFECTIVE_PCT
FROM    statsForCalculatedMetrics
),


/* Step 4: Obtain the remaining (non-calculated) stats to complete the report */
remainingStats AS (
SELECT DISTINCT 
        SQUAD_CODE,


        MATCHES_PLAYED,


        // Each set of stats need for each calculated metric are generated as their own columns
        // They are divided by MATCHES_PLAYED to get the Match average value


        // D50_REBOUND_IN50_PCT
        (SUM("'TACKLE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS TACKLE,
        (SUM("'MARK'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK,


        // DISPOSAL_EFFECTIVE_PCT
        (SUM("'MARK_CONTESTED_SQ'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK_CONTESTED_SQ,
        (SUM("'MARK_UNCONTESTED_SQ'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK_UNCONTESTED_SQ,


        // HANDBALL_EFFECTIVE_PCT
        (SUM("'MARK_ON_LEAD'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK_ON_LEAD,
        (SUM("'MARK_CONTESTED_OPPOSITION'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS MARK_CONTESTED_OPPOSITION,


        // INSIDE_50_KICK_RETAIN_PCT
        (SUM("'GROUNDBALL_GET'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS GROUNDBALL_GET,
        (SUM("'HARDBALL_GET'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS HARDBALL_GET,


        // KICK_EFFECTIVE_PCT
        (SUM("'LOOSEBALL_GET'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS LOOSEBALL_GET,
        (SUM("'HANDBALL_RECEIVE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS HANDBALL_RECEIVE,


        // MARK_PLAY_ON_PCT
        (SUM("'FIRST_POSSESSION'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS FIRST_POSSESSION,
        (SUM("'CLEARANCE_CB'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CLEARANCE_CB,
        (SUM("'CLEARANCE_BU'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CLEARANCE_BU,
        (SUM("'CLEARANCE_TI'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CLEARANCE_TI,
        (SUM("'SCORING_SHOT'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS SCORING_SHOT,
        (SUM("'GOAL'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS GOAL,
        (SUM("'CHAIN_LAUNCH_SCORE'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CHAIN_LAUNCH_SCORE,
        (SUM("'SCORE_ASSIST'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS SCORE_ASSIST,
        (SUM("'INSIDE_50'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS INSIDE_50,
        (SUM("'FREE_KICK'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS FREE_KICK,
        (SUM("'FREE_KICK_AGAINST'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS FREE_KICK_AGAINST,
        (SUM("'TURNOVER'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS TURNOVER,
        (SUM("'INTERCEPT'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS INTERCEPT,
        (SUM("'RANKING_POINTS'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS RANKING_POINTS,
        (SUM("'CHAIN_LAUNCH_ST_POINTS'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CHAIN_LAUNCH_ST_POINTS,
        (SUM("'CHAIN_LAUNCH_INT_POINTS'") OVER (PARTITION BY SQUAD_CODE)) / MATCHES_PLAYED AS CHAIN_LAUNCH_INT_POINTS


FROM    STATISTICS.SQUAD_STATS_BY_SEASON


// The PIVOT function obtains the values for each
// metric column listed in the SELECT clause
PIVOT(SUM(TOTAL) FOR METRIC IN (
        'TACKLE',
        'MARK',
        'MARK_CONTESTED_SQ',
        'MARK_UNCONTESTED_SQ',
        'MARK_ON_LEAD',
        'MARK_CONTESTED_OPPOSITION',
        'GROUNDBALL_GET',
        'HARDBALL_GET',
        'LOOSEBALL_GET',
        'HANDBALL_RECEIVE',
        'FIRST_POSSESSION',
        'MARK_PLAY_ON',
        'CLEARANCE_CB',
        'CLEARANCE_BU',
        'CLEARANCE_TI',
        'SCORING_SHOT',
        'GOAL',
        'CHAIN_LAUNCH_SCORE',
        'SCORE_ASSIST',
        'INSIDE_50',
        'FREE_KICK',
        'FREE_KICK_AGAINST',
        'TURNOVER',
        'INTERCEPT',
        'RANKING_POINTS',
        'CHAIN_LAUNCH_ST_POINTS',
        'CHAIN_LAUNCH_INT_POINTS'))
WHERE   SEASON_ID = 2025 AND LEAGUE_ID = 1
        )


/* Step 5: Combine calculated and non-calculated stats together for the report */
SELECT  calc.SQUAD_CODE,
        calc.MATCHES_PLAYED,
        TACKLE,
        DISPOSAL_EFFECTIVE_PCT,
        HANDBALL_EFFECTIVE_PCT, 
        KICK_EFFECTIVE_PCT,
        MARK,
        MARK_CONTESTED_SQ,
        MARK_UNCONTESTED_SQ,
        MARK_ON_LEAD,
        MARK_CONTESTED_OPPOSITION,
        MARK_PLAY_ON_PCT,
        GROUNDBALL_GET,
        HARDBALL_GET,
        LOOSEBALL_GET,
        HANDBALL_RECEIVE,
        FIRST_POSSESSION,
        CLEARANCE_CB,
        CLEARANCE_BU,
        CLEARANCE_TI,
        SCORING_SHOT,
        GOAL,
        CHAIN_LAUNCH_SCORE,
        SCORE_ASSIST,
        INSIDE_50,
        INSIDE_50_KICK_RETAIN_PCT,
        D50_REBOUND_IN50_PCT,
        FREE_KICK,
        FREE_KICK_AGAINST,
        TURNOVER,
        INTERCEPT,
        RANKING_POINTS,
        CHAIN_LAUNCH_ST_POINTS,
        CHAIN_LAUNCH_INT_POINTS
FROM    calculatedStats calc
JOIN    remainingStats rem
ON      calc.SQUAD_CODE = rem.SQUAD_CODE
ORDER BY calc.SQUAD_CODE;
        

Your results in Snowflake should look like this:

And when the SQL is applied to a Tableau report, looks like this:

What are Calculated Metrics?​

AFL 2025 Season Report​

Report Generation process​

What are Calculated Metrics?

AFL 2025 Season Report

Report Generation process