0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional mathematicians

CREATE TABLE #results (site NVARCHAR(250),
                       posts INT, upvotes INT, downvotes INT);

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''',
  (SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.PostsWithDeleted),
  (SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Votes WHERE VoteTypeId = 2),
  (SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.Votes WHERE VoteTypeId = 3)
'
FROM sys.databases
WHERE database_id > 5
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.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,
    Posts,
    Upvotes,
    ROUND(CAST(Upvotes AS FLOAT) / Posts, 2) AS 'Upvotes per post',
    RANK() OVER (ORDER BY ROUND(CAST(Upvotes AS FLOAT) / Posts, 2) DESC) AS 'Rank',
    Downvotes,
    ROUND(CAST(Downvotes AS FLOAT) / Posts, 2) AS 'Downvotes per post',
    RANK() OVER (ORDER BY ROUND(CAST(Downvotes AS FLOAT) / Posts, 2) DESC) AS 'Rank'
  FROM #results
  ORDER BY 8
     
DROP TABLE #results

fork query

edited jan 19 21

created jan 19 21 Glorfindel