Close vote time 5 percentile

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

with dates as (
  select 

    year(creationdate) as year,
    datediff(second, creationdate, closeddate )/60. as life_time
    

  from Posts
  where 
    score < 0
    and closeddate is not Null
    and datediff(minute, creationdate, closeddate )*1.0 < 365*24*60
    --and year(creationdate)>=2009
  
)

select DISTINCT 

  avg(year) over (partition by year) as year,
  -- AVG(life_time) OVER(PARTITION BY year) as life_time,
  PERCENTILE_CONT(0.05)
      WITHIN GROUP 
      (ORDER BY life_time)
      OVER 
      (PARTITION BY year)
      AS q05_close_time_minute,
  PERCENTILE_CONT(0.1)
      WITHIN GROUP 
      (ORDER BY life_time)
      OVER 
      (PARTITION BY year)
      AS q10_close_time_minute
from dates
ORDER BY 
    year

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results