CODEWITHSUNDEEP
sqlarraysgoogle-bigquery
Anna S
Anna S

Reputation: 1

SQL - working with arrays

I'm fairly new to SQL but have to write some queries in BigQuery at work. I have a query that joins different tables and gives me a following output:

account_ids with is_subscriber column

The goal is to figure out which users (account_ids) have ever been subscribers and create a column that will say yes (if at least one 'y') or no (if all 'n').

How to write a query where I can create a new column where: for each account_id if there is at least one ‘y’ in is_subsc then it will show ‘yes’, if there is no ‘y’ associated with that account_id it will return ‘no’ So for the table above it would be: 

account_id ------------------------------------------------is_subscriber
1632bfb5-c294-4282-b3fb-a73061dc475a--------no 
ef568b94-e621-4c36-b8da-ede623051da7--------no
6f60e841-9b60-49c9-a36f-70fc00d40f4a---------yes
ec68a446-3a7d-42fc-a79f-a71446b897cd--------yes

I’d appreciate any help.

Upvotes: 0

Views: 63

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Another option for BigQuery Standard SQL 

#standardSQL
SELECT account_id, 
  ('y' IN UNNEST(is_subs)) is_ever_subscribed
FROM `project.dataset.table`

If to apply to dummy data from your question as below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1632bfb5-c294-4282-b3fb-a73061dc475a' account_id, ['n'] is_subs UNION ALL
  SELECT 'ef568b94-e621-4c36-b8da-ede623051da7', ['n'] UNION ALL
  SELECT '6f60e841-9b60-49c9-a36f-70fc00d40f4a', ['n', 'n', 'y'] UNION ALL
  SELECT 'ec68a446-3a7d-42fc-a79f-a71446b897cd', ['y', 'n'] 
)
SELECT account_id, 
  ('y' IN UNNEST(is_subs)) is_ever_subscribed
FROM `project.dataset.table`

result will be as 

Row     account_id                              is_ever_subscribed   
1       1632bfb5-c294-4282-b3fb-a73061dc475a    false    
2       ef568b94-e621-4c36-b8da-ede623051da7    false    
3       6f60e841-9b60-49c9-a36f-70fc00d40f4a    true     
4       ec68a446-3a7d-42fc-a79f-a71446b897cd    true

If your output a must be y/n vs. true/false you can use below version 

#standardSQL
SELECT account_id, 
  IF('y' IN UNNEST(is_subs), 'y', 'n') is_ever_subscribed
FROM `project.dataset.table`

in this case - output will be 

Row     account_id                              is_ever_subscribed   
1       1632bfb5-c294-4282-b3fb-a73061dc475a    n    
2       ef568b94-e621-4c36-b8da-ede623051da7    n    
3       6f60e841-9b60-49c9-a36f-70fc00d40f4a    y    
4       ec68a446-3a7d-42fc-a79f-a71446b897cd    y

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Here is one method:

select account_id,
       (select max(is_sub)
        from unnest(is_subs) is_sub
       ) as ever_sub
from t;

This uses the observation that 'y' is bigger than 'n', so max() works for this encoding and your purpose. In general, though, I would recommend storing such flags as bona fide booleans, rather than characters.

Upvotes: 3

Related Questions