4

Please login or register to vote for this query.

(click on this box to dismiss)

How long between a question is asked, and the answer arrives....

Q&A for those who study, teach, research and apply economics and econometrics

-- measure from the answer time, not the question time.
-- this implies certain things, more to follow.

declare @history as int = - ##Weeks:int?60##
declare @zombie as int = ##Zombie:int?24##
;

with QandA as (
    select p.Id,
           p.CreationDate as QDate,
           min(a.CreationDate) as ADate
    from Posts p,
         Posts a
    where p.Id = a.ParentId
      and p.PostTypeId = 1
      and a.PostTypeId = 2
      and a.ClosedDate is null
      and p.closedDate is null
      and a.CreationDate >= convert(Date, DateAdd(week, @history, CURRENT_TIMESTAMP))
    group by p.Id, p.CreationDate
), AnswerWorking as (

    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,ADate)), 
                   CONVERT(DATE,ADate)) as WeekEnd,
           convert (Decimal(8,2), DateDiff(minute, QDate, ADate)
                   / Convert(float, 60)) as Delay
    from QandA
), AnswerDelays as (
    select WeekEnd, Delay as QuickDelay, null as ZombieDelay
    from AnswerWorking
    where Delay <= @zombie
    UNION ALL
    select WeekEnd, null as QuickDelay, Delay / (24 * 7) as ZombieDelay
    from AnswerWorking
    where Delay > @zombie
    
)
   
select WeekEnd as WeekEnd,
       Convert(Decimal(10,2), Avg(QuickDelay)) as QuickDelayHours,
       Convert(Decimal(10,2), Stdev(QuickDelay)) as QuickStdDevHours,
       Convert(Decimal(10,2), Count(QuickDelay)) as QuickCount,
       Convert(Decimal(10,2), Avg(ZombieDelay)) as ZombieDelayWeeks,
       Convert(Decimal(10,2), Stdev(ZombieDelay)) as ZombieStdDevWeek,
       Convert(Decimal(10,2), Count(ZombieDelay)) as ZombieCount
       
from AnswerDelays
where WeekEnd < CURRENT_TIMESTAMP
group by WeekEnd
order by WeekEnd


--select count (*) from QandA

view current version

edited feb 26 14

created feb 26 14 rolfl