WITH AnsMinusQ_ByMonth AS ( SELECT DATEADD( MONTH, DATEDI...

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

WITH AnsMinusQ_ByMonth AS (
    SELECT
                DATEADD( MONTH, DATEDIFF( MONTH, 0, p.CreationDate ), 0 )   AS postMonth
                /*--- Users can have posts that are way older than they are!!!
                    See http://meta.stackexchange.com/q/190267/148310
                */
                , MIN( CASE WHEN u.CreationDate < p.CreationDate THEN u.CreationDate ELSE p.CreationDate END )
                AS tentJoinDate
                , u.ID
                , SUM( CASE WHEN p.PostTypeId = 2 THEN 1 ELSE 0 END )
                  - SUM( CASE WHEN p.PostTypeId = 1 THEN 1 ELSE 0 END )
                AS AnsMinusQsts
                , COUNT (p.PostTypeId) AS numPosts
    FROM        Users u
    LEFT JOIN   Posts p             ON p.OwnerUserId = u.Id
    WHERE       u.ID > 1
    AND         u.Reputation > ##minReputation:int?100##
    GROUP BY    DATEADD( MONTH, DATEDIFF( MONTH, 0, p.CreationDate ), 0 )
                , u.ID
)
SELECT   top 20
            crrctAmQbyMemMon.correctMembMonth                   AS [Member for (months)]
            , crrctAmQbyMemMon.ID                               AS [User Link]
            , MAX( crrctAmQbyMemMon.AnsMinusQsts )              AS maxAnsMinusQst
            , MIN( crrctAmQbyMemMon.AnsMinusQsts )              AS minAnsMinusQst
            , AVG( 1.0 * crrctAmQbyMemMon.AnsMinusQsts )        AS avgAnsMinusQst
FROM        (
    SELECT      DATEDIFF( MONTH, mmt.correctJoinDate, amq.postMonth ) AS correctMembMonth
                , amq.AnsMinusQsts
                , amq.numPosts
                , amq.ID
    FROM        AnsMinusQ_ByMonth  amq
    JOIN        (
        SELECT      amq2.ID
                    , MIN( amq2.tentJoinDate )    AS correctJoinDate
        FROM        AnsMinusQ_ByMonth  amq2
        GROUP BY    amq2.ID
    )
    mmt
    ON          mmt.ID = amq.ID
    WHERE       amq.postMonth IS NOT NULL
)
AS crrctAmQbyMemMon
GROUP BY    crrctAmQbyMemMon.correctMembMonth
            , crrctAmQbyMemMon.ID
ORDER BY    MAX( crrctAmQbyMemMon.AnsMinusQsts ) DESC

Enter Parameters

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