4

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

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

/ Convert(float, 60)) as Delay
from QandA
), WeekEndDates as (
select distinct WeekEnd
select WeekEnd, Delay as QuickDelay, null as ZombieDelay, null as ZombieCount, null as ZombieAge
where Delay <= @zombie
UNION ALL
select WeekEnd, null as QuickDelay, Delay / (24 * 7) as ZombieDelay, null as ZombieCount, null as ZombieAge
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]
where WeekEnd < CURRENT_TIMESTAMP
group by WeekEnd
order by WeekEnd

--select count (*) from QandA``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached