StackExchange 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

-----------------------------------------------------------------------------------------
-- This query is written by rene (https://data.stackexchange.com/users/11779)          --
-- To see the original version of this query which includes all the comments           --
-- pls visit this link: https://data.stackexchange.com/stackoverflow/query/998446      --
-- Pls Note that this query has been modified by the user to meet his/her requirements --
----------------------------------------------------------------------------------------- 

DECLARE @sql NVARCHAR(max)

CREATE TABLE #output
(
   SiteName           NVARCHAR(max),
   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)
);  


SELECT @sql = N'INSERT INTO #output
            (SiteName, id, displayname, rep, upvotes, downvotes, views, creationdate, lastseen, location,
             maxscore, minscore, avgscore, totalnegscore, totalposscore, maxfavoritecount, totalfavoritecount)
'+ String_agg(Concat(N'
SELECT ''', CONVERT(NVARCHAR(max), NAME) ,N''' AS ''SiteName'',
          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       ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.users users
LEFT OUTER JOIN ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.posts posts on posts.owneruserid = users.id
where      users.accountid = ##StackExchangeNetworkProfileID##'), N'' + CHAR(13) + CHAR(13) + 'union all' + CHAR(13) + CHAR(13))
FROM   sys.databases
WHERE  database_id > 5 AND (NAME NOT LIKE '%.Meta' OR NAME = 'StackExchange.Meta')

EXEC (@sql)



SELECT @sql = N' '+ String_agg(Concat(N'
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   ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.comments comments
               INNER JOIN ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.users users
                       ON comments.userid = users.id
        WHERE  users.accountid = ##StackExchangeNetworkProfileID##) tmp
        WHERE  #output.id = tmp.id;'), N'' + CHAR(13) +  CHAR(13)  )  
FROM   sys.databases
WHERE  database_id > 5 AND (NAME NOT LIKE '%.Meta' OR NAME = 'StackExchange.Meta')

EXEC (@sql)


SELECT @sql = N' '+ String_agg(Concat(N'
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   ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.badges badges
                       INNER JOIN ', CONVERT(NVARCHAR(max), Quotename(NAME)), N'.dbo.users users ON users.id = badges.userid
                WHERE  users.accountid = ##StackExchangeNetworkProfileID##
                GROUP  BY users.id, badges.class) tmpBadges) tmp    
WHERE  #output.id = tmp.id;'), N'' + CHAR(13) +  CHAR(13)  )  
FROM   sys.databases
WHERE  database_id > 5 AND (NAME NOT LIKE '%.Meta' OR NAME = 'StackExchange.Meta')

EXEC (@sql)

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

SELECT * FROM #output;

Enter Parameters

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