Top users sorted by percentage of answers on closed questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ebooks

Q&A for ebook publishers and readers

SELECT
    Id AS [User Link], 
    Reputation,
    (
    SELECT COUNT(*) FROM Posts
    WHERE
        OwnerUserId = Users.Id AND
        PostTypeID = 2
    ) as TotalAnswers,
    (
    SELECT COUNT(*) FROM Posts as aPosts
      LEFT JOIN Posts as qPosts
        ON qPosts.PostTypeID = 1 --Questions
          AND qPosts.ID = aPosts.ParentId
    WHERE
        aPosts.OwnerUserId = Users.Id AND
        aPosts.PostTypeID = 2 AND
        qPosts.ClosedDate IS NOT NULL
    ) / (
    SELECT COUNT(*) + 0.000000001 FROM Posts
    WHERE
        OwnerUserId = Users.Id AND
        PostTypeID = 2
    ) * 100 as PercentageOnClosedQns,
    LastAccessDate
FROM
    Users
WHERE
    Reputation > 20000
ORDER BY
    PercentageOnClosedQns DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results