3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

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

fork query

edited may 10 19

created sep 17 14 Jon "not doe" Ericson