0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

SELECT
  sq.month 'Month',
  CASE sq.month
      WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()),1)
      THEN DATEDIFF(d, sq.month, GETDATE())
      ELSE DATEDIFF(d, sq.month, DATEADD(m, 1, sq.month))
    END 'Days',
  sq.tag 'Tag',
  sq.posts 'Posts'  
FROM
  (
  SELECT
    DATEFROMPARTS(YEAR(p.creationdate),MONTH(p.creationdate),1) 'Month',
    t.tagname 'Tag',
    COUNT(p.id) 'Posts'
  FROM
    posts p
  INNER JOIN
    posttags pt ON 
    (pt.postid = p.parentid)
  INNER JOIN
    tags t ON
    (t.id = pt.tagid)
  GROUP BY
    DATEFROMPARTS(YEAR(p.creationdate),MONTH(p.creationdate),1),
    t.tagname
 ) sq
WHERE
  sq.posts > 50 AND
  sq.month >= '1/1/2015'
ORDER BY
  sq.month DESC

fork query

edited feb 23 18

created feb 23 18 anonymous