sql - Postgres: join two queries and select based on result -

there social network each user can repost user's posts. each 10 reposts of posts gift. there 2 tables: gifts , repost_history, see scheme below.

question: how write query calculate how many gifts need grand each user in system?

========= = gifts = =========    id       // pk  user_id    // id of user received gift  amount     // amount of gifts (bonuses), may + or -   type      // type of gift.  type we're interested in 'repost_type'  ================== = repost_history = ==================       id     // pk     user_id  // id of user did repost     owner_id // id of user post reposted 

query algorithm:

1) find total repost count each user select owner_id, count(owner_id) repost_history group owner_id;

2)find total amount of repost_type gifts each user select user_id, count(amount) gifts type = 'repost_type' group user_id;

3) join 1st , 2nd steps based on owner_id = user_id

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

my workaround: 1-3 steps implementation (not working, since don't know how set subquery result variable). how make work , 4th step?

(   select owner_id, count(owner_id) reposts_count   reposts_history   group owner_id    user_reposts )    inner join (   select user_id, count(amount) gifts_count   gifts    type = 'repost_gift'    group user_id    user_gifts )  on user_reposts.owner_id = user_gifts.user_id 

data sample:

for simplicity reason let's suppose want grand gift on each 3rd repost (instead each 10th)

gifts - can see user_id=1 has been granted 1 gift of repost_type. we're not interested in how many gifts 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 - can see user owner_id=1 reposted 6 times other users.

id  | user_id | owner_id | 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 <total_reposts> / 3 - <already_granted_gifts_amount> = 6 / 3 - 1 = 1 gifts.

i want users in system:

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

you need outer join in order find users deserve gifts haven't received yet:

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