1

Please login or register to vote for this query.

(click on this box to dismiss)

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 )

fork query

edited apr 25 15

created apr 25 15 Brock Adams