SE in percentage of SO, by monthly voting activity

1

Please login or register to vote for this query.

(click on this box to dismiss)

Esperanto Language

Q&A for teachers and students of the Esperanto language

DECLARE @output TABLE (
  DbName VARCHAR(max),
  Month DATE,
  Count 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
  IF @query <> '' SELECT @query = @query + ' UNION '
    
  SELECT @query = @query + '
    SELECT
      ''' + @dbname + ''' AS DbName,
      DATEFROMPARTS(
        DATEPART(yyyy, CreationDate),
        DATEPART(mm, CreationDate),
      1) AS Month,
      COUNT(*) AS Count
    FROM [' + @dbname + '].dbo.Votes
    GROUP BY
      DATEFROMPARTS(
        DATEPART(yyyy, CreationDate),
        DATEPART(mm, CreationDate),
        1)
    ';
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

INSERT INTO @output EXEC (@query);

WITH NetworkMonth AS (
  SELECT
    CASE
      WHEN DbName IN (
        'StackOverflow', 'StackOverflow.Meta'
        )
      THEN 'SO'
      ELSE 'SE'
    END AS Network,
    Month,
    Count
  FROM @output
),
NetworkMonthGroup AS (
  SELECT Month, Network, SUM(Count) AS Count
  FROM NetworkMonth
  GROUP BY Network, Month
)
SELECT SE.Month, 100.0 * SE.Count / SO.Count AS SeAsSoPct
FROM NetworkMonthGroup SE, NetworkMonthGroup SO
WHERE
  SE.Month = SO.Month
  AND
  SE.Network = 'SE'
  AND
  SO.Network = 'SO'
ORDER BY Month;

Enter Parameters

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