Sites per ratio of non-CW self-answered questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

CREATE TABLE #output (
  dbName VARCHAR(max),
  SAQ INT,
  TQ INT
);

DECLARE @query VARCHAR(MAX);

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

DECLARE @dbName VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbName + ''' AS dbName,
      SAQ.SAQ,
      TQ.TQ
    FROM (
      SELECT COUNT(DISTINCT Q.Id) AS SAQ
      FROM
        [' + @dbName + '].dbo.Posts Q,
        [' + @dbName + '].dbo.Posts A
      WHERE
        A.ParentId = Q.Id
        AND
        Q.OwnerUserId >= 1
        AND
        Q.OwnerUserId = A.OwnerUserId
        AND
        Q.CommunityOwnedDate IS NULL
        AND
        A.CommunityOwnedDate IS NULL
      ) SAQ,
      (
      SELECT COUNT(*) AS TQ
      FROM [' + @dbName + '].dbo.Posts
      WHERE PostTypeId = 1
      ) TQ
  ';
  INSERT INTO #output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbName;
END

CLOSE dbcursor;

SELECT
  dbName,
  SAQ AS 'Self-Answered Questions',
  TQ AS 'Total Questions',
  100.0 * SAQ / TQ AS Pct
FROM #output
ORDER BY Pct DESC;

Enter Parameters

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