For a given month, what percentage of users have a "High" A/Q ratio?
Q&A for professional and enthusiast programmers
DECLARE @HighAtoQRatio FLOAT = ##HighAtoQRatio:float?3.0##; WITH PostsByUser AS ( SELECT u.ID , u.CreationDate , u.Reputation -- Future use , SUM( CASE WHEN p.PostTypeId = 1 THEN 1 ELSE 0 END ) AS numQuests , SUM( CASE WHEN p.PostTypeId = 2 THEN 1 ELSE 0 END ) AS numAnswers FROM Users u LEFT JOIN Posts p ON p.OwnerUserId = u.Id WHERE u.ID > 0 GROUP BY u.ID , u.CreationDate , u.Reputation ) SELECT DATEADD( MONTH, DATEDIFF( MONTH, 0, uaqr.CreationDate ), 0 ) AS Date , 100.0 * SUM( CASE WHEN uaqr.AnsByQst >= @HighAtoQRatio THEN 1 ELSE 0 END ) / COUNT (uaqr.ID) AS [Ans/Qst %] FROM ( SELECT /*pbu.numAnswers, pbu.numQuests, */ pbu.ID , pbu.CreationDate , CASE WHEN pbu.numQuests = 0 THEN pbu.numAnswers ELSE (1.0 * pbu.numAnswers) / (1.0 * pbu.numQuests) END AS AnsByQst FROM PostsByUser pbu ) AS uaqr GROUP BY DATEADD( MONTH, DATEDIFF( MONTH, 0, uaqr.CreationDate ), 0 ) ORDER BY DATEADD( MONTH, DATEDIFF( MONTH, 0, uaqr.CreationDate ), 0 )