Insaniac answering users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @HighAtoQRatio INT = ##HighAtoQRatio:int?5000##;

WITH PostsByUser AS (
    SELECT      u.ID
                , u.CreationDate
                , u.Reputation
                , 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
            uaqr.ID AS [User Link]
            , uaqr.CreationDate AS [Join Date]
            , uaqr.numAnswers
            , uaqr.numQuests
            , uaqr.Reputation
FROM        (
    SELECT
            --/*
            pbu.numAnswers,
            pbu.numQuests,
            pbu.Reputation,
            --*/
            pbu.ID
            , pbu.CreationDate
            , CASE  WHEN pbu.numQuests = 0
                    THEN pbu.numAnswers
                    ELSE 1.0 * pbu.numAnswers / pbu.numQuests
            END
            AS AnsByQst
    FROM    PostsByUser pbu
)
AS          uaqr
WHERE       uaqr.AnsByQst >= @HighAtoQRatio
ORDER BY    uaqr.ID

Enter Parameters

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