1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for committed Christians, experts in Christianity and those interested in learning more

-- result table, don't rename and keep the site column
CREATE TABLE #results (site nvarchar(250),
                       removed int,
                       total int);

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''',
  (SELECT COUNT(DISTINCT(PostId))
     FROM ' + quotename(name) + '.dbo.PostHistory
     WHERE PostHistoryTypeId = 53), -- removed from HNQ
  (SELECT COUNT(DISTINCT(PostId))
     FROM ' + quotename(name) + '.dbo.PostHistory
     WHERE PostHistoryTypeId = 52) -- became HNQ
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE 'StackExchange.Arabic%'
  AND name NOT LIKE '%.Meta'

-- execute it
EXEC (@sql)

SELECT
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '|' + site AS 'Site',
  removed AS '# of questions removed',
  total AS 'total # of HNQs',
  CASE WHEN total = 0
       THEN NULL
       ELSE ROUND(removed * 100.0 / total, 2) END AS 'removed (% of total)'
  FROM #results
  ORDER BY removed DESC, total ASC
     
DROP TABLE #results

fork query

edited may 8 19

created may 8 19 Glorfindel