Replicating the whole network profile account row for a single 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

;WITH src AS 
(
  SELECT su.user_id, 
         su.reputation,
         su.question_count,
         su.answer_count
    FROM dbo.sede_users AS su
   INNER JOIN dbo.sede_sites AS ss
      ON su.site_id = ss.site_id
   WHERE su.account_id = ##AccountId:int##
     AND ss.database_name = DB_NAME()
)
SELECT reputation,
       question_count, 
       answer_count,
       [bronze badges] = [1],
       [silver badges] = [2],
       [gold badges]   = [3]
  FROM src
 OUTER APPLY
       (
         SELECT class, BadgeCount = COUNT(*)
           FROM dbo.Badges AS b
          WHERE b.UserId = src.user_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