9

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people organizing, marketing or licensing open source development projects

declare @epoch datetime = getdate();

with ask_days as (
  select OwnerUserId,
         dateadd(day, datediff(day, @epoch, CreationDate), @epoch) day,
         min(Score) worst,
         max(Score) best,
         count(ClosedDate) closed,
         count(*) N
  from Posts
  where PostTypeId = 1
        and OwnerUserId in (##UserId##)
        and ClosedDate is null
  group by dateadd(day, datediff(day, @epoch, CreationDate), @epoch),
           OwnerUserId
),

good_days as (
  select OwnerUserId, count(*) N
  from ask_days
  where best >= 1
        and closed = 0
  group by OwnerUserId
),
        
badges as (
  select OwnerUserId, N,
         case
           when N >= ##bronze?5## then 1
         end curious,
         case
           when N >= ##silver?30## then 1
         end inquisitive,
         case
           when N >= ##gold?100## then N/100
         end socratic
  from good_days
       join Users u on u.Id = OwnerUserId
)

select OwnerUserId as [User Link], N, curious, inquisitive, socratic
from badges
order by N desc;

fork query

edited jul 2 14

created jun 18 14 Jon "not doe" Ericson