Getting number of upvotes and downvotes for each tag for a user, along with ratio

0

Please login or register to vote for this query.

(click on this box to dismiss)

number of upvotes and downvotes i have for each tag, questions and answers combined

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @userid int = ##userid:int##

-- remember order of execution - from, where, group by, having, select, order by
-- each command can take aliases from previous command

select a.tagname as "Tagname", x.totalposts as "Total posts", a.upvotes  as "Upvotes", b.downvotes as "Downvotes", ROUND(CAST(a.upvotes as float) / CAST(b.downvotes as float), 2) as 'Ratio up to down', ROUND(CAST(a.upvotes as float) / CAST(x.totalposts as float), 2) as 'Ratio up to total', ROUND(CAST(b.downvotes as float) / CAST(x.totalposts as float), 2) as 'Ratio down to total'

from

(SELECT tagname, COUNT(*) as totalposts
FROM Tags
JOIN PostTags on posttags.tagid = tags.id
JOIN Posts on Posts.parentid = posttags.postid or posts.id = posttags.postid
where posts.owneruserid = @userid
GROUP BY tagname) as x

JOIN

(SELECT tagname, COUNT(*) as upvotes
FROM Tags
JOIN PostTags on posttags.tagid = tags.id
JOIN Posts on Posts.parentid = posttags.postid or posts.id = posttags.postid
JOIN Votes on votes.postid = posts.id and votes.votetypeid=2
where posts.owneruserid = @userid
GROUP BY tagname) as a
--ORDER BY upvotes desc

on x.tagname = a.tagname

JOIN

(SELECT tagname, COUNT(*) as downvotes
FROM Tags
JOIN PostTags on posttags.tagid = tags.id
JOIN Posts on Posts.parentid = posttags.postid or posts.id = posttags.postid
JOIN Votes on votes.postid = posts.id and votes.votetypeid=3
where posts.owneruserid = @userid
GROUP BY tagname) as b

on a.tagname = b.tagname

ORDER BY 'Ratio up to down' desc;

Enter Parameters

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