0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for engineers, scientists, programmers, and computing professionals interested in quantum computing

/* 
  top 50 users by rep network-wide
  
  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 to inspire you to craft your own queries, and:
  "how hard would this be without these new views and procedure?"
*/

CREATE TABLE #Top50
(
  account_id          int NOT NULL PRIMARY KEY,
  display_name        nvarchar(40),
  site_count          int,
  total_questions     int,
  total_answers       int,
  network_reputation  int,
  hi_site_rep         int, 
  hi_rep_site_id      int,
  hi_rep_site_profile nvarchar(2048),
  hi_rep_post         nvarchar(2048),
  hi_post_score       int,
  INDEX IX_Top50_Site (hi_rep_site_id)
);

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

;WITH agg AS
(
  SELECT *, site_rank = RANK() OVER 
            (PARTITION BY account_id ORDER BY reputation DESC)
  FROM dbo.sede_users
)
  INSERT #Top50
  (
    account_id,
    site_count,
    total_questions,
    total_answers,
    network_reputation,
    hi_site_rep, 
    hi_rep_site_id    
  )
  SELECT TOP (50) 
         account_id,
         site_count         = COUNT(*),
         total_questions    = SUM(question_count),
         total_answers      = SUM(answer_count),
         network_reputation = SUM(reputation),
         hi_site_rep        = MAX(reputation),
         hi_rep_site_id     = MAX(CASE site_rank WHEN 1 THEN site_id END)
    FROM agg
   GROUP BY account_id
   ORDER BY hi_site_rep DESC;

/*
  Next, go into each DB and grab their display name, 
  user profile link, their highest scoring post, and
  its score, and update #Top50:
*/

   EXEC dbo.sede_ineachdb @CollectResultsForMe = 0,
                          @SQLCommand = N';
   WITH src AS
   (
      SELECT u.AccountId,
             UserId = u.Id,
             u.DisplayName, 
             PostLink = CONCAT
                        (
                          [$site url$],
                          ''/q/'', 
                          COALESCE(pinf.ParentId, pinf.PostId),
                          ''#'', pinf.PostId, 
                          ''|'', pinf.Title
                        ),
             pinf.Score
        FROM Users AS u
       CROSS APPLY 
       (
         SELECT TOP (1) 
                PostId = p.Id, 
                Title = COALESCE(p.Title, q.Title), 
                ParentId = q.Id, 
                p.Score
           FROM Posts AS p
           LEFT OUTER JOIN Posts AS q
             ON p.ParentId = q.Id
          WHERE p.OwnerUserId = u.Id
            AND p.PostTypeId IN (1,2)
            AND q.PostTypeId = 1
          ORDER BY p.Score DESC
       ) AS pinf
    )
    UPDATE t50 SET
           t50.display_name        = src.DisplayName,
           t50.hi_rep_site_profile = CONCAT
                                     ([$site url$], ''/u/'', src.UserId, 
                                      ''|'', [$long name$]),
           t50.hi_rep_post         = src.PostLink,
           t50.hi_post_score       = src.Score
      FROM #Top50 AS t50
     INNER JOIN src 
        ON t50.account_id = src.AccountId 
     WHERE t50.hi_rep_site_id = [$site id$];';
     
/* 
  Finally, get the important information,
  and an account profile link 
*/

     SELECT account_profile = CONCAT('https://stackexchange.com/u/', 
                                     account_id, '|', display_name),
            network_reputation,
            site_count,
            total_questions, 
            total_answers,
            hi_rep_site_profile,
            hi_rep_post,
            hi_post_score
       FROM #Top50
      ORDER BY network_reputation DESC;

fork query

created apr 18 Aaron Bertrand