6

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero

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

) , QuestionsAt as (

    select Weekend, convert(float, count(*)) as Count
    from Posts p, WeekEnds we
    where p.CreationDate < we.WeekEnd
      and p.PostTypeId = 1 -- question
    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
    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
    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
    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
    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
    group by we.WeekEnd

) , AnswerAccepts as (

    select Weekend, convert(float, count(*)) as Count
    from Votes v, WeekEnds we
    where v.CreationDate < we.WeekEnd
      and v.VoteTypeId = 1
    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 VotesQuestionRatio

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
order by we.WeekEnd

view current version

edited feb 10 14

created feb 10 14 rolfl