Year on Year Day of Week Sales in Big Query using GENERATE_DATE_ARRAY

bq_sales_yoy.sql

--
-- Week Daily for non-Orthogonal Date dimensions e.g. day of week
--
with Calendar as (
  select 
    this_year,  
    date_add(this_year, interval - 364 day) as last_year
  from 
    unnest(GENERATE_DATE_ARRAY('2017-01-01', '2017-12-31')) as this_year
),
Sales as (
  select sales_date, sales_count
  from unnest([
    STRUCT<sales_date date, sales_count int64>('2017-11-30', 1233),  
    STRUCT<sales_date date, sales_count int64>('2017-12-01', 1234),
    STRUCT<sales_date date, sales_count int64>('2017-12-02', 1235),
    STRUCT<sales_date date, sales_count int64>('2017-12-03', 1236),
    STRUCT<sales_date date, sales_count int64>('2017-12-04', 1237),
    STRUCT<sales_date date, sales_count int64>('2017-12-05', 1238),
    STRUCT<sales_date date, sales_count int64>('2016-11-30', 1223),
    STRUCT<sales_date date, sales_count int64>('2016-12-01', 1224),
    STRUCT<sales_date date, sales_count int64>('2016-12-02', 1225),
    STRUCT<sales_date date, sales_count int64>('2016-12-03', 1226),
    STRUCT<sales_date date, sales_count int64>('2016-12-04', 1227),
    STRUCT<sales_date date, sales_count int64>('2016-12-05', 1228)
  ])
),
Totals as (
  -- Optional Pre-aggregation step
  select 
    this_year, 
    case when this_year = sales_date then 'this_year' else 'last_year' end as year_group, 
    sales_count
  from Sales inner join Calendar 
    on Sales.sales_date = Calendar.this_year 
    or Sales.sales_date = Calendar.last_year 
),
Pivot as (
  -- Pivot to this/last year as columns 
  select 
    this_year, 
    max(case when year_group = 'this_year' then sales_count end) as sales_this_year, 
    max(case when year_group = 'last_year' then sales_count end) as sales_last_year
  from Totals
  group by 
    1
)
select * 
from Pivot
order by this_year asc