Volodymyr Bakhmatiuk Volodymyr Bakhmatiuk - 6 months ago 9
SQL Question

Postgres: join two queries and select based on result

There is a social network where each user can repost another user's posts. Each 10 reposts of your posts you get a gift. There are two tables:

gifts
and
repost_history
, see scheme below.

Question: how to write a query which will calculate how many gifts I need to grand for each user in a system?

=========
= gifts =
=========
id // PK
user_id // id of user which received a gift
amount // amount of gifts (bonuses), may be + or -
type // type of a gift. The only type we're interested in is 'REPOST_TYPE'

==================
= repost_history =
==================
id // PK
user_id // id of a user which did repost
owner_id // id of a user whose post was reposted


Query algorithm:

1) Find total repost count for each user
SELECT owner_id, COUNT(owner_id) FROM repost_history GROUP BY owner_id;


2)Find total amount of
REPOST_TYPE
gifts for each user
SELECT user_id, COUNT(amount) FROM gifts WHERE type = 'REPOST_TYPE' GROUP BY user_id;


3) Join 1st and 2nd steps based on
owner_id = user_id


4) From (user_id, gift_to_grand_count) result set based on 3rd step result. Where
<gift_to_grand_count> = (<reposts_of_user> / 10) - <user_repost_gifts_amount>


My workaround: the 1-3 steps implementation (not working, since I don't know how to set subquery result to variable). How to make it work and do 4th step?

(
SELECT owner_id, COUNT(owner_id) AS reposts_count
FROM reposts_history
GROUP BY owner_id
AS user_reposts
)
INNER JOIN (
SELECT user_id, COUNT(amount) AS gifts_count
FROM gifts
WHERE type = 'REPOST_GIFT'
GROUP BY user_id
AS user_gifts
)
ON user_reposts.owner_id = user_gifts.user_id


Data sample:



For simplicity reason let's suppose we want to grand a gift on each 3rd repost (instead each 10th)

gifts - you can see
user_id=1
has been granted with 1 gift of
REPOST_TYPE
. We're not interested in how many gifts he has spent.

id | user_id | amount | type |
1 | 1 | 1 | 'REPOST_TYPE' |
2 | 1 | 2 | 'OTHER_TYPE' |
3 | 1 | -1 | 'REPOST_TYPE' |
4 | 2 | 1 | 'REPOST_TYPE' |


reposts_history - you can see that user
owner_id=1
was reposted 6 times by other users.

id | user_id | owner_id | another columns...
1 | 2 | 1 |
2 | 3 | 1 |
3 | 4 | 1 |
4 | 5 | 1 |
5 | 2 | 1 |
6 | 6 | 1 |
6 | 13 | 2 |


So
user_id=1
should b granted with
<total_reposts> / 3 - <already_granted_gifts_amount> = 6 / 3 - 1 = 1
gifts.

I want to get for all users in system:

user_id | gifts_to_grant |
1 | 1 |
2 | 0 |
..........

Answer

You need an outer join in order to also find users who deserve gifts but haven't received any yet:

select
  b.ownerid as userid, 
  b.rebets_count, 
  b.rebets_count / 10 as gifts_expected, 
  coalesce(g.gifts_count, 0) as gifts_received,
  b.rebets_count / 10 - coalesce(g.gifts_count, 0) as gifts_missing 
from
(
  select owner_id, count(*) as rebets_count
  from bets 
  group by owner_id 
) b  
left join 
(
  select user_id, count(*) as gifts_count
  from gifts 
  where type = 'REBET_GIFT' 
  group by user_id 
) g on g.user_id = b.owner_id;
Comments