SE in percentage of the SO, historical comparison

2

Please login or register to vote for this query.

(click on this box to dismiss)

And also a good source for prediction

Emacs

Q&A for those using, extending or developing Emacs

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
  SELECT @query = '
    WITH t AS (
      SELECT
        DATEFROMPARTS(
          DATEPART(yyyy, CreationDate),
          DATEPART(mm, CreationDate),
        1) AS Month
      FROM [' + @dbname + '].dbo.PostsWithDeleted
    )
    SELECT
      ''' + @dbname + ''' AS dbname,
      Month, COUNT(*) AS N
    FROM t
    GROUP BY Month;';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

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 to meta site
loading Hold tight while we fetch your results