0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

with
  post_age_and_views as (
    select
      id,
      cast(viewcount as float) as viewcount,
      datediff(minute, creationdate, GetDate()) / 1440.0 as age_in_days
    from posts
    where posttypeid = 1
  ),
  stats as (
    select
      cast(max(age_in_days) as float) as max_age,
      cast(count(1) as float) as num_posts,
      cast(max(viewcount) as float) as max_views
    from
      post_age_and_views
  ),
  binned as (
    select
      ceiling((age_in_days / s.max_age) / 0.05) * s.max_age * 0.05 as bin,
      viewcount
    from
      post_age_and_views
        join
      stats s on 1=1
  )
select
  bin,
  avg(viewcount) / max(max_views) as [Average view count (% of max)],
  count(*) / max(num_posts) as [Posts in bin (% of total)]
from
  binned
    join
  stats on 1=1
group by bin
order by bin asc

fork query

edited sep 27 cafce25

created sep 24 14 anonymous