1

Please login or register to vote for this query.

(click on this box to dismiss)

Lists all users which answered the same questions as I did and compare the scores

Q&A for active researchers, academics and students of physics

WITH cte AS (
    SELECT oa.OwnerUserId
         , COUNT(*) AS CA
         , COUNT(CASE WHEN qu.AcceptedAnswerId = ma.Id THEN 1 END) AS MYA
         , COUNT(CASE WHEN qu.AcceptedAnswerId = oa.Id THEN 1 END) AS OUA
         , SUM(ma.Score) AS MYS
         , SUM(oa.Score) AS OUS
    FROM       Posts AS ma
    INNER JOIN Posts AS qu ON ma.ParentId = qu.Id
    INNER JOIN Posts AS oa ON qu.Id = oa.ParentId AND ma.Id <> oa.Id
    WHERE ma.PostTypeId = 2
    AND   ma.OwnerUserId = ##UserId##
    AND   oa.OwnerUserId IS NOT NULL
    GROUP BY oa.OwnerUserId
    HAVING COUNT(*) >= ##MinCommonAnswers:int?5##
)
SELECT OwnerUserId AS [User Link]
     , CA AS [Common Answers]
     , MYA AS [MY Accepted]
     , OUA AS [OU Accepted]
     , CASE WHEN MYA > 0 AND OUA > 0 THEN ROUND(1.0 * MYA / OUA, 1) END AS [Delta 1]
     , MYS AS [MY Score]
     , OUS AS [OU Score]
     , CASE WHEN MYS > 0 AND OUS > 0 THEN ROUND(100.0 * MYS / OUS, 1) END AS [Delta 2]
     , CONCAT('query://969316?UserId=', ##UserId##, '&OtherUserId=', OwnerUserId, '|Details') AS [Details]
FROM cte

fork query

edited jan 22 19

created jan 21 19 Salman.Arshad