My longest posts network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

-- Thanks to https://data.stackexchange.com/stackoverflow/query/851061/users-with-the-highest-combined-reputation-network-wide
-- and https://data.stackexchange.com/meta.sharepoint/query/1060274/dupes-and-relates-of-your-posts-network-wide
-- both by peterh.

-- More directly, see 
-- https://data.stackexchange.com/tex/query/1306015/my-account-id-on-each-website 
-- https://data.stackexchange.com/tex/query/769513/longest-answers
-- https://data.stackexchange.com/tex/query/1306018/my-longest-answers-network-wide-inconvenient-output-without-links
-- https://meta.stackexchange.com/questions/300375/how-does-id-as-user-link-work-as-a-hyperlink-while-running-a-query-in-the-stac

-- See also:
-- https://data.stackexchange.com/stackoverflow/query/744727/answers-with-highest-total-bounties-throughout-network
-- https://meta.stackexchange.com/questions/269743/which-answer-has-earned-highest-bounty-in-se-network

DECLARE @output TABLE (
    _LENGTH INT,
    PostId INT,
    PostTypeId INT,
    CreationDate DATETIME,
    Score INT,
    ViewCount INT,
    ScorePerView FLOAT,
    Body VARCHAR(MAX),
    OwnerUserId INT,
    Title VARCHAR(MAX),
    DbName VARCHAR(MAX)
);
DECLARE @query VARCHAR(MAX);
DECLARE @dbname VARCHAR(max);

DECLARE dbcursor CURSOR FOR
    SELECT name
    FROM sysdatabases
    WHERE (
        name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
        -- AND name NOT LIKE '%.Meta'
    );

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    DECLARE @MyAccountId INT;
    SELECT @MyAccountId = Id
    FROM [' + @dbname + '].dbo.Users
    WHERE AccountId = ' + CAST(##SEAccountId:int## AS VARCHAR(MAX)) + ';
    
    SELECT 
      len(p1.body) AS _LENGTH,
      p1.Id AS PostId,
      p1.PostTypeId,
      p1.CreationDate,
      p1.Score,
      isnull(p1.ViewCount, p2.ViewCount),
      1.0 * p1.Score / isnull(p1.ViewCount, p2.ViewCount),
      p1.Body,
      p1.OwnerUserId,
      isnull(p1.Title, p2.Title),
      ''' + @dbname + ''' AS DbName
    FROM 
        [' + @dbname + '].dbo.PostsWithDeleted p1
        LEFT JOIN [' + @dbname + '].dbo.PostsWithDeleted p2
        ON p1.ParentId = p2.Id 
    WHERE 
      p1.OwnerUserId = @MyAccountId; 
    ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END
CLOSE dbcursor;

-- Everything except the URL is already in the @output table.
-- We join with the following "Site2Db" table to get the URLs.
WITH Site2Db AS (                                                                                                                                                                             
  SELECT                                                                                                                                                                                      
    name AS DbName,                                                                                                                                                                           
    LOWER(CASE                                                                                                                                                                                
      WHEN                                                                                                                                                                                    
        name = 'StackExchange.Programmers'                                                                                                                                                    
      THEN                                                                                                                                                                                    
        'softwareengineering.stackexchange.com'                                                                                                                                               
      WHEN                                                                                                                                                                                    
        name = 'StackExchange.Programmers.Meta'                                                                                                                                               
      THEN                                                                                                                                                                                    
        'softwareengineering.meta.stackexchange.com'                                                                                                                                          
      WHEN                                                                                                                                                                                    
        name LIKE 'StackExchange.%'                                                                                                                                                           
      THEN                                                                                                                                                                                    
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'                                                                                                                                    
      WHEN                                                                                                                                                                                    
        name = 'StackOverflow.Br'                                                                                                                                                             
      THEN                                                                                                                                                                                    
        'pt.stackoverflow.com'                                                                                                                                                                
      WHEN                                                                                                                                                                                    
        name = 'StackOverflow.Br.Meta'                                                                                                                                                        
      THEN                                                                                                                                                                                    
        'pt.meta.stackoverflow.com'                                                                                                                                                           
      WHEN                                                                                                                                                                                    
        name LIKE 'StackOverflow.__'                                                                                                                                                          
      THEN                                                                                                                                                                                    
        RIGHT(name, 2) + '.stackoverflow.com'                                                                                                                                                 
      WHEN                                                                                                                                                                                    
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
)
SELECT
    'https://' + Site2Db.SiteName + '/q/' + CAST(output.PostId AS VARCHAR(MAX)) + '|' + output.title AS Title,
    _LENGTH,
    -- PostId,
    -- PostTypeId,
    CreationDate,
    Score,
    ViewCount,
    ScorePerView,
    Body,
    -- OwnerUserId,
    output.DbName
FROM 
    @output AS output 
    LEFT JOIN 
    Site2Db 
    ON output.DbName = Site2Db.DbName -- AND output.PostTypeId = 1
ORDER BY _LENGTH DESC;

Enter Parameters

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