/* top 50 users by rep network-wide and their top post in...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Aviation Meta

Q&A about the site for aircraft pilots, mechanics, and enthusiasts

/* 
  top 50 users by rep network-wide
  and their top post including bounties
  and the site with the highest number of gold badges
  
  inspired by rene
  https://data.stackexchange.com/stackapps/query/1836111
  
  this is not simple
  but the point is not "hey, look how easy this is"
  it's "how hard would this be without these new views/procedures?"
*/

/* 
  first, the TOP 50 users, and their list of sites,
  and the site with the highest rep (which is where
  we'll grab their displayname from) 
*/

;WITH agg AS
(
  SELECT TOP (50) 
         account_id, 
         total_reputation   = SUM(reputation),
         total_sites        = COUNT(*),
         total_questions    = SUM(question_count),
         total_answers      = SUM(answer_count),
         max_reputation     = MAX(reputation),
         site_list_json     = '[' 
                              + STRING_AGG
                                (
                                  CONCAT_WS
                                  ( '":"',
                                    CONCAT('"', site_id), 
                                    CONCAT(reputation, '"')
                                  ), ','
                                ) WITHIN GROUP (ORDER BY reputation DESC)
                              + ']'
    FROM dbo.sede_users
   GROUP BY account_id
   ORDER BY total_reputation DESC
)
SELECT * INTO #top50 FROM agg;

SELECT * FROM #top50
--CROSS APPLY OPENJSON(site_list_json);

/*SELECT agg.account_id, 
       u.site_id,
       u.user_id,
       
    INTO #top50

FROM agg 
  INNER JOIN dbo.sede_users AS u
  ON agg.max_reputation = u.reputation;
 */

Enter Parameters

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