What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 130 Stack Exchange communities.

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

1 Answer 1

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 .