CODEWITHSUNDEEP
mysqlsql
inspire
inspire

Reputation: 189

Finding users with at least one of every item

For example, I have the following table called, Information

user_id | item
-------------------------
45      | camera
36      | smartphone
23      | camera
1       | glucose monitor
3       | smartwatch
2       | smartphone
7       | smartphone
2       | camera
2       | glucose monitor
2       | smartwatch

How can I check which user_id has at least one of every item?

The following items will not be static and may be different everytime. However in this example there are 4 unique items: camera, smartphone, smartwatch, glucose monitor

Expected Result:

Because user_id : 2 has at least one of every item, the result will be:

user_id 
2

Here is what I attempted at so far, however if the list of items changes from 4 unique items to 3 unique items, I don't think it works anymore. 

SELECT *
FROM Information
GROUP BY Information.user_id
having count(DISTINCT item) >= 4

Upvotes: 0

Views: 58

Answers (3)

user3427017
user3427017

Reputation: 23

One more way of solving this problem is by using CTE and dense_rank function. This also gives better performance on MySQL. The Dense_Rank function ranks every item among users. I count the number of distinct items and say pick the users who have the maximum number of distinct items.

 With Main as (
   Select user_id
          ,item
          ,Dense_Rank () over (
              Partition by user_id
              Order by item
              ) as Dense_item
   From information
   )
 Select
     user_id
 From Main
 Where 
     Dense_item = (
        Select
           Count(Distinct item)
        from
           information);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

One approach would be to aggregate by user_id, and then assert that the distinct item_id count matches the total distinct item_id count from the entire table.

SELECT
    user_id
FROM Information
GROUP BY
    user_id
HAVING
    COUNT(DISTINCT item_id) = (SELECT COUNT(DISTINCT item_id) FROM Information);

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

You can try to use self-join by count and total count

SELECT t1.user_id
FROM (
  SELECT user_id,COUNT(DISTINCT item) cnt
  FROM T
  GROUP BY user_id
) t1 JOIN (SELECT COUNT(DISTINCT item) cnt FROM T) t2
WHERE t1.cnt = t2.cnt

or exists

Query 1:

SELECT t1.user_id
FROM (
  SELECT user_id,COUNT(DISTINCT item) cnt
  FROM T
  GROUP BY user_id
) t1 
WHERE exists(
  SELECT 1
  FROM T tt
  HAVING COUNT(DISTINCT tt.item) = t1.cnt
)

Results:

| user_id |
|---------|
|       2 |

Upvotes: 1

Related Questions