Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 157 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

I was trying to construct a query in the DataExplorer to find the posts with the most upvotes without any downvotes, but found that (by including the wrong ORDER BY clause) SO lists a bunch of questions at score -1, yet it doesn't record any downvotes (or votes for that matter) for them.

I can't log into the DataExplorer for some reason, but I'm using the following query (to show all posts of score < 1 without any downvotes):

WITH DownVotes AS (
SELECT v.Id AS Id, v.PostId AS PostId
FROM Votes v INNER JOIN VoteTypes vt ON vt.Id = v.VoteTypeId
WHERE vt.Name = 'DownMod'
)

SELECT p.Id AS [Post Link], p.PostTypeId AS [Post Type], p.Score,
(SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id) AS [Num Votes]
FROM Posts p
WHERE p.CommunityOwnedDate IS NULL AND
(SELECT COUNT(*) FROM DownVotes v WHERE v.PostId = p.Id) = 0
AND p.Score < 0

I included the Score column to show that the votes didn't simply happen after the data dump.

Are these votes by deleted users or what may I have found?

share|improve this question
    
spam/offensive flags also decrease the score by one – balpha Oct 23 '10 at 18:09
    
@balpha: Wouldn't those at least show in the "Total Votes" column I've specified? – palswim Oct 23 '10 at 18:18
    
AFAIK, flags aren't included in the data dump, at least not those that have aged away. I could be wrong though. – balpha Oct 23 '10 at 19:04

Flags are definitely included in the data dump, all the way back to site inception (or flag type inception, which was probably later):

SELECT * FROM Votes WHERE VoteTypeId IN (4, 12)

but right now in the latest dump, I'm having a hard time finding a lot of posts where the calculated score is out of sync with the denormalized score. This query returns a meagre 2 results in the Stack Overflow data set:

SELECT
    PostId AS [Post Link],
    PostScore,
    UpDnVoteScore + FlagScore AS VoteScore
    FROM
    (
        SELECT
            p.Id AS PostId,
            p.Score AS PostScore,
            SUM(CASE v.VoteTypeId WHEN 2 THEN 1 WHEN 3 THEN -1 END) AS UpDnVoteScore,
            SUM(CASE v.VoteTypeId WHEN 4 THEN -1 WHEN 12 THEN -1 END) AS FlagScore
            FROM Posts p
            INNER JOIN Votes v ON p.Id = v.PostId
            WHERE v.VoteTypeId IN (2, 3, 4, 12)
            GROUP BY p.Id, p.Score
    ) s
    WHERE PostScore != UpDnVoteScore

Note that in your query, you need to filter out the different vote types -- you're going to get weird results from a query like SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id because the Votes table includes voting types such as close votes, delete votes, bounty, etc. See here for a reference of all the vote types.

share|improve this answer

You must log in to answer this question.

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