1

Please login or register to vote for this query.

(click on this box to dismiss)

List of user trios with the strongest "friendliness" (friendliness = MIN(count of answers of user1 to the questions of user2, count of answers of user2 to the questions of user1) )

Q&A for users, experts, and developers of the Tezos blockchain project

WITH Raw AS (
  SELECT
    Q.OwnerUserId AS QUserId,
    A.OwnerUserId AS AUserId,
    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
  GROUP BY Q.OwnerUserId, A.OwnerUserId
),
Raw2 AS (
  SELECT
    A.QUserId AS User1,
    A.AUserId AS User2,
    CASE WHEN A.Count > B.Count THEN B.Count ELSE A.Count END AS Count
  FROM Raw A, Raw B
  WHERE
    A.QUserId = B.AUserId
    AND
    A.AUserId = B.QUserId
    AND
    A.QUserId < B.QUserId
)
SELECT TOP 100
  A.User1 AS [User Link],
  B.User1 AS [User Link],
  C.User2 AS [User Link],
  A.Count AS F12,
  B.Count AS F23,
  C.Count AS F13,
  CASE
    WHEN A.Count < B.Count
    THEN
      CASE
        WHEN A.Count < C.Count
        THEN A.Count
        ELSE C.Count
      END
    ELSE
      CASE
        WHEN B.Count < C.Count
        THEN B.Count
        ELSE C.Count
      END
  END AS Count
FROM Raw2 A, Raw2 B, Raw2 C
WHERE
  A.User1 = C.User1
  AND
  A.User2 = B.User1
  AND
  B.User2 = C.User2
  AND
  A.User1 < B.User1
  AND
  A.User2 < B.User2
ORDER BY Count DESC, A.User1, B.User1, C.User1

fork query

edited apr 1

created apr 1 peterh - PLEASE DELETE THIS ACCOUNT