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


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
  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) 
         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('"', 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, 
    INTO #top50

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

Enter Parameters

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