0

Please login or register to vote for this query.

(click on this box to dismiss)

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

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 > 30000
ORDER BY
    PercentageOnClosedQns DESC;

fork query

edited nov 9 anonymous

created jun 2 15 curiousdannii