3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

with all_posts as (
select p.id, p.owneruserid
  from posts p
  join posts q
    on p.parentid = q.id
   and p.owneruserid <> q.owneruserid
  join posthistory ph
    on p.parentid = ph.postid
   and p.owneruserid = ph.userid
   and p.creationdate between dateadd(hour, -12, ph.creationdate)
                          and dateadd(hour,  12, ph.creationdate)
  join votes v
    on p.id = v.postid
   and p.creationdate between dateadd(hour, -12, v.creationdate)
                          and dateadd(hour,  12, v.creationdate)
 where p.posttypeid = 2
   and ph.posthistorytypeid in (4, 5)
   and q.posttypeid = 1
   and q.closeddate is null
   and v.votetypeid in (2, 3)
   and not exists ( select 1 
                      from votes 
                     where postid = p.id
                       and v.votetypeid = 6
                       and v.creationdate <= dateadd(hour, 12, p.creationdate)
                           )
 group by p.id, p.owneruserid
having sum(case when v.votetypeid = 2 then 1 else -1 end) >= 1
       )
, all_users as (
select owneruserid, count(*) as ct
  from all_posts
 group by owneruserid
having count(*) >= 10
        )
select case when ct >= 500 then 'gold'
            when ct >= 50 then 'silver'
            else 'bronze'
       end as badges
     , count(*)
  from all_users
 group by case when ct >= 500 then 'gold'
               when ct >= 50 then 'silver'
               else 'bronze'
          end

fork query

created sep 24 14 Ben