0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

with comment_count as (
select
  UserId
  ,count(Id) count_comments
from
  Comments
where
  CreationDate >= dateadd(year, -5, getdate())
group by
  UserId
)
 
, post_count as (
select
  OwnerUserId
  ,count(a.id) count_posts
from
  Posts a
where
    CreationDate >= dateadd(year, -5, getdate())
group by
  OwnerUserId
)

, top_users as (
select top 10
  coalesce(a.OwnerUserId, UserId) UserId
  ,count_posts + count_comments as total_count
from
  post_count a
full join
  comment_count b on a.OwnerUserId = b.UserId
order by
  count_posts + count_comments desc
)

select
  a.UserId
  ,b.DisplayName
  ,a.total_count
from
  top_users a
left join
  Users b on a.UserId = b.Id

fork query

edited oct 29

created oct 29 anonymous