'How to count elements based on a unique value in BigQuery

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

sqlgoogle-bigquery


Solution 1:[1]

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`

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant

Related Questions