0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

with userTags (userId, tags) as 
(select p.OwnerUserId, tags
from
  (select distinct OwnerUserId
  from posts 
  where tags like '%c#%') p
join
  posts
on p.OwnerUserId = posts.OwnerUserId)
select top 50000 *
from
  (select OwnerUserId, 
  sum(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) as qCount,
  sum(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) as aCount,
  COUNT (AcceptedAnswerId) as acceptedPosts, 
  SUM(FavoriteCount) as favoritePosts
  from Posts
  where OwnerUserId in (select userId from userTags)
  group by owneruserid)  as p
join
  (select id, views, upvotes, downvotes, age, reputation, creationdate, lastaccessdate
  from Users
  where reputation > 100) as u
on u.id = p.ownerUserId
join
  (select userid, 
  sum(CASE WHEN class = 1 THEN 1 ELSE 0 END) as gold,
  sum(CASE WHEN class = 2 THEN 1 ELSE 0 END) as silver,
  sum(CASE WHEN class = 3 THEN 1 ELSE 0 END) as bronze
  from badges
  group by userId) as b
on b.userid = u.id
join
  (select userId,
      stuff((
        select cast(',' as varchar(max)) + tags
        from userTags
        where u.userId = userId
        for xml path('')
        ), 1, 1, '') as mergedTags
  FROM
    userTags u
  GROUP BY
    userId
  ) as t
on t.userId = u.id

fork query

edited jul 14 15

created jul 14 15 .18809