WITH Questions AS (SELECT OwnerUserId, ParentId, ID FROM ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

WITH Questions
     AS (SELECT OwnerUserId,
                ParentId,
                ID
         FROM   Posts
         WHERE  PostTypeId = 1),
     QuestionsGrouped
     AS (SELECT OwnerUserId,
                COUNT(*) AS Cnt
         FROM   Questions
         GROUP  BY OwnerUserId),
     Answers
     AS (SELECT OwnerUserId,
                ParentId,
                ID
         FROM   Posts
         WHERE  PostTypeId = 2),
     AnswersGrouped
     AS (SELECT OwnerUserId,
                COUNT(*) AS Cnt
         FROM   Answers
         GROUP  BY OwnerUserId),
     Top100
     AS (SELECT TOP 100 a.OwnerUserId        AS A_UserId,
                        q.OwnerUserId        AS Q_UserId,
                        COUNT(DISTINCT q.ID) AS Cnt
         FROM   Questions q
                INNER JOIN Answers a
                  ON a.ParentId = q.ID
         WHERE  a.OwnerUserId <> q.OwnerUserId
         GROUP  BY a.OwnerUserId,
                   q.OwnerUserId
         ORDER  BY Cnt DESC)
SELECT T.Cnt,
       A_UserId,
       UA.DisplayName,
       AG.Cnt                 AS AnswersGiven,
       UA.Reputation,
       Q_UserId,
       QA.DisplayName,
       QG.Cnt                 AS QuestionsAsked,
       100.0 * T.Cnt / QG.Cnt AS PercentAnsweredByThisAnswerer
FROM   Top100 T
       JOIN Users UA
         ON UA.Id = A_UserId
       JOIN Users QA
         ON QA.Id = Q_UserId
       join QuestionsGrouped QG
         ON QG.OwnerUserId = Q_UserId
       join AnswersGrouped AG
         ON AG.OwnerUserId = A_UserId
ORDER  BY PercentAnsweredByThisAnswerer DESC  

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached