0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with first_post as (
select min(p.Id) PostId, 
       count(distinct p.Id) posts,
       OwnerUserId
from Posts p
where PostTypeId in (##types?1,2##)
      and (##sameyear?0## = 0
           or CreationDate between ##year## + '-01-01' 
                               and ##year## + '-12-31')
group by OwnerUserId
)

select 
       count(*) N,
       --min(u.Id) as [User Link],
       isnull(Name, 'none') Badge,

       round(avg(1.0*Score), 2) avg_score,
       round(avg(case 
                   when ClosedDate is null then 0.0
                   else 100.0
                 end/posts), 1) closed_rate,
                 
       round(avg(1.0*posts), 1) avg_posts,
       round(avg(1.0*Reputation), 1) avg_rep,
       round(avg(1.0*Reputation/posts), 1) rep_per_post
from first_post fp
     join Posts p on fp.PostId = p.Id
     join Users u on p.OwnerUserId = u.Id
     left outer join Badges b on p.OwnerUserId = b.UserId
          and Date < p.CreationDate
where u.CreationDate between ##year## + '-01-01' 
                         and ##year## + '-12-31'
group by isnull(Name, 'none')
order by count(*) desc

fork query

edited aug 25 15

created aug 24 15 Jon "not doe" Ericson