6

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?60## - 1, CURRENT_TIMESTAMP)
;

with WeekEnds as (

    select distinct DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,CreationDate)), CONVERT(DATE,CreationDate)) as WeekEnd
    from Posts
    where CreationDate >= @epoch

) , QuestionsAt as (

    select Weekend, convert(float, count(*)) as Count
    from Posts p, WeekEnds we
    where p.CreationDate < we.WeekEnd
      and p.PostTypeId = 1 -- question
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

) , AnswersAt as (

    select Weekend, convert(float, count(*)) as Count
    from Posts p, WeekEnds we
    where p.CreationDate < we.WeekEnd
      and p.PostTypeId = 2 -- answer
      and p.CommunityOwnedDate is null
    group by we.WeekEnd

) , AnswerCommentsAt as (

    select Weekend, convert(float, count(*)) as Count
    from Comments c, Posts p, WeekEnds we
    where c.CreationDate < we.WeekEnd
      and c.PostId = p.Id
      and p.PostTypeId = 2 -- answer
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

) , QuestionCommentsAt as (

    select Weekend, convert(float, count(*)) as Count
    from Comments c, Posts p, WeekEnds we
    where c.CreationDate < we.WeekEnd
      and c.PostId = p.Id
      and p.PostTypeId = 1 -- question
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

) , QuestionVotesAt as (

    select Weekend, convert(float, count(*)) as Count
    from Votes v, Posts p, WeekEnds we
    where v.CreationDate < we.WeekEnd
      and v.VoteTypeId in (2, 3)
      and v.PostId = p.Id
      and p.PostTypeId = 1 -- QuestionVotes
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

) , AnswerVotesAt as (

    select Weekend, convert(float, count(*)) as Count
    from Votes v, Posts p, WeekEnds we
    where v.CreationDate < we.WeekEnd
      and v.VoteTypeId in (2, 3)
      and v.PostId = p.Id
      and p.PostTypeId = 2 -- AnswerVotes
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

) , AnswerAccepts as (

    select Weekend, convert(float, count(*)) as Count
    from Votes v, Posts p, WeekEnds we
    where v.CreationDate < we.WeekEnd
      and v.VoteTypeId = 1
      and v.PostId = p.Id
      and p.CommunityOwnedDate is null
      and p.ClosedDate is null
    group by we.WeekEnd

)

select we.Weekend,
Convert(decimal(10,2), AnswerAccepts.Count / QuestionsAt.Count) as AnswerAcceptRatio,
Convert(decimal(10,2), AnswersAt.Count / QuestionsAt.Count) as AnswerQuestionRatio,
Convert(decimal(10,2), AnswerVotesAt.Count / AnswersAt.Count) as VotesAnswerRatio,
Convert(decimal(10,2), AnswerCommentsAt.Count / AnswersAt.Count) as CommentsAnswerRatio,
Convert(decimal(10,2), QuestionVotesAt.Count / QuestionsAt.Count) as VotesQuestionRatio,
Convert(decimal(10,2), QuestionCommentsAt.Count / QuestionsAt.Count) as CommentsQuestionRatio

from Weekends we
left join AnswerAccepts      on we.WeekEnd = AnswerAccepts.WeekEnd
left join AnswersAt          on we.WeekEnd = AnswersAt.WeekEnd
left join QuestionsAt        on we.WeekEnd = QuestionsAt.WeekEnd
left join AnswerVotesAt      on we.WeekEnd = AnswerVotesAt.WeekEnd
left join QuestionVotesAt    on we.WeekEnd = QuestionVotesAt.WeekEnd
left join AnswerCommentsAt   on we.WeekEnd = AnswerCommentsAt.WeekEnd
left join QuestionCommentsAt on we.WeekEnd = QuestionCommentsAt.WeekEnd
where we.WeekEnd >= @epoch
  and we.WeekEnd < CURRENT_TIMESTAMP
order by we.WeekEnd

fork query

edited feb 10 14

created feb 10 14 rolfl