Sites by percentage of well-received questions (network-wide)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

CREATE TABLE #Sites (
  DbId INT NOT NULL,
  Type BIT NOT NULL,
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX) = '';

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

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId INT;

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  SELECT @query = @query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      0 AS Type,
      COUNT(*)
    FROM [' + @DbName + '].dbo.PostsWithDeleted
    WHERE
      PostTypeId = 1
      AND
      CreationDate > ''2018-01-01''
    
    UNION ALL
    
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      1 AS Type,
      COUNT(*)
    FROM
      [' + @DbName + '].dbo.Posts Q,
      [' + @DbName + '].dbo.Posts A
    WHERE
      Q.AcceptedAnswerId = A.Id
      AND
      Q.Score > 0
      AND
      A.Score > 0
      AND
      Q.ClosedDate IS NULL
      AND
      Q.CreationDate > ''2018-01-01''
  ';

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

CLOSE dbcursor;

PRINT @query;

INSERT INTO #Sites EXEC (@query);

SELECT
  sysdatabases.name AS DbName,
  W.Count AS WellQ,
  T.Count AS TotalQ,
  100.0 * W.Count / T.Count AS Pct
FROM #Sites T, #Sites W, sysdatabases
WHERE
  T.DbId = W.DbId
  AND
  T.Type = 0
  AND
  W.Type = 1
  AND
  T.DbId = sysdatabases.dbid
ORDER BY Pct DESC, T.DbId

Enter Parameters

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