Users With A Combination Of 3 Badges

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

WITH MultiBadgeHolders AS 
(
 SELECT UserId,Name,Date From Badges
 WHERE UserId IN 
 (
  SELECT UserId FROM Badges WHERE Name='##BadgeName1##'
  INTERSECT
  SELECT UserId FROM Badges WHERE Name='##BadgeName2##'
  INTERSECT
  SELECT UserId FROM Badges WHERE Name='##BadgeName3##'
 )
 AND Name IN ('##BadgeName1##','##BadgeName2##','##BadgeName3##') 
),
AllHoldersBadges AS 
(
 SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId,Name ORDER BY Date ASC) AS Row 
 FROM MultiBadgeHolders
),
Results AS
(
 SELECT UserId,Name,Date, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) AS Row 
 FROM AllHoldersBadges WHERE AllHoldersBadges.Row=1
)
SELECT UserId AS [User Link],Name AS 'Last Badge Name',Date 
FROM Results 
WHERE Results.Row=1
ORDER BY Date ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached