1

Please login or register to vote for this query.

(click on this box to dismiss)

I used the query "Progress towards Curious, Inquisitive and Socratic badges" created by Jon Ericson - and added the stats for all users, including deleted ones.https://data.stackexchange.com/mathoverflow/query/229495/progress-towards-curious-inquisitive-and-socratic-badges

Q&A for pro webmasters

---based on https://data.stackexchange.com/mathoverflow/query/229495/progress-towards-curious-inquisitive-and-socratic-badges

declare @epoch datetime = getdate();

with ask_days as (
  select dateadd(day, datediff(day, @epoch, CreationDate), @epoch) day,
         OwnerUserId uid, 
         OwnerDisplayName udn,
         min(Score) worst,
         max(Score) best,
         -- only posts closed within 60 days count against qualification
         count(case when ClosedDate is not null and datediff(day, CreationDate, ClosedDate) <= 60 then 1 else null end) closed
  from Posts
  where PostTypeId = 1
---        and OwnerUserId in (##UserId##)
  group by dateadd(day, datediff(day, @epoch, CreationDate), @epoch), OwnerUserId, OwnerDisplayName
),

good_days as (
  select uid, udn, count(*) N
  from ask_days
  where best >= 1
        and worst >= 0
        and closed = 0
  group by uid, udn        
)

SELECT TOP(##num?100##)
  uid AS [User Link], udn, N AS [Count]
FROM good_days  
ORDER BY N DESC

fork query

edited jan 16

created jan 16 Martin20528