Views-per-day time distribution

0

Please login or register to vote for this query.

(click on this box to dismiss)

Psychology & Neuroscience Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

with
  post_age as (
    select
      id,
      datediff(minute, creationdate, GetDate()) / 1440.0 as age_in_days
    from posts
    where posttypeid = 1
  ),
  age_stats as (
    select top 1
      age_in_days as max_age,
      percentile_disc(0.5)
          within group (order by age_in_days)
          over (partition by 1)
        as median_age
    from post_age
    order by age_in_days desc
  ),
  bins as (
    select
      max(age_in_days) * 0.05 as hi1,
      max(age_in_days) * 0.10 as hi2,
      max(age_in_days) * 0.15 as hi3,
      max(age_in_days) * 0.20 as hi4,
      max(age_in_days) * 0.25 as hi5,
      max(age_in_days) * 0.30 as hi6,
      max(age_in_days) * 0.35 as hi7,
      max(age_in_days) * 0.40 as hi8,
      max(age_in_days) * 0.45 as hi9,
      max(age_in_days) * 0.50 as hi10,
      max(age_in_days) * 0.55 as hi11,
      max(age_in_days) * 0.60 as hi12,
      max(age_in_days) * 0.65 as hi13,
      max(age_in_days) * 0.70 as hi14,
      max(age_in_days) * 0.75 as hi15,
      max(age_in_days) * 0.80 as hi16,
      max(age_in_days) * 0.85 as hi17,
      max(age_in_days) * 0.90 as hi18,
      max(age_in_days) * 0.95 as hi19,
      max(age_in_days) as hi20
    from
      post_age
  ),
  stats as (
    select
      age_in_days,
      avg(cast(viewcount as float) / age_in_days) as mvpd
    from post_age
      join posts on posts.id = post_age.id
    group by age_in_days
  ),
  binned as (
    select
      case when age_in_days between 0 and b.hi1 then s.mvpd else null 
        end as bin1,
      case when age_in_days between b.hi1 and b.hi2 then s.mvpd else null 
        end as bin2,
      case when age_in_days between b.hi2 and b.hi2 then s.mvpd else null 
        end as bin3,
      case when age_in_days between b.hi3 and b.hi2 then s.mvpd else null 
        end as bin4,
      case when age_in_days between b.hi4 and b.hi2 then s.mvpd else null 
        end as bin5,
      case when age_in_days between b.hi5 and b.hi2 then s.mvpd else null 
        end as bin6,
      case when age_in_days between b.hi6 and b.hi2 then s.mvpd else null 
        end as bin7,
      case when age_in_days between b.hi7 and b.hi2 then s.mvpd else null 
        end as bin8,
      case when age_in_days between b.hi8 and b.hi2 then s.mvpd else null 
        end as bin9,
      case when age_in_days between b.hi9 and b.hi2 then s.mvpd else null 
        end as bin10,
      case when age_in_days between b.hi10 and b.hi2 then s.mvpd else null 
        end as bin11,
      case when age_in_days between b.hi11 and b.hi2 then s.mvpd else null 
        end as bin12,
      case when age_in_days between b.hi12 and b.hi2 then s.mvpd else null 
        end as bin13,
      case when age_in_days between b.hi13 and b.hi2 then s.mvpd else null 
        end as bin14,
      case when age_in_days between b.hi14 and b.hi2 then s.mvpd else null 
        end as bin15,
      case when age_in_days between b.hi15 and b.hi2 then s.mvpd else null 
        end as bin16,
      case when age_in_days between b.hi16 and b.hi2 then s.mvpd else null 
        end as bin17,
      case when age_in_days between b.hi17 and b.hi2 then s.mvpd else null 
        end as bin18,
      case when age_in_days between b.hi18 and b.hi2 then s.mvpd else null 
        end as bin19,
      case when age_in_days between b.hi19 and b.hi2 then s.mvpd else null 
        end as bin20
    from
      stats s
        join
      bins b on 1=1
  )
select
  top 100 *
from
  binned

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results