CODEWITHSUNDEEP
sqlgoogle-bigquery
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Only selecting values if other column has a certain value in BigQuery

I have a structure like this:

user_id     platform
aaa         ios
bbb         ios
ccc         android
ccc         ios
ddd         ios
ddd         android

I am trying to get user_id's that are present in both ios and android platforms.

I tried:

SELECT user_id  FROM `table`
where platform in ('ios') and platform in ('android')
group by user_id
LIMIT 100

But I get 0 results. The idea is to check cross-platform users and if user_id is shared between devices. The data is new to me, I am trying to understand it and I am not sure if my approach is correct since it returns nothing.

Desired result:

user_id    
ccc
ddd

Upvotes: 0

Views: 717

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Just use IN and add a HAVING clause:

SELECT user_id 
FROM `table`
WHERE platform in ('ios', 'android')
GROUP BY user_id
HAVING COUNT(DISTINCT platform) = 2
LIMIT 100;

If the table has no user_id/platform duplicates, then just use COUNT(*).

Upvotes: 1

Related Questions