Q&A about the site for serious players and enthusiasts of chess
select q.PostTypeId PostTypeId, isnull(datediff(minute, q.CreationDate, min(a.CreationDate)), 9999999) TTA into #answers from PostsWithDeleted q left join PostsWithDeleted a ON a.ParentId = q.Id and a.OwnerUserId <> q.OwnerUserId where q.PostTypeId = 1 and q.ClosedDate is null and q.Score >= 0 and a.CreationDate > q.CreationDate --and q.DeletionDate is null and a.DeletionDate is null and q.CreationDate >= ##start?2016-01-01## group by q.Id, q.CreationDate, q.PostTypeId; with median_answer as ( select PostTypeId, percentile_disc(0.5) within group (order by TTA) over (partition by PostTypeId) median_tta from #answers ), medians as ( select PostTypeId, -- http://stackoverflow.com/a/8846679/1438 percentile_disc(0.5) within group (order by ViewCount) over (partition by PostTypeId) median_views, percentile_disc(0.5) within group (order by AnswerCount) over (partition by PostTypeId) median_answers, percentile_disc(0.5) within group (order by Score) over (partition by PostTypeId) median_score from PostsWithDeleted p where PostTypeId = 1 and DeletionDate is null and CreationDate >= ##start## ), averages as ( select PostTypeId, count(*) N, avg(ViewCount) avg_views, round(avg(1.0*Score), 1) avg_score, round(100.0*count(ClosedDate)/count(*), 1) closed_rate, round(100.0*count(DeletionDate)/count(*), 1) deleted_rate, round(avg(1.0*AnswerCount), 1) avg_answers, round(100.0*count(AcceptedAnswerId)/count(*), 1) accepted_rate, round(100.0*count(case when AnswerCount > 0 then 1 end)/count(*), 1) answer_rate from PostsWithDeleted p where PostTypeId = 1 --and DeletionDate is null and CreationDate >= ##start## group by PostTypeId ) select N, median_views, avg_score, closed_rate, deleted_rate, avg_answers, accepted_rate, answer_rate, median_tta from medians m join averages c on c.PostTypeId = m.PostTypeId join median_answer a on a.PostTypeId = m.PostTypeId group by m.PostTypeId, N, median_views, avg_score, closed_rate, deleted_rate, avg_answers, accepted_rate, answer_rate, median_tta order by m.PostTypeId