Downvoted posts with no comments (all-sites)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- 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),
  TotalDV int,
  DVNoComm int,
  TotalNeg int,
  NegNoComm 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 ( SiteDbName, TotalDV, DVNoComm, TotalNeg, NegNoComm )
      VALUES (
        ''' + @sitedbname + ''',
        ( SELECT COUNT(DISTINCT p.ID)
          FROM Posts p INNER JOIN Votes v ON p.Id = v.PostId
          WHERE v.VoteTypeId = 3
        ),
        ( SELECT COUNT(DISTINCT p.ID)
          FROM Posts p INNER JOIN Votes v ON p.ID = v.PostId
          WHERE v.VoteTypeId = 3 AND ISNULL(p.CommentCount,0) = 0
        ),
        ( SELECT COUNT(*)
          FROM Posts p
          WHERE p.Score < 0
        ),
        ( SELECT COUNT(*)
          FROM Posts p
          WHERE p.Score < 0 AND ISNULL(p.CommentCount,0) = 0
        )
      )
    '
    EXEC sp_executesql @query
  END
  
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites;


WITH Computed AS (
  SELECT
    SiteDbName,
    TotalDV,
    DVNoComm,
    ROUND(CAST(DVNoComm AS float)/CAST(TotalDV AS float), 3) AS "DVRatio",
    TotalNeg,
    NegNoComm,
    ROUND(CAST(NegNoComm AS float)/CAST(TotalNeg AS float), 3) AS "NegRatio"
  FROM
    #out
) 

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

Enter Parameters

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