Break strings with days of the week

user_12006167 0
Viewed 43

I have this metric in mi table:

+--------------+---------+
| days of week | User_id |
+--------------+---------+
|(1,2,3,6,7)   |  user1  |
+--------------+---------+
|(3,4,7)       |  user2  |
+--------------+---------+
|(1,2)         |  user3  |
+--------------+---------+
|(2,3,4,5,6,7) |  user4  |
+--------------+---------+
|....          | ....    |
+--------------+---------+

Those numbers represent the days of the week that the users choose.

1 -> Monday
2 -> Tuesday
3 -> Wednesday 
4 -> Thursday 
5 -> Friday
6 -> Saturday
7 -> Sunday

I would like to get the number of distinct users by days of week:

1 (Monday) -> 2 users
2 (Tuesday) -> 3 users
3 (Wednesday) -> 3 users
4 (Thursday) -> 2 users
5 (Friday) -> 1 users
6 (Saturday) -> 2 users
7 (Sunday) -> 3 users

I use SQL in BigQuery. Thank you!

google-bigquery sql
2 Answers

Consider below

select format_date('%A', '2021-01-03' + cast(day as int64)) weekday, 
  count(distinct user_id) users
from `project.dataset.table`, 
unnest(split(trim(days, '()'))) as day
group by day
order by day

if applied to sample data in your question - output is

enter image description here

user_52219440

Use translate to remove ( and ), then split to extract days from string:

with mytable as (
  select '(1,2,3,6,7)' as days, 'user1' as user_id union all
  select '(3,4,7)', 'user2' union all
  select '(1,2)', 'user3' union all
  select '(2,3,4,5,6,7)', 'user4'
)
select day, count(distinct user_id) as distinct_users
from mytable, unnest(split(translate(days, '()', ''))) as day
group by day
order by day

enter image description here

user_134735250
Related