Weekly challenge results

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mi Yodeya

Q&A for those who base their lives on Jewish law and tradition and anyone interested in learning more

declare @start date = '##start?2011-11-02##';
declare @end   date = '##end?2012-03-21##';
declare @weeks int  = (select datediff(week, @start, @end));

with 

question_type as (
  select p.Id qt_id,
         OwnerUserId,
         CreationDate,
         case 
           when CreationDate between @start and @end
                then 'contest'
           when CreationDate between dateadd(week, -@weeks, @start)
                             and @start
                then 'baseline'
           when CreationDate between @end
                              and dateadd(week, @weeks, @end)
                then 'next'
          end q_type
  from Posts p
       join PostTypes pt on pt.Id = PostTypeId
  where pt.Name = 'Question'
        and ClosedDate is null
),

questioning as (
  select count(*) questions, p.Id q_id, sum(Score) q_score
  from Posts p
       join PostTypes pt on pt.Id = PostTypeId
  where pt.Name = 'Question'
        and ClosedDate is null
  group by p.Id
),

answering as (
select count(*) answers, ParentId a_id, sum(Score) a_score
  from Posts
      join PostTypes pt on pt.Id = PostTypeId
  where pt.Name = 'Answer'
  group by ParentId
),

a_editing as (
  select count(*) a_edits, q.Id ae_id
  from Posts q
       join Posts a on q.Id = a.ParentId
       join PostHistory ph on ph.PostId = a.Id
       join PostHistoryTypes pht on pht.Id = PostHistoryTypeId
  where pht.Name in ('Edit Title', 'Edit Body', 'Edit Tags')
  group by q.Id
),
  
q_editing as (
  select count(*) q_edits, q.Id qe_id
  from Posts q
       join PostHistory ph on PostId = q.Id
       join PostHistoryTypes pht on pht.Id = PostHistoryTypeId
  where pht.Name in ('Edit Title', 'Edit Body', 'Edit Tags')
  group by q.Id
),
  
q_voting as (
  select count(*) q_votes, PostId qv_id 
  from Votes
       join VoteTypes vt on vt.Id = VoteTypeId
  where vt.Name in ('UpMod', 'DownMod')
  group by PostId
),

a_voting as (
  select count(*) a_votes, q.Id av_id 
  from Posts q
       join Posts a on q.Id = a.ParentId
       join Votes on PostId = a.Id
       join VoteTypes vt on vt.Id = VoteTypeId
  where vt.Name in ('UpMod', 'DownMod')
  group by q.Id
),

commenting as (
  select p.ParentId c_id, 
         COUNT(*) Comments, 
         SUM(pc.Score) CommentScore
  FROM Posts p
       LEFT JOIN Comments pc ON pc.PostId=p.Id
  WHERE p.PostTypeId IN (1,2)
        AND p.ClosedDate IS NULL
  GROUP BY p.ParentId
),

q_commenting as (
  select count(*) q_comments, q.Id qc_id, sum(c.Score) qc_score
  from Posts q
       join Comments c on c.PostId = q.Id
  group by q.Id
),

a_commenting as (
  select count(*) a_comments, a.ParentId ac_id, sum(c.Score) ac_score
  from Posts q
       join Posts a on q.Id = a.ParentId
       join Comments c on PostId = a.Id
  group by a.ParentId
)

select q_type,
       --max(CreationDate),
       count(distinct(OwnerUserId)) askers,
       isnull(sum(questions), 0) questions,   isnull(sum(answers), 0) answers, 
       isnull(sum(q_edits), 0) q_edits,       isnull(sum(a_edits), 0) a_edits,
       isnull(sum(q_votes), 0) q_votes,       isnull(sum(a_votes), 0) a_votes,
       isnull(sum(q_comments), 0) q_comments, isnull(sum(a_comments), 0) a_comments,
       round(
       (isnull(sum(answers), 0)    + 
        isnull(sum(q_edits), 0)    + isnull(sum(a_edits), 0) +
        isnull(sum(q_votes), 0)    + isnull(sum(a_votes), 0) +
        isnull(sum(q_comments), 0) + isnull(sum(a_comments), 0))
       /cast (isnull(sum(questions), 0) as float)
       , 2) touches_per_q    
from questioning
     full join answering    on q_id = a_id
     full join q_editing    on q_id = qe_id
     full join a_editing    on q_id = ae_id
     full join q_voting     on q_id = qv_id
     full join a_voting     on q_id = av_id
     full join q_commenting on q_id = qc_id
     full join a_commenting on q_id = ac_id
     join question_type on qt_id = q_id
where q_type is not null
group by q_type
order by min(CreationDate);

select convert(varchar, dateadd(week, -@weeks, @start), 111) 'Start baseline',
       @weeks 'Contest weeks',
       convert(varchar, dateadd(week, @weeks, @end), 111) 'End next'

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results