Asking over time badges


Please login or register to vote for this query.

(click on this box to dismiss)

Server Fault

Q&A for system administrators and IT professionals

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),

good_days as (
  select OwnerUserId, count(*) N
  from ask_days
  where best >= 1
        and closed = 0
  group by OwnerUserId
badges as (
  select OwnerUserId, N,
           when N >= ##bronze?5## then 1
         end curious,
           when N >= ##silver?30## then 1
         end inquisitive,
           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;

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results