Users with Accepted Answer Rate

0

Please login or register to vote for this query.

(click on this box to dismiss)

All users (with > 10 answers) with their accepted answer rate. Accepted Answers / Total Answers (only includes questions which actually have an accepted answer)

Stack Overflow

Q&A for programmers

SELECT
    'You'
    , subqAllAnswers.[DisplayName]
    , subqAllAnswers.Reputation
    , subqAllAcceptedAnswers.Total [Total Accepted Answers]
    , subqAllAnswers.Total [Total Answers]
    , ROUND( 
            CONVERT(FLOAT, subqAllAcceptedAnswers.Total) / CONVERT(FLOAT, subqAllAnswers.Total) 
            , 2
            ) * 100'Accepted Answer Rate'
FROM
  (
  SELECT
      COUNT(PPP.Id) 'Total'
      , UUU.DisplayName
      , UUU.Reputation
      , UUU.Id
  FROM
              Posts PPP
  INNER JOIN  Users UUU ON PPP.OwnerUserId = UUU.Id
  INNER JOIN  Posts PPR ON PPP.ParentId    = PPR.Id
  WHERE
        PPR.AcceptedAnswerId IS NOT NULL
  AND UUU.Id = ##UserId##
  GROUP BY
    UUU.DisplayName
    , UUU.Reputation
    , UUU.Id
    )subqAllAnswers
LEFT JOIN
  (
  SELECT
      COUNT(PPP.Id) 'Total'
      , UUU.DisplayName
      , UUU.Id
  FROM
              Posts PPP              
  INNER JOIN  Users UUU ON PPP.OwnerUserId = UUU.Id
  LEFT JOIN   Posts PPR ON PPP.ParentId    = PPR.Id
  WHERE
      PPR.AcceptedAnswerId = PPP.Id
  AND PPR.OwnerUserId      <> PPP.OwnerUserId
  AND UUU.Id = ##UserId##
  GROUP BY
    UUU.DisplayName  
    , UUU.Id
  )subqAllAcceptedAnswers ON subqAllAnswers.Id = subqAllAcceptedAnswers.Id
 
 UNION 
 
SELECT
    'Someone Else'
    , subqAllAnswers.[DisplayName]
    , subqAllAnswers.Reputation
    , subqAllAcceptedAnswers.Total [Total Accepted Answers]
    , subqAllAnswers.Total [Total Answers]
    , ROUND( 
            CONVERT(FLOAT, subqAllAcceptedAnswers.Total) / CONVERT(FLOAT, subqAllAnswers.Total) 
            , 2
            ) * 100'Accepted Answer Rate'
FROM
  (
  SELECT
      COUNT(PPP.Id) 'Total'
      , UUU.DisplayName
      , UUU.Reputation
      , UUU.Id
  FROM
              Posts PPP
  INNER JOIN  Users UUU ON PPP.OwnerUserId = UUU.Id
  INNER JOIN  Posts PPR ON PPP.ParentId    = PPR.Id
  WHERE
        PPR.AcceptedAnswerId IS NOT NULL
  GROUP BY
    UUU.DisplayName
    , UUU.Reputation
    , UUU.Id
    )subqAllAnswers
LEFT JOIN
  (
  SELECT
      COUNT(PPP.Id) 'Total'
      , UUU.DisplayName
      , UUU.Id
  FROM
              Posts PPP              
  INNER JOIN  Users UUU ON PPP.OwnerUserId = UUU.Id
  LEFT JOIN   Posts PPR ON PPP.ParentId    = PPR.Id
  WHERE
      PPR.AcceptedAnswerId = PPP.Id
  AND PPR.OwnerUserId      <> PPP.OwnerUserId
  GROUP BY
    UUU.DisplayName  
    , UUU.Id
  )subqAllAcceptedAnswers ON subqAllAnswers.Id = subqAllAcceptedAnswers.Id
  WHERE
    subqAllAnswers.Total > 10
ORDER BY 1 DESC, 6 DESC, 5 DESC, 4 DESC

Enter Parameters

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