How long between a question is asked, and the answer arrives....
Q&A about the site for serious players and enthusiasts of chess
-- 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