Accounts that certainly deserve an association bonus

0

Please login or register to vote for this query.

(click on this box to dismiss)

Islam Meta

Q&A about the site for Muslims, experts in Islam, and those interested in learning more about Islam

/*
List account_id, user_id (Math.SE), and the total number of sites
this user is associated with.

The bonus is given when (https://meta.stackexchange.com/a/303118):
   - a user has 200+ rep at at least one site, and
   - is registered at 2 or more sites

As an approximation, we check if a user's current reputation exceeds 200,
instead of checking if this was ever the case in the past.
*/ 
with au
as
(
  select account_id, user_id from dbo.sede_users
  where site_id = 69
),
temp
as
(
  select t2.account_id, count(site_id) as sites_count from
    au
    inner join 
    (select * from dbo.sede_users) t2
    on au.account_id = t2.account_id
    group by t2.account_id
    having count(site_id) > 1 and max(reputation) > 200
)
SELECT temp.account_id, au.user_id, temp.sites_count
-- SELECT count(*)
FROM temp
inner join
au
on au.account_id = temp.account_id
order by temp.account_id ASC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results