Postgres: join two queries and select based on result

Question

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 1

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;