downvote/upvote ratio of golang related tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

with 
golangposts as ( 
  select p.id from posts p
  join posttags pt on pt.postid = p.id
  where pt.tagid = 15627 and p.posttypeid = 1
  group by p.id
),
q as (
  select count(*) as cnt, pt.tagid, v.votetypeid
  from golangposts as gop
  join posttags pt on pt.postid = gop.id
  join votes v on v.postid = gop.id
  where v.votetypeid in (2, 3)
  group by pt.tagid, v.votetypeid
)
select top 100 t.tagname AS [Tags], q.cnt AS [Upvotes], qq.cnt AS [Downvotes], ROUND(100.0 * qq.cnt / NULLIF(q.cnt, 0), 2) AS [D/U ratio]
from q
join tags t on t.id = q.tagid
join q qq on q.tagid = qq.tagid and qq.votetypeid = 3
where q.votetypeid = 2
order by qq.cnt+q.cnt desc

Enter Parameters

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