0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH FilteredAnswers AS (
    SELECT DISTINCT
        ParentId,
        OwnerUserId
    FROM
        Posts
    WHERE
        PostTypeId = 2
)
, QuestionAnswerCounts AS (
    SELECT
        ParentId,
        COUNT(*) AS answer_count
    FROM
        FilteredAnswers
    GROUP BY
        ParentId
    HAVING
        COUNT(*) > 8
)
SELECT
    qac.ParentId,
    qac.answer_count,
    COUNT(DISTINCT fa.OwnerUserId) AS unique_answerers,
    SUM(u.Reputation) AS total_reputation
FROM
    QuestionAnswerCounts qac
JOIN
    FilteredAnswers fa ON qac.ParentId = fa.ParentId
JOIN
    Users u ON fa.OwnerUserId = u.Id
GROUP BY
    qac.ParentId, qac.answer_count
ORDER BY
    qac.answer_count DESC;

fork query

edited dec 15 23

created dec 15 23 stevekass