Get my badge count on each site

0

Please login or register to vote for this query.

(click on this box to dismiss)

Earth Science

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

SELECT account_id, site_id, user_id
  INTO #MyUsers
  FROM dbo.sede_users
  WHERE account_id = ##AccountId:int##;

EXEC dbo.sede_ineachdb @SQLCommand = N'

SELECT Url, 
       [bronze badges] = COALESCE([1], 0),
       [silver badges] = COALESCE([2], 0),
       [gold badges]   = COALESCE([3], 0)
FROM 
(
  SELECT u.Id, Url = CONCAT([$site_url$], ''/users/'', 
         u.Id, ''/?tab=badges&sort=class|'', [$site_name$])
    FROM dbo.Users AS u
   INNER JOIN #MyUsers AS mu
      ON mu.user_id = u.Id
   WHERE u.AccountId = mu.account_id
     AND mu.site_id = [$site_id$]
) AS src
OUTER APPLY
    (
      SELECT class, BadgeCount = COUNT(*)
        FROM dbo.Badges AS b
       WHERE b.UserId = src.Id
       GROUP BY class
    ) AS b
 PIVOT
 (
   MAX(BadgeCount) 
   FOR [class] IN ([1],[2],[3])
 ) AS p;';

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results