Q&A about the site for members of the workforce navigating the professional setting
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