CODEWITHSUNDEEP
mysql
aksfasdfdsfas
aksfasdfdsfas

Reputation: 1

mysql query output the number of people that have purchased any of the same models that the user has purchased

purchased_models

user_id model_id
1   1
1   4
1   9
1   3
2   3
2   2
2   7
2   4
3   9
3   6

3   1
3   5
4   8
4   7
4   9
4   1
5   8
5   9

Using the above table, write a query to output a table the columns of which are user_id, number of models that user has purchased, and the number of people that have purchased any of the same models that the user in question has purchased. The result should look like this:

Result

user_id models_purchased    users_purchasing_same_models
1                  4           4
2                  4           2
3                  4           3
4                  4           4
5                  2           3

Got the first two with:

SELECT
                    DISTINCT user_id,
                    COUNT(DISTINCT model_id)
                FROM 
                    purchased_models
                GROUP BY user_id

, but having trouble with the third one. Any help would be greatly appreciated. Thanks!

Upvotes: 0

Views: 79

Answers (2)

Felype
Felype

Reputation: 3136

I would personaly try to self-join the table and conditional sum, as follows:

SELECT
    a.user_id AS Id,
    SUM(CASE WHEN a.user_id = b.user_id AND a.model_id=b.model_id THEN 1 ELSE 0 END) AS ModelCount,
    SUM(CASE WHEN a.user_id != b.user_id AND a.model_id=b.model_id THEN 1 ELSE 0 END) AS Others
FROM purchased_models AS a
LEFT OUTER JOIN purchased_models AS b
ON TRUE
GROUP BY a.user_id

Upvotes: 0

jpw
jpw

Reputation: 44901

One way to do this is to use a correlated subquery that counts the distinct number of users that has purchased any of the models that the user referenced in the outer scope has purchased:

select 
    t1.user_id, 
    count(distinct t1.model_id) models_purchased, 
    (
       select count(distinct user_id)
       from purchased_models t2 
       where t2.model_id in (
          select model_id 
          from purchased_models 
          where user_id = t1.user_id 
            and t2.user_id <> t1.user_id
       )
    ) users_purchasing_same_models 
from purchased_models t1
group by t1.user_id
order by t1.user_id

Upvotes: 1

Related Questions