CODEWITHSUNDEEP
sqlpostgresql
Khurram Majeed
Khurram Majeed

Reputation: 2391

Counting association between records

I have a dataset of users who have liked each other. The data looks like 

User        HasLiked      
User 1      User 3
User 1      User 4  
User 1      User 5  
User 2      User 3  
User 3      User 1     
User 4      User 1
User 4      User 5 
User 5      User 3
User 5      User 4

I can get count of liked users for any given user using

select user, count(*) as liked_count
  from users
 group by user;

which gives me

User        liked_count      
User 1      3    
User 2      1  
User 3      1     
User 4      2 
User 5      2

However I can't figure out how to count the number of times a user is liked back by someone they have liked. e.g what I want is:

User        liked_count      liked_back_count
User 1        2                 2    
User 2        1                 0
User 3        1                 1
User 4        2                 2
User 5        2                 1

Any ideas how to do this using sql? FYI, The number of distinct users is around 100K.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use a left join to determine if the "inverse" record appears. And then aggregate:

select u.user, count(*) as liked_count,
       count(*) filter (where u2.user is not null) as liked_back_count
from users u left join
     users u2
     on u2.user = u.hasliked and
        u2.hasliked = u.user
group by u.user;

Upvotes: 2

Related Questions