Is the Area51 dying?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

DECLARE @output TABLE (
  DbName VARCHAR(max),
  FirstPost DateTime);

DECLARE @query VARCHAR(MAX);

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

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS DbName,
      MIN(CreationDate) AS FirstPost
    FROM [' + @dbname + '].dbo.Posts
    WHERE
      PostTypeId = 1
      AND
      Id NOT IN (
        SELECT PostId
        FROM [' + @dbname + '].dbo.PostHistory
        WHERE PostHistoryTypeId IN (17, 18, 35, 36, 37, 38)
      );'
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH SitesCreated AS (
  SELECT
    DbName,
    DATEFROMPARTS(
      DATEPART(yyyy, FirstPost),
      1,
      --DATEPART(mm, FirstPost),
      1) AS FirstPost
  FROM @output
)
SELECT
  FirstPost,
  /* -- old version with year equalization for may-2017
  CASE WHEN FirstPost = '2017-01-01 00:00:00' THEN 12.0 * COUNT(*) / 5
  ELSE COUNT(*) END AS [Started sites in that year]*/
  COUNT(*) AS [Started sites in that year]
FROM SitesCreated
GROUP BY FirstPost
ORDER BY FirstPost

Enter Parameters

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