Q&A for amateur radio enthusiasts
-- Displays % questions answered, sampled weekly:
-- Total: Running total of % answered since beginning of time.
-- Weekly: % answered each week.
-- Weekly, Smoothed: % answered each week smoothed by WindowSize parameter.
-- WindowSize: Smoothing window size in weeks. 0 = none. 15 = 16 weeks.
-- % answered each week is computed as new accepted answers this week
-- divided by new questions this week. There are other interpretations.
-- It would theoretically be possible for this to exceed 100% if many
-- old answers were accepted at the same time few new ones were asked,
-- but I think this interpretation is good enough for the purposes of
-- this query.
-- This is currently too slow to run on SO. Optimizations welcome.
create table #dates (sampledate date)
declare @d date
select @d = min(creationdate) from posthistory
-- generate table of weekly sample dates spanning life of site
while @d <= getdate() begin
insert into #dates values (@d)
set @d = dateadd(week, 1, @d)
-- build info about accept dates
with accepted as (
left outer join votes v on v.postid = q.acceptedanswerid and v.votetypeid = 1
q.posttypeid = 1
-- build info about total questions and answered questions over time
runningstats as (
row_number() over (order by d.sampledate) k,
sum(iif(q.acceptdate <= d.sampledate, 1, 0)) answered
left outer join accepted q on
q.creationdate <= d.sampledate and
(q.closeddate is null or
q.closeddate > d.sampledate)
-- compute delta between each row in runningstats
deltastats as (
c.total - coalesce(p.total, 0) delta_total,
c.answered - coalesce(p.answered, 0) delta_answered
left outer join runningstats p on p.k = c.k - 1
-- the graph, subquery computes percentages, outer query does smoothing
avg(pctweekanswered) over (order by sampledate rows ##WindowSize?15## preceding) [Weekly, Smoothed]
iif(total > 0, 1.0 * answered / total, 0) pctanswered,
iif(delta_total > 0, 1.0 * delta_answered / delta_total, 0) pctweekanswered