2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for peer programmer code reviews

create table #weeks (weeknum int)
declare @d0 datetime
select @d0 = min(creationdate) from posthistory
declare @wk int
set @wk = 0
while dateadd(week, @wk, @d0) < getdate() begin
  insert into #weeks select @wk
  set @wk = @wk + 1
end;

with activity as (
  select
    userid,
    creationdate
  from (
    select distinct
      userid, 
      creationdate,
      revisionguid
    from
      posthistory
    where
      userid in (##UserIds?351462,286501,259412,352329##)
      and posthistorytypeid not in (1, 2, 3) -- covered by posts below
    ) x
  union all select
    owneruserid,
    creationdate
  from
    suggestededits
  where
    owneruserid in (##UserIds##)
  union all select
    owneruserid,
    creationdate
  from
    posts
  where
    owneruserid in (##UserIds##)
),

tallies as (
  select
    weeknum,
    userid,
    count(*) actions
  from (
    select
      userid,
      datediff(week, @d0, creationdate) weeknum
    from
      activity) a
  group by
    userid, weeknum
),

withzeroes as (
  -- lazily clamp to leftdate
  select
    period,
    userid,
    avg(1.0 * actions) over (partition by userid order by period rows ##WindowSize?3## preceding) actions,
    row_number() over (partition by userid order by period) k
  from (
    -- the graph data
    select
      dateadd(week, w.weeknum, @d0) period,
      us.userid,
      coalesce(t.actions, 0) actions
    from
      #weeks w
      cross join (select distinct userid from tallies) us
      left outer join tallies t on t.weeknum = w.weeknum and t.userid = us.userid
    ) x
  where
    period >= '##LeftDate?2014-01-01 00:00:00##'
)

-- clean up consecutive similar values because dots on graph are annoying
select
  c.period,
  (select displayname from users where id = c.userid) displayname,
  c.actions
from 
  withzeroes c
  left outer join withzeroes p on p.k = c.k - 1 and p.userid = c.userid
  left outer join withzeroes n on n.k = c.k + 1 and n.userid = c.userid
where
  p.actions is null or p.actions <> c.actions or
  n.actions is null or n.actions <> c.actions
order by
  c.period, displayname

fork query

edited may 30 17

created may 30 17 Jason C