Number of posts per one active user - with some tags excluded

0

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

;with acounts_cte AS (
SELECT
  eomonth(a.CreationDate) AS Cmonth,
  COUNT(DISTINCT a.Id) AS Acount
  FROM Posts q INNER JOIN Posts a ON a.ParentId=q.Id
  WHERE NOT((q.Tags Like '%<##tag1?big-list##>%') OR (q.Tags Like '%<##tag2?soft-question##>%'))
  GROUP BY eomonth(a.CreationDate)
), qcounts_cte AS (
SELECT
  eomonth(q.CreationDate) AS Cmonth,
  COUNT(DISTINCT q.Id) AS QCount
  FROM Posts q
  WHERE NOT((q.Tags Like '%<##tag1##>%') OR (q.Tags Like '%<##tag2##>%'))
  GROUP BY eomonth(q.CreationDate)
), counts_cte AS (
SELECT
  q.Cmonth AS CMonth,
  CAST(q.QCount+a.ACount AS Integer) AS PCount,
  q.Qcount AS Qcount,
  a.Acount AS Acount
FROM acounts_cte a INNER JOIN qcounts_cte q ON a.Cmonth=q.Cmonth
), usercounts_cte AS 
(
SELECT eomonth(p.CreationDate) AS Umonth,
  CAST(COUNT(DISTINCT p.OwnerUserId)+COUNT(DISTINCT p.OwnerDisplayName) AS Integer) AS UCount
FROM Posts p
---counting users who made at least one post
GROUP BY eomonth(p.CreationDate)
)

SELECT
  Cmonth,
  (CAST(PCount AS decimal))/(CAST(UCount AS decimal)) AS [Posts per user],
  (CAST(QCount AS decimal))/(CAST(UCount AS decimal)) AS [Questions per user],
  (CAST(ACount AS decimal))/(CAST(UCount AS decimal)) AS [Answers per user]
FROM counts_cte INNER JOIN usercounts_cte on Cmonth=Umonth
ORDER BY Cmonth

Enter Parameters

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