How to count elements based on a unique value in BigQuery

user_15525130 0
Viewed 32

I have this table 1 in Bigquery and I need to count the elements in column segments and category that correspond to a single user id. Desired outcome presented in table 2. I haven't been able to figure out how to do it... maybe transforming those elements to arrays?

TABLE 1

TABLE 1

TABLE 2

TABLE 2

google-bigquery sql
1 Answers

Use below

select `desc`, count(distinct user_id) distinct_user_id
from (
  select category as `desc`, user_id from your_table 
  union all
  select segment, user_id from your_table, 
  unnest(split(segment, ';')) segment
)
where `desc` != ''
group by `desc`
user_52219440
Related