0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Tridion developers and administrators

WITH main AS (SELECT * FROM Posts WHERE PostTypeId = 1),

sub AS (SELECT datepart(year,Posts.CreationDate) as post_year, 
               datepart(month,Posts.CreationDate) as post_month,
               Posts.Id,
               count(*) as total
        FROM Posts WHERE PostTypeId = 1
        GROUP BY datepart(year,Posts.CreationDate), 
                 datepart(month,Posts.CreationDate),
                 Posts.Id)
              

SELECT post_year,
       post_month,
       tag,
       month_count, -- optional, not needed in main table
       total,
       SUM(subquery.month_count) OVER(PARTITION BY tag
                                 ORDER BY tag
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as total_count
FROM (

  SELECT datepart(year,main.CreationDate) as post_year, 
         datepart(month,main.CreationDate) as post_month, 
         tags.TagName as tag, 
         sub.total as total,
         COUNT(*) as month_count
         
  FROM PostTags as pt
    INNER JOIN main
    ON pt.PostId = main.Id
    INNER JOIN sub
    ON pt.PostId = sub.Id
    INNER JOIN Tags as tags 
    ON pt.TagId = tags.Id
    
  WHERE tags.TagName in ('reactjs', 'react-native', 'swift',
                         'python', 'javascript', 'java')
  
  GROUP BY datepart(year,main.CreationDate), 
           datepart(month,main.CreationDate), 
           tags.TagName,
           sub.total
           
     ) AS subquery
     
     
GROUP BY post_year, 
         post_month, 
         tag,
         month_count,
         total
ORDER BY post_year,
         post_month

fork query

edited may 20

created may 18 anonymous