1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

DECLARE @impossibleBadges TABLE (
  Name VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
);

INSERT INTO @impossibleBadges
/*SELECT 'Tenacious'
UNION
SELECT 'Unsung Hero'
UNION
SELECT 'Census'
UNION
SELECT 'Not a Robot'
UNION*/
SELECT 'Analytical'
UNION
/*SELECT 'Beta'
UNION*/
SELECT 'Documentation Beta'
UNION
SELECT 'Documentation Pioneer'
UNION
SELECT 'Documentation User'
UNION
SELECT 'Reversal'
UNION
SELECT 'Tumbleweed';

WITH Raw0 AS (
  SELECT UserId, Name
  FROM Badges
  GROUP BY UserId, Name
),
Raw AS (
  SELECT
    UserId,
    STRING_AGG(Raw0.Name, ', ') WITHIN GROUP (ORDER BY Raw0.Name)
      AS HavingImpossibleBadges
  FROM Raw0, @impossibleBadges ImpossibleBadges
  WHERE
    Raw0.Name = ImpossibleBadges.Name
  GROUP BY UserId
  HAVING COUNT(*) = (SELECT COUNT(*) FROM @impossibleBadges)
)
SELECT
  Users.Id AS [User Link],
  Users.Reputation,
  Users.LastAccessDate,
  Raw.HavingImpossibleBadges
FROM Users, Raw
WHERE Users.Id = Raw.UserId
ORDER BY Users.Reputation DESC, Users.Id

fork query

edited jan 10

created jan 10 peterh - PLEASE DELETE THIS ACCOUNT