1

Please login or register to vote for this query.

(click on this box to dismiss)

Вопросы и ответы о сайте для программистов

WITH Raw AS (
  SELECT
    Q.OwnerUserId AS QId,
    A.OwnerUserId AS AId,
    COUNT(*) AS Count
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    A.OwnerUserId > 0
    AND
    Q.OwnerUserId > 0
    AND
    A.OwnerUserId <> Q.OwnerUserId
    AND
    Q.CommunityOwnedDate IS NULL
    AND
    A.CommunityOwnedDate IS NULL
  GROUP BY Q.OwnerUserId, A.OwnerUserId
)
SELECT
  Hin.QId AS [User Link],
  Hin.AId AS [User Link],
  Hin.Count AS HinCount,
  Her.Count AS HerCount
FROM Raw Hin, Raw Her
WHERE
  Hin.AId = Her.QId
  AND
  Hin.QId = Her.AId
  AND
  Hin.QId > Hin.AId
ORDER BY
  CASE
    WHEN Hin.Count > Her.Count THEN Her.Count
    ELSE Hin.Count
  END DESC, Hin.QId, Hin.AId

fork query

edited sep 5 19

created sep 5 19 peterh (2)