trending tags recent time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

with tag_stats as (
  select 
    p.id as postId, 
    p.viewCount, 
    p.score, 
    p.creationDate, 
    DATEADD(MONTH, DATEDIFF(MONTH,0,p.creationDate), 0) as creationMonth,
    t.tagName
  from Posts p
  inner join PostTags pt on pt.postid = p.id
  inner join Tags t on pt.tagId = t.id
),
tag_aggregated_stats as (
  select 
     tagName, creationMonth,
     sum(viewCount) as total_views, 
     sum(score) as total_score,
     count(distinct postId) as total_posts
     from tag_stats
  where creationDate > DATEADD(year,-1,GETDATE()) --'2019-10-01'  
  group by tagName, creationMonth
),
tags_trending as (
  select 
    tagName, 
    creationMonth,
    lag(total_posts) over(order by creationMonth asc) as prev_month_total_posts, 
    total_posts,
    total_posts - lag(total_posts) over(order by creationMonth asc) as total_posts_diff
  from tag_aggregated_stats
)
select tagName, sum(total_posts_diff) posts_over_month_differences
from tags_trending
group by tagName
order by posts_over_month_differences desc

Enter Parameters

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