0

Please login or register to vote for this query.

(click on this box to dismiss)

The minimum, maximum, and average number of answers per post on Stack Overflow.

Q&A about the site for gardeners and landscapers

select 
    u.CreationDate as [DateJoined], 
    u.Id,
      (select count(*)
        from Posts p1
        where p1.ownerUserId=u.Id
        and p1.posttypeid=2 
        and p1.CreationDate > 
          Isnull(
            (select max(bd.Date) 
            from Badges bd 
            where bd.UserId=u.Id
            and bd.Name Like '%Answer'),getdate()
          )
       )as [AnswersSinceBadge], 
    b.Name,
    (select count(*)  
      from (select p.id 
        from Posts p 
        where p.ownerUserId=u.Id and
        p.posttypeid=2) y) as [TotalAnswers],
    (select count(*)  
      from 
        (select ba.Name 
          from Badges ba 
          where ba.Name LIKE '%Answer' 
          and ba.UserId=u.Id) x) as [NumAnswerBadges]
   --     (select count(*) from Votes
   --     inner join
   --       (select id, min(CreationDate) as [Creation] 
   --         from Posts 
   --         where posttypeid=2 and CreationDate >= b.Date
   --         group by id) pos
   --     on pos.id=Votes.PostId
   --         where CreationDate >= pos.Creation) as [Number of Votes After Badge]
    from Badges as b
    inner join Users as u
    on b.UserId = u.Id
    
    where 
    b.Name = 'Nice Answer' and
    u.CreationDate >= '2013/2/1' and
    u.CreationDate < '2013/3/1'
    order by u.CreationDate desc

fork query

edited may 8 14 anonymous

created oct 26 11 The Blue Bomber