0

Please login or register to vote for this query.

(click on this box to dismiss)

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.
--
-- Parameters:
--
--    WindowSize: Smoothing window size in weeks. 0 = none. 15 = 16 weeks.
--
-- Note:
--
--    % 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.
--
-- https://stackexchange.com/users/305991/jason-c

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)
end;

-- build info about accept dates
with accepted as (
  select
    q.creationdate,
    q.closeddate,
    v.creationdate acceptdate
  from
    posts q
    left outer join votes v on v.postid = q.acceptedanswerid and v.votetypeid = 1
  where
    q.posttypeid = 1
),

-- build info about total questions and answered questions over time
runningstats as (
  select
    d.sampledate,
    row_number() over (order by d.sampledate) k,
    count(q.creationdate) total,
    sum(iif(q.acceptdate <= d.sampledate, 1, 0)) answered
  from
    #dates d 
    left outer join accepted q on 
      q.creationdate <= d.sampledate and
      (q.closeddate is null or
       q.closeddate > d.sampledate)
  group by
    d.sampledate
),

-- compute delta between each row in runningstats
deltastats as (
  select
    c.sampledate,
    c.total,
    c.answered,
    c.total - coalesce(p.total, 0) delta_total,
    c.answered - coalesce(p.answered, 0) delta_answered
  from
    runningstats c
    left outer join runningstats p on p.k = c.k - 1
)

-- the graph, subquery computes percentages, outer query does smoothing
select
  sampledate,
  pctanswered [Total],
  pctweekanswered [Weekly],
  avg(pctweekanswered) over (order by sampledate rows ##WindowSize?15## preceding) [Weekly, Smoothed]
from (
  select
    sampledate,
    iif(total > 0, 1.0 * answered / total, 0) pctanswered,
    iif(delta_total > 0, 1.0 * delta_answered / delta_total, 0) pctweekanswered
  from
    deltastats
  ) x
order by
  sampledate

fork query

edited may 28 17

created may 27 17 Jason C