I have the following table:
I want to group by YEAR, WEEK and Event (Walk, jump, Swim) and count the number of times each event occurs in Day_1, Day_2, Day_3. I.e.
How can I do this efficiently?
In BigQuery, I would unpivot using arrays and then aggregate:
with t as ( select 2020 as year, 1 as week, 'Walk' as day_1, 'Jump' as day_2, 'Swim' as day_3 union all select 2020, 3, 'Walk', 'Swim', 'Walk' union all select 2020, 1, 'Jump', 'Walk', 'Swim' ) select t.year, t.week, s.event, countif(day = 1) as day_1, countif(day = 2) as day_2, countif(day = 3) as day_3 from t cross join unnest([struct(t.day_1 as event, 1 as day), struct(t.day_2 as event, 2 as day), struct(t.day_3 as event, 3 as day) ]) s group by t.year, t.week, s.event;