SQL - working with arrays

Question

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.

Gordon Linoff · Accepted Answer · 2018-08-15 16:31:15Z

3

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.

answered Aug 15, 2018 at 16:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail Berlyant · Accepted Answer · 2018-08-15 21:53:06Z

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

Collectives™ on Stack Overflow

SQL - working with arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related