Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
select TagName, isnull(datediff(minute, q.CreationDate, min(a.CreationDate)), 9999999) TTA into #answers from Posts q join PostTags on q.Id = PostId join Tags t on t.Id = TagId left join Posts 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 t.TagName in (##tag##) and q.CreationDate > ##start?2019-01-01## group by q.Id, q.CreationDate, TagName; with median_answer as ( select TagName, percentile_disc(0.5) within group (order by TTA) over (partition by TagName) median_tta from #answers ), medians as ( select TagName, -- http://stackoverflow.com/a/8846679/1438 percentile_disc(0.5) within group (order by ViewCount) over (partition by TagName) median_views, percentile_disc(0.5) within group (order by AnswerCount) over (partition by TagName) median_answers, percentile_disc(0.5) within group (order by Score) over (partition by TagName) median_score from Posts p join PostTags on p.Id = PostId join Tags t on t.Id = TagId where PostTypeId = 1 and TagName in (##tag##) ), averages as ( select TagName, count(*) N, avg(ViewCount) avg_views, round(avg(1.0*Score), 1) avg_score, round(100.0*count(ClosedDate)/count(*), 1) closed_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 Posts p join PostTags on p.Id = PostId join Tags t on t.Id = TagId where PostTypeId = 1 and TagName in (##tag##) group by TagName ) select m.TagName, N, median_views, avg_score, closed_rate, avg_answers, accepted_rate, answer_rate, median_tta from medians m join averages c on c.TagName = m.TagName join median_answer a on a.TagName = m.TagName group by m.TagName, N, median_views, avg_score, closed_rate, avg_answers, accepted_rate, answer_rate, median_tta order by median_tta