Rarest (but existing) badge pairs

1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

WITH Raw AS (
  SELECT UserId, Name
  FROM Badges
  WHERE TagBased = 0
  GROUP BY UserId, Name
),
Raw2 AS (
  SELECT
    A.UserId,
    A.Name AS BadgeA,
    B.Name AS BadgeB
  FROM Raw A, Raw B
  WHERE
    A.UserId = B.UserId
    AND
    A.Name < B.Name
),
Raw3 AS (
  SELECT BadgeA, BadgeB, COUNT(*) AS Count
  FROM Raw2
  GROUP BY BadgeA, BadgeB
)
SELECT
  Raw3.Count,
  Raw2.BadgeA,
  Raw2.BadgeB,
  Raw2.UserId AS [User Link]
FROM Raw3, Raw2
WHERE
  Raw2.BadgeA = Raw3.BadgeA
  AND
  Raw2.BadgeB = Raw3.BadgeB
  AND
  Raw3.Count <= ##MaxCount:int?100##
ORDER BY Raw3.Count, Raw2.BadgeA, Raw2.BadgeB, Raw2.UserId

Enter Parameters

Options:
Switch to main site
Hold tight while we fetch your results
{"cc_re__cc_re__cc_by_sa_4_0": ["/a>\r\n                <a href=\"https://data.stackexchange.com\">data</a>\r\n                <a href=\"https://stackexchange.com/legal\">legal</a>\r\n                <strong><a href=\"https://meta.stackexchange.com/contact\">contact us</a></strong>\r\n                <strong><a href=\"https://meta.stackexchange.com\">feedback</a></strong>\r\n            </div>\r\n            <div id=\"copyright\">\r\n                site design / logo &copy; 2021 Stack Exchange Inc; \r\n                user contributions licensed under <a href=\"https://creativecommons.org/licenses/by-sa/4.0/\" rel=\"license\">cc by-sa</a>; see the <a href=\"https://stackoverflow.com/help/licensing\">licensing help page</a> for more info\r\n            </div>\r\n            <div id=\"revision\">rev 2021.6.16.81</div>\r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">\r\n            Data Explorer works best with JavaScript enabled</div>\r\n    </noscript>\r\n    <script>\r\n(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){\r\n(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date("], "copyright__copyright__copyright": ["    A.UserId = B.UserId\n    AND\n    A.Name < B.Name\n),\nRaw3 AS (\n  SELECT BadgeA, BadgeB, COUNT(*) AS Count\n  FROM Raw2\n  GROUP BY BadgeA, BadgeB\n)\nSELECT\n  Raw3.Count,\n  Raw2.BadgeA,\n  Raw2.BadgeB,\n  Raw2.UserId AS [User Link]\nFROM Raw3, Raw2\nWHERE\n  Raw2.BadgeA = Raw3.BadgeA\n  AND\n  Raw2.BadgeB = Raw3.BadgeB\n  AND\n  Raw3.Count <= ##MaxCount:int?100##\nORDER BY Raw3.Count, Raw2.BadgeA, Raw2.BadgeB, Raw2.UserId\n\nEnter Parameters\n\nOptions:\nSwitch to main site\nHold tight while we fetch your results"]}