Time span of answer score badge acquisition for users with just one Nice-and-Great answer

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

select U.Id as [User Link], A.Id as [Post Link]
  , datediff(minute, max(A.CreationDate), max(BNi.Date)) as Nice
  , datediff(minute, max(A.CreationDate), max(BGo.Date)) as Good
  , datediff(minute, max(A.CreationDate), max(BGr.Date)) as Great
  , datediff(minute, max(A.CreationDate), getdate()) as Now
  , max(A.Score) as CurScore
from Users U
inner join Posts A on A.OwnerUserId = U.Id
inner join Posts Q on Q.Id = A.ParentId
inner join PostTags PT on PT.PostId = Q.Id
inner join Tags T on T.Id = PT.TagId 
inner join (
  select A.OwnerUserId, max(A.Score) as MaxScore
  from Posts A
  inner join Posts Q on Q.Id = A.ParentId
  inner join PostTags PT on PT.PostId = Q.Id
  inner join Tags T on T.Id = PT.TagId   
    and A.PostTypeId = 2
    and A.Score >= 10
  group by A.OwnerUserId
  having count(A.Id) = 1 -- Redundant, but needed to avoid a timeout.
    and max(A.Score) >= 100
  ) AC on AC.OwnerUserId = U.Id
inner join Badges BNi on BNi.UserId = U.Id
inner join Badges BGo on BGo.UserId = U.Id
inner join Badges BGr on BGr.UserId = U.Id
where A.PostTypeId = 2
  and A.Score = MaxScore
  and BNi.Name = 'Nice Answer'
  and BGo.Name = 'Good Answer'
  and BGr.Name = 'Great Answer'
group by U.Id, A.Id
having count(BNi.Id) = 1 -- Eliminates users with deleted Nice Answers.

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results