SELECT c.Total AS [Careers Posts], a.Total AS [Total Post...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

SELECT
   c.Total AS [Careers Posts],
   a.Total AS [Total Posts],
   CONVERT(varchar(10), ROUND(100.0 * (COALESCE(CAST(c.Total AS float), 0) / CAST(a.Total AS float)), 2)) + '%' AS [Monthly %],
   a.Year,
   a.Month
FROM
  (
    SELECT
      COUNT(*) Total,
      YEAR(Posts.CreationDate) AS [Year],
      MONTH(Posts.CreationDate) AS [Month]
    FROM
      Posts
    WHERE
      Posts.PostTypeId = 1
    GROUP BY
      YEAR(Posts.CreationDate),
      MONTH(Posts.CreationDate)
  ) AS a
LEFT JOIN
  (
    SELECT
      COUNT(*) Total,
      YEAR(Posts.CreationDate) AS [Year],
      MONTH(Posts.CreationDate) AS [Month]
    FROM
      PostTags
    JOIN
      Tags ON PostTags.TagId = Tags.Id
    JOIN
      Posts ON PostTags.PostId = Posts.Id
    WHERE
      Tags.TagName = 'careers' AND
      Posts.PostTypeId = 1
    GROUP BY
      YEAR(Posts.CreationDate),
      MONTH(Posts.CreationDate)
  ) AS c ON a.Year = c.Year AND a.Month = c.Month
ORDER BY
  a.Year DESC, a.Month DESC

Enter Parameters

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