4

I was trying to find the ratio of "how many downvotes I have cast (on others' posts obviously)" : "how many downvotes my posts has received".

The question basically boils down to finding "how many downvotes my posts has received?" because finding out "how many downvotes I have cast" is trivial and the info is publicly visible to everyone on my profile.

So, I need a query for "The number of downvotes a particular user's posts has received so far".

1 Answer 1

Reset to default
5

There is a query present to find the User Ratio of Upvotes to Downvotes Received.

select
    SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS [Up Votes],
    SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS [Down Votes],
    (CAST(SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS float) / CAST(SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS float)) AS [Up Votes to Down Votes]
from
    Votes v inner join Posts p on v.PostId=p.Id
where
    p.OwnerUserId=##UserId##

There is another query present to find the number of up vote and down vote ratio for all users.

9
  • 1
    Yes this is what I was looking for.. so are these queries uptodate?
    – Rickross
    Commented Jan 7, 2019 at 9:40
  • 1
    Keshav Srinivasan tops the chart! :D
    – Sarvabhouma
    Commented Jan 7, 2019 at 9:42
  • 2
    @Rickross SEDE is updated once a week, on Sunday 03:00 UTC. The first entry in the FAQ has the relative duration since the last update (also shown on the front page).
    – rene
    Commented Jan 7, 2019 at 17:07
  • @rene Thanks ... sometimes I hv seen those data not to match with the current stats.. tht's why asked.
    – Rickross
    Commented Jan 9, 2019 at 8:37
  • 1
    @Rickross yeah, it is a fair question. If you need uptodate data a better option is using the Stack API but that requires scripting in some programming language and is limited in its capabilities, specially if you want a data aggregation over lots of rows. For your specific use case described here I think SEDE with at max 7 days old data is fine.
    – rene
    Commented Jan 9, 2019 at 8:51
  • I am unable to generate mine though. What is UserID? "Macindows" does not seem to be working.
    – Macindows
    Commented Jan 24, 2019 at 6:50
  • 1
    @Macindows If you will go to your profile, you will see this url in browser: hinduism.meta.stackexchange.com/users/4893/macindows Here 4893 is your userId.
    – Triyugi Narayan Mani
    Commented Jan 24, 2019 at 7:13
  • @TriyugiNarayanMani Got it finally lol. Thanks for the help man.
    – Macindows
    Commented Feb 8, 2019 at 3:56
  • @Macindows You are welcome. Glad to know it helped :)
    – Triyugi Narayan Mani
    Commented Feb 8, 2019 at 5:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .