Sites by meta representativity (network-wide)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Representativity is measured so: how many percent of the answer scores (counting only positively scored answers) were created by users having post on the meta sites.

Poker Meta

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

CREATE TABLE #Stats (
  MainName VARCHAR(MAX),
  MetaName VARCHAR(MAX),
  AllAnswers INT,
  HasMetaPost INT
);

DECLARE dbcursor CURSOR FOR
SELECT main.name AS MainName, meta.name AS MetaName
FROM sysdatabases main, sysdatabases meta
WHERE
  main.name NOT IN (
    'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'
  )
  AND
  meta.name = main.name + '.Meta';

DECLARE @query VARCHAR(MAX)='';
DECLARE @MainName VARCHAR(MAX);
DECLARE @MetaName VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @MainName, @MetaName;

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

  SELECT @query = @query + '
    SELECT
      ''' + @MainName + ''' AS MainName,
      ''' + @MetaName + ''' AS MetaName,
      SUM(MainA.Score) AS AllAnswers,
      SUM(
        CASE
          WHEN HasMetaPost.UserId IS NOT NULL THEN MainA.Score
          ELSE 0
        END
      ) AS HasMetaPost
    FROM [' + @MainName + '].dbo.Posts MainA
    LEFT JOIN (
        SELECT OwnerUserId AS UserId
        FROM [' + @MetaName + '].dbo.Posts
        WHERE
          OwnerUserId > 0
        GROUP BY OwnerUserId
      ) HasMetaPost ON MainA.OwnerUserId = HasMetaPost.UserId
    WHERE
      MainA.OwnerUserId > 0
      AND
      MainA.PostTypeId = 2
      AND
      MainA.Score > 0
';

  FETCH NEXT FROM dbcursor INTO @MainName, @MetaName;
END;

PRINT @query;

INSERT INTO #Stats EXEC (@query);

SELECT MainName, MetaName, AllAnswers, HasMetaPost,
  100.0 * HasMetaPost / AllAnswers AS Pct
FROM #Stats;

Enter Parameters

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