1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

SELECT *,
  SUBSTRING((
    SELECT DISTINCT ', ' + Name
    FROM Badges b1
    WHERE NOT EXISTS
     (SELECT * FROM Badges b2
      WHERE UserId = subq.[User ID]
        AND b2.Name = b1.Name)
    AND ASCII(CAST(LEFT(b1.Name, 1) AS CHAR)) BETWEEN 65 AND 90
    ORDER BY ', ' + Name
    FOR XML PATH('')), 3, 2147483647) AS [Unattained Badges]  
FROM
 (SELECT TOP 10
   u.Id AS [User ID],
   u.DisplayName AS [User Name],
   COUNT(DISTINCT b.Name) AS [Distinct Badges],
   COUNT(b.Id) AS [Total Badges]
  FROM Badges b
  JOIN Users u
  ON u.Id = b.UserId
  WHERE ASCII(CAST(LEFT(b.Name, 1) AS CHAR)) BETWEEN 65 AND 90
  GROUP BY u.Id, u.DisplayName
  ORDER BY [Distinct Badges] DESC) subq;

fork query

edited jan 2 Steve.Chambers

created jan 1 anonymous