0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

SELECT post_year,
       post_month,
       tag,
       month_count, -- optional, not needed in main table
       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,posts.CreationDate) as post_year, 
         datepart(month,posts.CreationDate) as post_month, 
         tags.TagName as tag, 
         COUNT(*) as month_count
         
  FROM PostTags as pt
    INNER JOIN POSTS as posts 
    ON pt.PostId = posts.Id
    INNER JOIN Tags as tags 
    ON pt.TagId = tags.Id
    
  WHERE tags.TagName in ('reactjs', 'react-native', 'swift',
                         'python', 'javascript', 'java')
        AND posts.PostTypeId = 1
  
  GROUP BY datepart(year,posts.CreationDate), 
           datepart(month,posts.CreationDate), 
           tags.TagName
           
     ) AS subquery
     
     
GROUP BY post_year, 
         post_month, 
         tag,
         month_count
ORDER BY post_year,
         post_month

fork query

edited may 19 22

created may 18 22 anonymous