Bigquery SQL Multiple Count group-by in single query

Question

I have a data set where a user can take several actions and each action is timestamped.

User	Action 1	Action 2	Action 3
1	5-2-2022	null	4-1-2022
2	4-2-2022	4-1-2022	4-1-2022
3	1-2-2022	null	null

I want to count the actions by date:

Date	Action 1	Action 2	Action 3
1-2-2022	1	0	0
4-1-2022	0	1	2
4-2-2022	1	0	0
5-2-2022	1	0	0

I'm struggling to figure out how to do this in Bigquery SQL and I'm not even sure what words to google. Any ideas?

Thanks in advance!

Answer 1

Consider below approach

select * from (
  select * from your_table
  unpivot (date for col in (action1, action2, action3))
)
pivot (count(distinct user) for col in ('action1', 'action2', 'action3'))