Join 4 tables with group by, 2 having and where clause

Question

I have the database consists of 4 tables:

1. users(id, "name", surname, birthdate)
2. friendships(userid1, userid2, "timestamp")
3. posts(id, userid, "text", "timestamp")
4. likes(postid, userid, "timestamp")

I need to get a result set of unique usernames having more than 3 friendships within January of 2018 and their "likes" average per "post" in the range of [10; 35).

I wrote this statement for the first step:

select  distinct u."name"
from users u
join friendships f on u.id = f.userid1
where f."timestamp" between '2018-01-01'::timestamp and '2018-01-31'::timestamp
group by u.id
having count(f.userid1) > 3;

It's working fine and returns 3 rows. But when I'm adding the second part this way:

select  distinct u."name"
from users u
join friendships f on u.id = f.userid1
join posts p on p.userid = u.id
join likes l on p.id = l.postid
where f."timestamp" between '2018-01-01'::timestamp and '2018-01-31'::timestamp
group by u.id
having count(f.userid1) > 3 
    and ((count(l.postid) / count(distinct l.postid)) >= 10 
        and (count(l.postid) / count(distinct l.postid)) < 35);

I'm getting crazy 94 rows. I don't know why. Will be thankful for possible help.

Answer 1

You don't need distinct in u.name because aggregate will remove the duplicate.

select
   u."name"
from 
   users u
   inner join friendships f on u.id = f.userid1
   inner join posts p on u.id = p.userid
   inner join likes l on p.id = l.postid
where 
   f."timestamp" >= '2018-01-01'::timestamp 
   and f."timestamp" < '2018-02-01'::timestamp
group by 
    u."name"
having 
    count(distinct f.userid1) > 3 
    and ((count(l.postid) / count(distinct l.postid)) >= 10 
            and (count(l.postid) / count(distinct l.postid)) < 35);

As in comment stated. Not good idea when you use between for date to do range.

f."timestamp" >= '2018-01-01'::timestamp 
and f."timestamp" < '2018-02-01'::timestamp

Will give you a full month of January.

Answer 2

Try the below! The issue with using "count(f.userid1) > 3" is that if a user has , e.g. 2 friends and 6 posts and 3 likes they'll get 2 x 6 = 12 rows, so 12 records with non-null f.userid1. By counting distinct f.userid2 you can count distinct friends. Similar issues appear for the other counts used for filtering.

select  u."name"
from users u
join friendships f on u.id = f.userid1
join posts p on p.userid = u.id
left join likes l on p.id = l.postid
where f."timestamp" > '2018-01-01'::timestamp and f."timestamp" < '2018-02-01'::timestamp
group by u.id, u."name"
having
 --at least three distinct friends
 count( distinct f.userid2) > 3 
  --distinct likes / distinct posts
  --we use l.* to count distinct likes since there's no primary key
  and ((count(distinct l.*) / count(distinct p.id)) >= 10 
        and ((count(distinct l.*) / count(distinct p.id)) < 35);