0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Get count of active users with rep > 9999 who have answered posts
-- Active means they had any post activity in that month
WITH MonthlyData AS (
  SELECT 
    FORMAT(p.CreationDate, 'yyyy-MM') AS YearMonth,
    COUNT(DISTINCT u.Id) AS ActiveUsers
  FROM Users u
  INNER JOIN Posts p ON p.OwnerUserId = u.Id
  WHERE 
    u.Reputation > 9999
    AND EXISTS (
      SELECT 1 
      FROM Posts a 
      WHERE a.OwnerUserId = u.Id 
      AND a.PostTypeId = 2  -- Answers only
    )
    AND p.CreationDate >= DATEADD(MONTH, -12, GETDATE())
    AND p.CreationDate < GETDATE()
  GROUP BY FORMAT(p.CreationDate, 'yyyy-MM')
)

SELECT 
  YearMonth,
  ActiveUsers,
  LAG(ActiveUsers) OVER (ORDER BY YearMonth) AS PreviousMonth,
  CAST(100.0 * (ActiveUsers - LAG(ActiveUsers) OVER (ORDER BY YearMonth)) / 
       LAG(ActiveUsers) OVER (ORDER BY YearMonth) AS DECIMAL(5,2)) AS MonthlyChangePercent
FROM MonthlyData
ORDER BY YearMonth DESC;

fork query

edited nov 1

created nov 1 jon.doe32071