Daniël Zwijnenburg Daniël Zwijnenburg - 7 months ago 18
SQL Question

Count with subquery, group_by and Left join

I am struggeling for along time with this particular query. I am making a 'leaderboard' of users whom have added or received the most places to their guides.

My data model is as follows:

Users

uuid id
string first_name
bool blogger

Guests

uuid id

Tips (join model)

uuid id
uuid guide_id
uuid place_id
integer status
uuid owner_id
string owner_type

Guides

uuid id
uuid user_id
string name

Place
uuid id
string name


A user can add places (trough tips) to his/her guide. This same user can also receive places (tips) from other users to his/her guide. These tips can be accepted
tips.status = 1
.

What I want is the following:

first_name and count of all places added or accepted tips over all their guides, but not tips they have given to other users grouped by
users.blogger = 1
.

example:

Guest = true

you 40
user1 30
user2 25

Guest = false
user3 20
user4 15
user5 5


this is what I have so far:

SELECT tips.owner_id, tips.owner_type, count(tips.owner_id) AS places_count
FROM "tips"
LEFT JOIN users on (owner_type ='User' AND users.id = owner_id)
GROUP BY "tips"."owner_id", "tips"."owner_type"
ORDER BY places_count DESC
LIMIT 16


This query does return counts, but does not take received tips in regard and it also counts given tips to other users. I have a hunch that I need to use subqueries, firstly select all the guide id's from a given user and secondly 'simply' select a count of all tips where
guide_id = selected_guide_ids
AND
tips.status = 1
. Lastly group the results by
users.blogger = 1


But how do I write this?

Edit 1:



I have updated my original question with an additional
Guest
table (this is why I use
owner_type and owner_id instead
of
table_id
. And I've updated the user table with blogger (bool) on which I want to group the results.

Sample data:

Users

id first_name blogger
user1 Daniel true
user2 Quassnoi false
user3 vkp true

Guests

id
guest_1
guest_2

Guides

id user_id name
guide_1 user_1 Bugers
guide_2 user_1 Cool places
guide_3 user_2 Amsterdam

Tips

id guide_id place_id status owner_id owner_type
tip1 guide_1 place_1 1 user_1 User # user_1 added place_1 to his own guide guide_1 (accepted)
tip2 guide_1 place_2 1 guest_1 Guest # guest_1 suggested place_2 to user_1's guide guide_1 (accepted)
tip2 guide_1 place_2 0 guest_1 Guest # guest_1 suggested place_2 to user_1's guide guide_1 (rejected)
tip_3 guide_2 place_1 1 user_2 User # user_2 added place_1 to his own guide guide_3 (accepted)
tip_4 guide_2 place_2 1 user_2 User # user_2 added place_2 to his own guide guide_3 (accepted)
tip_5 guide_2 place_3 1 user_1 User # user_1 added place_3 to user_2's guide guide_2 (accepted)

Places

id name
place1 burgerbar
place2 burgermeester
place_3 bbq shack


What my desired outcome is:

Note that tips given to other users don't count for the tip giver.

first_name tips_count blogger

Quassnoi 3 false (2 added by himself, 1 received from user_1)
Daniel 2 true (1 added by himself, 1 received from guest1. Note that the rejected tip does not count)
vkp 0 false


Edit 2



I've altered Quassnoi's answer a little bit to this:

SELECT *
FROM users u
LEFT JOIN
(
SELECT g.user_id, COUNT(*) tips_count
FROM guides g
JOIN tips t
ON t.guide_id = g.id
AND (t.owner_id = g.user_id AND t.status = 1)
GROUP BY g.user_id
) g
ON g.user_id = u.id
ORDER BY tips_count DESC


This however returns all the records where tips_count is NULL first. I want those to be 0 instead of NULL. How can I cast NULL tips_count to 0?

Edit 3:



I've updated the query so that it only counts the tips where the guide_id is equal to the guide ids from the given user.

SELECT *
FROM users u
LEFT JOIN
(
SELECT g.user_id, COUNT(*) tips_count
FROM guides g
JOIN tips t
ON t.guide_id = g.id
AND (t.guide_id = g.id AND t.status = 1)
GROUP BY g.user_id
) g
ON g.user_id = u.id
ORDER BY tips_count DESC

Answer

It seems that it doesn't really matter who added/suggested a place. Once it is accepted (status 1) it belongs to the guide and thus to the guide's user. Hence:

select u.first_name, u.blogger, count(t.id)
from users u
left join guides g on g.user_id = u.id
left join tips t on t.guide_id = g.id and t.status = 1
group by u.id
order by count(t.id) desc;
Comments