0

Please login or register to vote for this query.

(click on this box to dismiss)

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
  select AccountId as account_id, Id as user_id from Users
),
temp
as
(
  select 
    t2.account_id,
    count(site_id) as sites_count,
    max(reputation) as max_rep
  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 as AccountId,
--  au.user_id as UserId,
--  temp.max_rep as max_rep,
--  temp.sites_count as sites_count
 count(*)
FROM temp
inner join
au
on au.account_id = temp.account_id
WHERE au.account_id >= ##AccountIdFrom:int?1##
--order by temp.account_id ASC

fork query

edited may 23

created may 23 avm23