CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Scott Borden
Scott Borden

Reputation: 187

Redshift query to identify users belonging to specific groups

I have a redshift table (user_group) that looks like

user_id | group_id
--------+---------
1       | 1
1       | 2
2       | 1
2       | 3
3       | 2
3       | 3
4       | 4

What's the best way to write a query that returns all users (user_id) who belong to some arbitrary subset of groups (group_id)?

Right now, my hacked query is as follows, where I just add/remove WHERE filters as needed:

SELECT DISTINCT user_id
FROM
    user_group
WHERE
    user_id IN (SELECT DISTINCT user_id FROM user_group WHERE group_id = 1)
    AND user_id IN (SELECT DISTINCT user_id FROM user_group WHERE group_id = 5)
    ...

And, it feels like there should be a better way?

Upvotes: 1

Views: 1961

Answers (2)

John Rotenstein
John Rotenstein

Reputation: 269091

Users who belong to ANY listed group:

SELECT DISTINCT
  user_id
FROM user_group
WHERE group_id in (1, 5)

Users who belong to ALL listed groups:

SELECT
  user_id
FROM user_group
GROUP BY user_id
HAVING SUM(CASE WHEN group_id = 1 THEN 1 END) > 0
   AND SUM(CASE WHEN group_id = 2 THEN 1 END) > 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use GROUP BY and HAVING:

SELECT user_id
FROM user_group
WHERE group_id IN (1, 5)
GROUP BY user_id
HAVING COUNT(*) = 2;  -- number of groups in the `IN` list

Upvotes: 3

Related Questions