2

Please login or register to vote for this query.

(click on this box to dismiss)

Also shows "created" content type. See http://meta.stackoverflow.com/questions/130648/does-stack-exchange-follow-the-1-rule

Q&A about the site for administrators, end users, developers and designers for Craft CMS

;WITH EditsByUser AS (
    SELECT
          ph.UserId
        , SUM (
            CASE    WHEN ph.PostHistoryTypeId > 3 AND ph.PostHistoryTypeId < 7  -- Edit types
                    OR   ph.PostHistoryTypeId = 24 -- Suggested edit applied
                    THEN 1
                    ELSE 0
            END
        )
        AS NumEdits
        , SUM (
            CASE    WHEN ph.PostHistoryTypeId = 2   -- "Initial body"
                    THEN 1
                    ELSE 0
            END
        )
        AS NumPostsCreated
        --- Classify content that the user has CREATED...
        , SUM (
            CASE    WHEN ph.PostHistoryTypeId = 2   -- "Initial body"
                    AND  p.PostTypeId         = 1   -- Questions
                    THEN 1
                    ELSE 0
            END
        )
        AS NumQuestionsCreated
        , SUM (
            CASE    WHEN ph.PostHistoryTypeId = 2   -- "Initial body"
                    AND  p.PostTypeId         = 2   -- Answers
                    THEN 1
                    ELSE 0
            END
        )
        AS NumAnswersCreated
        , SUM (
            CASE    WHEN ph.PostHistoryTypeId >= 1  -- Initial title, body or tags
                    AND  ph.PostHistoryTypeId <= 3  -- Tests show that just checking body is sufficient.
                    AND  p.PostTypeId         > 2   -- Wikis, etc.
                    THEN 1
                    ELSE 0
            END
        )
        AS NumOtherCreated

    FROM        PostHistory ph
    LEFT JOIN   Posts       p
    ON          ph.PostId   =  p.Id
    GROUP BY
        ph.UserId
)
SELECT
          COUNT (u.Id)          AS NumUsers
        , SUM (
            CASE    WHEN COALESCE (ebu.NumEdits, 0) > 0
                    THEN 1
                    ELSE 0
            END
        )
        AS [Made edits]
        , SUM (
            CASE    WHEN COALESCE (ebu.NumPostsCreated, 0) > 0
                    THEN 1
                    ELSE 0
            END
        )
        AS [Created Posts of any type]
        , SUM (
            CASE    WHEN COALESCE (ebu.NumQuestionsCreated, 0) > 0
                    THEN 1
                    ELSE 0
            END
        )
        AS [Created Questions]
        , SUM (
            CASE    WHEN COALESCE (ebu.NumAnswersCreated, 0) > 0
                    THEN 1
                    ELSE 0
            END
        )
        AS [Created Answers]
        , SUM (
            CASE    WHEN COALESCE (ebu.NumOtherCreated, 0) > 0
                    THEN 1
                    ELSE 0
            END
        )
        AS [Created Wikis, etc.]
        , SUM (
            CASE    WHEN COALESCE (ebu.NumQuestionsCreated, 0) > 0
                    AND  COALESCE (ebu.NumAnswersCreated, 0)   < 1
                    AND  COALESCE (ebu.NumOtherCreated, 0)     < 1
                    THEN 1
                    ELSE 0
            END
        )
        AS [Created ONLY questions]
FROM        Users       u
LEFT JOIN   EditsByUser ebu
ON          u.id        = ebu.UserId

fork query

edited apr 29 12

created apr 29 12 Awesome Poodles