4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

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

fork query

edited jun 1 16

created sep 23 14 Jon "not doe" Ericson