0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

--Credit: Aaron Bertrand, Determining the least dangerous tags with over 1K
--answers to answer, Code Review
--Answer link: codereview/stackexchange.com/a/291853/277260
;WITH BusyTags AS /* isolate the tag aggregate */
(
  SELECT TagId
    FROM PostTags
   GROUP BY TagId
  HAVING COUNT(*) >= 100
), t AS /* then get the names */
(
  SELECT bt.TagId, t.TagName
    FROM BusyTags AS bt
   INNER JOIN Tags AS t
      ON bt.TagId = t.Id
), pt AS /* now get the posts */
(
  SELECT pt.PostId, t.TagId, t.TagName
    FROM PostTags AS pt
   INNER JOIN t
      ON pt.TagId = t.TagId
), v AS /* then sum the votes with a single scan */
(
  SELECT pt.TagId, Tags = pt.TagName, 
         UpVotes   = SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END), 
         DownVotes = SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END)
    FROM pt
   INNER JOIN Votes AS v
      ON pt.PostId = v.PostId
   GROUP BY pt.TagId, pt.TagName
)
SELECT Tags, 
       ratio = CONVERT(DECIMAL(5,2), 
               1.0 * Upvotes/NULLIF(DownVotes,0))
  FROM v
 ORDER BY ratio DESC;

fork query

edited dec 5

created apr 30 CrSb0001