0

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

``````-- granularity: How many days should be in one bucket?
-- fgitw: How many minutes is Fastest Gun?

with phid as (
select postid, max(id) id
from posthistory
where posthistorytypeid in (3, 10, 11)
group by postid)

select dateadd(day, datediff(day, 0, a.creationdate)/##granularity:int?14##*##granularity##, 0) [date]
, sum(
case when posthistorytypeid = 10
and ph.comment in (1, 101)
then 100.0 else 0 end)
, sum(
case when datediff(mi, q.creationdate, a.creationdate) > ##fgitw:int?60##
and posthistorytypeid = 10
and ph.comment in (1, 101)
then 100.0 else 0 end)
/ sum(
case when datediff(mi, q.creationdate, a.creationdate) <= ##fgitw##
then 1 else 0 end) [without fgitw]
, sum(
case when datediff(mi, q.creationdate, a.creationdate) <= ##fgitw##
and posthistorytypeid = 10
and ph.comment in (1, 101)
then 100.0 else 0 end)
/ sum(
case when datediff(mi, q.creationdate, a.creationdate) <= ##fgitw##
then 1 else 0 end) [only fgitw]
from posts q
left join phid on q.id = phid.postid
left join posthistory ph on ph.id = phid.id
join posts a on a.parentid = q.id
group by dateadd(day, datediff(day, 0, a.creationdate)/##granularity##*##granularity##, 0)
order by dateadd(day, datediff(day, 0, a.creationdate)/##granularity##*##granularity##, 0)``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached