Aaron's top questions by view count

0

Please login or register to vote for this query.

(click on this box to dismiss)

Artificial Intelligence

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

CREATE TABLE #results 
(
  db           sysname,
  Id           int,
  Title        nvarchar(250),
  ViewCount    int,
  Score        int,
  CreationDate datetime
);

DECLARE @exec nvarchar(500), @sql nvarchar(max), @c cursor;

SELECT @sql = N'INSERT #results
  (db,Id,Title,ViewCount,Score,CreationDate)
  SELECT DB_NAME(), p.Id, p.Title, p.ViewCount, p.Score, p.CreationDate
  FROM Posts AS p INNER JOIN Users AS u
    ON p.OwnerUserId = u.Id
    WHERE p.PostTypeId = 1 AND u.AccountId = @AccountId;';

SET @c = cursor FOR SELECT database_name
  FROM dbo.sede_databases WHERE notes IS NULL;

OPEN @c;
FETCH NEXT FROM @c INTO @exec;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@exec) + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@AccountId int', ##AccountId:int##;
  FETCH NEXT FROM @c INTO @exec;
END

SELECT Site        = sd.long_name, 
       Question    = CONCAT(sd.site_url, '/q/', r.Id, '|', r.Title),
       [# views]   = r.ViewCount,
       r.Score,
       [Posted on] = r.CreationDate
  FROM dbo.sede_databases AS sd
  INNER JOIN #results AS r 
     ON r.db = sd.database_name
  ORDER BY r.ViewCount DESC;

Enter Parameters

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