StackOverflow Summary

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

DECLARE @NetAccountID AS INTEGER
SET @NetAccountID = ##StackExchangeNetworkProfileID## 

CREATE TABLE #output
(
   id                 INTEGER,
   displayname        NVARCHAR(max),
   rep                INTEGER,
   upvotes            INTEGER,
   downvotes          INTEGER,
   views              INTEGER,
   creationdate       DATE,
   lastseen           DATE,
   location           NVARCHAR(max),
   maxscore           INTEGER,
   minscore           INTEGER,
   avgscore           INTEGER,
   totalnegscore      INTEGER,
   totalposscore      INTEGER,
   maxfavoritecount   INTEGER,
   totalfavoritecount INTEGER,
   TotalCommentScore  INTEGER,
   MaxCommentScore    INTEGER,
   TotalBadges        INTEGER,
   GoldBadges         NVARCHAR(max),
   SilverBadges       NVARCHAR(max),
   BronzeBadges       NVARCHAR(max)
);  

-- Details from users table and post table
-- Other tables can be included to this query but then query will be slow.

INSERT INTO #output
            (id, displayname, rep, upvotes, downvotes, views, creationdate, lastseen, location,
             maxscore, minscore, avgscore, totalnegscore, totalposscore, maxfavoritecount, totalfavoritecount)
SELECT    Max(users.id)                       AS 'ID',
          Max(users.displayname)              AS 'DisplayName',
          Max(ISNULL(users.reputation, 0))    AS 'Rep',
          Max(ISNULL(users.upvotes, 0))       AS 'UpVotes',
          Max(ISNULL(users.downvotes, 0))     AS 'Downvotes',
          Max(ISNULL(users.views, 0))         AS 'Views',
          Max(users.creationdate)             AS 'CreationDate',
          Max(users.lastaccessdate)           AS 'LastSeen',
          Max(users.location)                 AS 'Location',
          Max(ISNULL(posts.score, 0))         AS 'MaxScore',
          Min(ISNULL(posts.score, 0))         AS 'MinScore',
          Avg(ISNULL(posts.score, 0))         AS 'AvgScore',
          Sum(ISNULL((CASE WHEN posts.score < 0 THEN posts.score END), 0)) AS 'TotalNegScore',
          Sum(ISNULL((CASE WHEN posts.score > 0 THEN posts.score END), 0)) AS 'TotalPosScore',
          Max(ISNULL(posts.favoritecount, 0)) AS 'MaxFavoriteCount',
          Sum(ISNULL(posts.favoritecount, 0)) AS 'TotalFavoriteCount'
FROM       users
INNER JOIN posts on posts.owneruserid = users.id
where      users.accountid = @NetAccountID;



-- Details from comments table

UPDATE #output
SET    #output.totalcommentscore = tmp.totalcommentscore,
       #output.maxcommentscore = tmp.maxcommentscore
FROM   (SELECT Max(users.id)                  AS 'ID',
               Sum(Isnull(comments.score, 0)) AS 'TotalCommentScore',
               Max(Isnull(comments.score, 0)) AS 'MaxCommentScore'
        FROM   comments
               INNER JOIN users
                       ON comments.userid = users.id
        WHERE  users.accountid = @NetAccountID) tmp
WHERE  #output.id = tmp.id;  



-- Details from badges table

UPDATE #output
SET    #output.TotalBadges  = tmp.TotalBadges,
       #output.GoldBadges   = tmp.GoldBadges,
       #output.SilverBadges = tmp.SilverBadges,
       #output.BronzeBadges = tmp.BronzeBadges
FROM   (SELECT Max(ID)          AS ID,
               Sum(TotalBadges) AS TotalBadges,
               Max(gold)        AS GoldBadges,
               Max(silver)      AS SilverBadges,
               Max(bronze)      AS BronzeBadges
        FROM   (SELECT users.id AS ID,
                       ISNULL(Count(badges.Name), 0) AS 'TotalBadges',
                       CASE WHEN badges.class < 2 THEN CONCAT(Count(badges.Name), ' - ', STRING_AGG(CAST(badges.Name as NVARCHAR(MAX)), ', ')) END AS 'Gold',
                       CASE WHEN badges.class = 2 THEN CONCAT(Count(badges.Name), ' - ', STRING_AGG(CAST(badges.Name as NVARCHAR(MAX)), ', ')) END AS 'Silver',
                       CASE WHEN badges.class > 2 THEN CONCAT(Count(badges.Name), ' - ', STRING_AGG(CAST(badges.Name as NVARCHAR(MAX)), ', ')) END AS 'Bronze'
                FROM   badges
                       INNER JOIN users ON users.id = badges.userid
                WHERE  users.accountid = @NetAccountID
                GROUP  BY users.id, badges.class) tmpBadges) tmp    
WHERE  #output.id = tmp.id; 

SELECT * FROM #output;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results