Sites by upvote-to-downvote ratio

1

Please login or register to vote for this query.

(click on this box to dismiss)

Japanese Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
DECLARE @sitedbname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  SiteDbName nvarchar(max),
  UpVotes int,
  DownVotes int,
  QUpVotes int,
  QDownVotes int,
  AUpVotes int,
  ADownVotes int
)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)
  
  IF @ispersitemeta = 0 
  BEGIN
    SET @query = '
      USE [' + @sitedbname + ']

      INSERT INTO #out
      SELECT
        ''' + @sitedbname + ''' AS [SiteDbName],
        SUM((CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END)) AS [UpVotes],
        SUM((CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END)) AS [DownVotes],
        SUM((CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1 ELSE 0 END)) AS [QUpVotes],
        SUM((CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1 ELSE 0 END)) AS [QDownVotes],
        SUM((CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1 ELSE 0 END)) AS [AUpVotes],
        SUM((CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1 ELSE 0 END)) AS [ADownVotes]
      FROM
        Votes AS v
        INNER JOIN Posts AS p
          ON v.PostId = p.Id
      '
    EXEC sp_executesql @query
  END
  
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites;


WITH Computed AS (
  SELECT
    SiteDbName,
    UpVotes,
    DownVotes,
    ROUND(CAST(UpVotes AS float)/CAST(DownVotes AS float), 1) AS "UDRatio",
    ROUND(CAST(QDownVotes AS float)/CAST(ADownVotes AS float), 2) AS "QADownvoteRatio"
  FROM
    #out
) 

SELECT
  ROW_NUMBER() OVER (ORDER BY "UDRatio" DESC) AS "Rank",
  *
FROM
  Computed

Enter Parameters

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