Your questions by estimated daily view count, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

DECLARE @result TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  Id INT NOT NULL,
  Title VARCHAR(MAX) NOT NULL,
  Score INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  ViewCount INT NOT NULL,
  ViewsPerDay FLOAT NOT NULL
)

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');

OPEN dbCursor;
FETCH NEXT FROM dbcursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
      UNION ALL
    '
  
  SELECT @query = @query + '
    SELECT
      ''' + @dbName + ''' AS DbName,
      Id,
      Title,
      Score,
      CreationDate,
      ViewCount,
      86400.0 * ViewCount / DATEDIFF(s, CreationDate, (
        SELECT MAX(CreationDate) FROM [' + @dbName + '].dbo.Posts
        ) + 1) AS ViewsPerDay
    FROM [' + @dbName + '].dbo.Posts
    WHERE
      PostTypeId = 1
      AND
      OwnerUserId = (
        SELECT Id
        FROM [' + @dbName + '].dbo.Users
        WHERE AccountId = ' + CAST(##AccountId:int## AS VARCHAR(MAX)) + '
      )
  ';

  FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;

--PRINT @query;

INSERT INTO @result EXEC (@query);

WITH SiteToDb 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
  DENSE_RANK() OVER (ORDER BY Result.ViewsPerDay DESC) AS Rank,
  'https://' + SiteToDb.SiteName + '|' + SiteToDb.SiteName AS Site,
  'https://' + SiteToDb.SiteName + '/q/' + CAST(Id AS VARCHAR(MAX)) +
    + '|' + Result.Title AS [Question Link],
  Result.Score,
  Result.CreationDate,
  Result.ViewCount,
  Result.ViewsPerDay
FROM @result Result, SiteToDb
WHERE
  SiteToDb.DbName = Result.DbName
ORDER BY Result.ViewsPerDay DESC, Result.DbName, Result.Id;

DECLARE @dailySum FLOAT = (SELECT SUM(ViewsPerDay) FROM @result);

PRINT 'Your total daily question view count: '
  + CAST(@dailySum AS VARCHAR(MAX));

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results