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.1 as bin_1_max,
      max(age_in_days) * 0.2 as bin_2_max,
      max(age_in_days) * 0.3 as bin_3_max,
      max(age_in_days) * 0.4 as bin_4_max,
      max(age_in_days) * 0.5 as bin_5_max,
      max(age_in_days) * 0.6 as bin_6_max,
      max(age_in_days) * 0.7 as bin_7_max,
      max(age_in_days) * 0.8 as bin_8_max,
      max(age_in_days) * 0.9 as bin_9_max,
      max(age_in_days) as bin_10_max
    from
      post_age
  ),
  hist_data as (
    select
      age_in_days,
      avg(cast(viewcount as float) / age_in_days) as mean_views_per_day
    from post_age
      join posts on posts.id = post_age.id
    group by age_in_days
  )
select
  top 100 *
from
  post_age

Enter Parameters

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