Voting statistics from anonymous feedback

0

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

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.PostFeedback WHERE VoteTypeId = 2),
  (SELECT COUNT(*) FROM ' + quotename(name) + '.dbo.PostFeedback 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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results