Year over Year % difference

BigQuery Snippets
,
#1

Year-on-year percentage difference

To explore this example with some demo data, head here.

Description

Calculating the year-on-year change of a quantity typically requires a few steps:

  1. Aggregate the quantity by year (and by any other fields)
  2. Add a column for last years quantity (this will be null for the first year in the dataset)
  3. Calculate the change from last year to this year

A generic query template may then look like

select
  *,
  -- Calculate percentage change
  (<year_total> - last_year_total) / last_year_total * 100 as perc_diff
from (
  select
    *,
    -- Use a window function to get last year's total
    lag(<year_total>) over (partition by <fields> order by <year_column> asc) last_year_total
  from <table>
  order by <fields>, <year_column> desc
)

where

  • table - your source data table
  • fields - one or more columns to split the year-on-year differences by
  • year_column - the column containing the year of the aggregation
  • year_total - the column containing the aggregated data

Example

Using total Spotify streams as an example data source, let’s identify:

  • table - this is called raw
  • fields - this is just the single column artist
  • year_column - this is called year
  • year_total - this is sum_streams

Then the query looks like:

Or as a visual:

1 Like