How to compare same period of different date ranges in columns in BigQuery standard SQL

user_17627764 0
Viewed 104

i have a hard time figuring out how to compare the same period (e.g. iso week 48) from different years for a certain metric in different columns. I am new to SQL and haven't fully understand how PARTITION BY works but guess that i'll need it for my desired output.

How can i sum the data from column "metric" and compare same periods of different date ranges (e.g. YEAR) in a table?

current table

date          iso_week          iso_year          metric
2021-12-01       48              2021              1000         
2021-11-30       48              2021               850
...
2020-11-28       48              2020               800
2020-11-27       48              2020               950
...
2019-11-27       48              2019               700
2019-11-26       48              2019               820

desired output

iso_week          metric_thisYear        metric_prevYear       metric_prev2Year
48                     1850                   1750                   1520
...
google-bigquery
1 Answers

Consider below simple approach

select * from (
  select * except(date)
  from your_table
)
pivot (sum(metric) as metric for iso_year in (2021, 2020, 2019))

if applied to sample data in your question - output is

enter image description here

user_52219440
Related