getting id filtered from a query and using it in another query

I’ve queried user_invitations.inviter_id with filter I want which is:

select 
  inviter_id, 
  count(inviter_id) as invited_cnt
from user_invitations
group by 
  inviter_id
having 
  invited_cnt >= 52
order by 
  invited_cnt

From here I am trying to use only inviter_id to get id in another table called user (i.e. want to get user_id that match inviter_id I’ve just got above)

I’ve tried doing inner joing

from users
inner join (
    select inviter_id, count(inviter_id) as invited_cnt
    from user_invitations
    group by inviter_id
    having invited_cnt >= 52
    order by invited_cnt
)user_inv
on users.id = user_inv.id;

no luck, I thought about using subquery

from users
where id in (
    select inviter_id
    from user_invitations
    where inviter_id in (
        select count(inviter_id) as invited_cnt
        from user_invitations
        group by inviter_id
        having invited_cnt >= 52
    )
    group by inviter_id
);

but in most inner query it I cannot find out a way to return inviter_id after filtering by invited_cnt >= 52. How can I achieve this, is it not possible since to use having invited_cnt >= 52 we need aggreate count?

I’ve looked at some resources: Select id from one query and use it in another

but none of them seem to deal with aggregate.

Answer

If I understood your question, this query should give you the user_id from user that matched the inviter_id

SELECT
    us.inviter_id, 
    count(us.inviter_id) AS invited_cnt,
    u.user_id
FROM user_invitations AS us
JOIN user AS u ON u.user_id = us.inviter_id //just change the column name if needed
GROUP BY
  us.inviter_id
HAVING
  invited_cnt >= 52
ORDER BY 
  invited_cnt

If you are expecting only one user_id, then I think this should work. Although it would be nice if you included your table structure plus some sample data and expected output