Question age distribution and average views

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

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) 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

Enter Parameters

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