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_and_views as (
    select
      id,
      viewcount as views,
      datediff(minute, creationdate, GetDate()) / 1440.0 as age_in_days
    from posts
    where posttypeid = 1
  ),
  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_and_views
  ),
--  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 views else null
        end as [0.00-0.05],
      case when age_in_days between b.hi1 and b.hi2 then views else null
        end as [0.05-0.10],
      case when age_in_days between b.hi2 and b.hi2 then views else null
        end as [0.10-0.15],
      case when age_in_days between b.hi3 and b.hi2 then views else null
        end as [0.15-0.20],
      case when age_in_days between b.hi4 and b.hi2 then views else null
        end as [0.20-0.25],
      case when age_in_days between b.hi5 and b.hi2 then views else null
        end as [0.25-0.30],
      case when age_in_days between b.hi6 and b.hi2 then views else null
        end as [0.30-0.35],
      case when age_in_days between b.hi7 and b.hi2 then views else null
        end as [0.35-0.40],
      case when age_in_days between b.hi8 and b.hi2 then views else null
        end as [0.40-0.45],
      case when age_in_days between b.hi9 and b.hi2 then views else null
        end as [0.45-0.50],
      case when age_in_days between b.hi10 and b.hi2 then views else null
        end as [0.50-0.55],
      case when age_in_days between b.hi11 and b.hi2 then views else null
        end as [0.55-0.60],
      case when age_in_days between b.hi12 and b.hi2 then views else null
        end as [0.60-0.65],
      case when age_in_days between b.hi13 and b.hi2 then views else null
        end as [0.65-0.70],
      case when age_in_days between b.hi14 and b.hi2 then views else null
        end as [0.70-0.75],
      case when age_in_days between b.hi15 and b.hi2 then views else null
        end as [0.75-0.80],
      case when age_in_days between b.hi16 and b.hi2 then views else null
        end as [0.80-0.85],
      case when age_in_days between b.hi17 and b.hi2 then views else null
        end as [0.85-0.90],
      case when age_in_days between b.hi18 and b.hi2 then views else null
        end as [0.90-0.95],
      case when age_in_days between b.hi19 and b.hi2 then views else null
        end as [0.95-1.00]
    from
      post_age_and_views
        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