Bigquery SQL Multiple Count group-by in single query

user_1509132 0
Viewed 93

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!

google-bigquery sql
1 Answers

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'))

enter image description here

user_52219440
Related