Site question stats

4

(click on this box to dismiss)

Q&A for users and developers of hardware and software for Raspberry Pi

``````select q.PostTypeId PostTypeId,
isnull(datediff(minute, q.CreationDate, min(a.CreationDate)),
9999999) TTA
from Posts q
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 q.CreationDate > '2014-01-01'
group by q.Id, q.CreationDate, q.PostTypeId;

select PostTypeId,
percentile_disc(0.5)
within group (order by TTA)
over (partition by PostTypeId)
median_tta
),

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)
over (partition by PostTypeId)

percentile_disc(0.5)
within group (order by Score)
over (partition by PostTypeId)
median_score
from Posts p
where PostTypeId = 1
),

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(case
when AnswerCount > 0 then 1
from Posts p
where PostTypeId = 1
group by PostTypeId
)

select
N,
median_views,
avg_score,
closed_rate,
accepted_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,