0

Please login or register to vote for this query.

(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)
  / count(*) [Percent of answers added on confirmed dupes]
, 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)

fork query

edited feb 11 16

created feb 11 16 Deduplicator