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
), WeekEndDates as (
    select distinct WeekEnd
    from AnswerWorking
), AnswerDelays as (
    select WeekEnd, Delay as QuickDelay, null as ZombieDelay, null as ZombieCount, null as ZombieAge
    from AnswerWorking
    where Delay <= @zombie
    UNION ALL
    select WeekEnd, null as QuickDelay, Delay / (24 * 7) as ZombieDelay, null as ZombieCount, null as ZombieAge
    from AnswerWorking
    where Delay > @zombie
    UNION ALL
    select WeekEnd, null as QuickDelay, null as ZombieDelay,
           1 as ZombieCount,
           DateDiff(hour, q.CreationDate, WeekEnd) as ZombieAge
    from WeekEndDates, Posts q
    where DateAdd(hour, @zombie, q.CreationDate) < WeekEnd
      and q.PostTypeId = 1
      and q.closedDate is null
      and not exists (select 1
                      from Posts a
                      where a.ParentId = q.Id
                        and a.CreationDate < WeekEnd
                        and a.PostTypeId = 2)
    
)
   
select WeekEnd as WeekEnd,
       Convert(Decimal(10,2), Avg(QuickDelay)) as [Avg Quick Age when Answered in Hours],
       Convert(Decimal(10,2), Stdev(QuickDelay)) as [StdDev Quick Age when Answered in Hours],
       Convert(Decimal(10,2), Count(QuickDelay)) as [Quick Answers Per Week],
       Convert(Decimal(10,2), Avg(ZombieDelay)) as [Avg Zombie Age when Answered in Weeks],
       Convert(Decimal(10,2), Stdev(ZombieDelay)) as [StdDev of Zombie Age when Answered in Weeks],
       Convert(Decimal(10,2), Count(ZombieDelay)) as [Zombie Answers per Week],
       Convert(Decimal(10,1), sum(ZombieCount) / 10.0) as [10's of Zombies Still Walking],
       Convert(Decimal(10,1), avg(ZombieAge) / 168.0) as [Avg Walking Zombie Age in Weeks]
from AnswerDelays
where WeekEnd < CURRENT_TIMESTAMP
group by WeekEnd
order by WeekEnd


--select count (*) from QandA

fork query

edited feb 26 14

created feb 26 14 rolfl