I have the following table in postgres 14:

user  team 
1      A
10     B
113    C
12     A
1      B
113    A
12     C
1      C
113    B

What is the best way to query the count of distinct user that is in all 3 teams A, B, C and the list of all these user

The output should:

count   list_user
2       [1, 113]

I am trying to use cte's for each one of the 3 teams but the query is running forever and is not retrieving any results then timeout.

CodePudding user response：

We can use a double aggregation approach here:

SELECT COUNT(*) AS count,
       STRING_AGG(user::text, ',') AS list_user
FROM
(
    SELECT user
    FROM yourTable
    WHERE team IN ('A', 'B', 'C')
    GROUP BY user
    HAVING COUNT(DISTINCT team) = 3
) t;