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

I've written a query " List of Questions with only 1 Remaining Delete Vote Required to Delete ". One of the several problems with this query is that it includes questions that I have already voted to delete.

Is there a way I could omit such questions from my query? The UsedId column of the Votes table is never set.

Note that I'm a 10k user.


Query:

;
WITH CLOSEDQUESTIONS(Id, Score, AScore, VotesNeededToDelete)
AS (
  SELECT-- top 1000
    p.Id, p.Score, SUM(a.Score) AS AScore,
    CASE WHEN 3 + ((p.Score + Sum(a.Score)) /20)>10
    THEN 10
    ELSE 3 + ((p.Score + Sum(a.Score)) /20)
    END as VotesNeededToDelete
  FROM Posts as p
  INNER JOIN Posts as a ON a.ParentID = p.Id
  WHERE p.PostTypeId=1 AND p.ClosedDate IS NOT NULL
    AND EXISTS (SELECT * FROM Votes AS v WHERE v.PostId = p.Id)
  GROUP BY p.Id, p.Score
)
SELECT TOP 100
  Q.Id AS [Post Link],
  MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId) AS VotesToGo
FROM CLOSEDQUESTIONS AS Q
INNER JOIN Votes AS v ON v.PostId = Q.Id
WHERE v.VoteTypeId=10
GROUP BY Q.Id
HAVING MAX(Q.VotesNeededToDelete) - COUNT(v.VoteTypeId)=1
share|improve this question
1  
a userid is only present for BountyStart and Favourite votetypes... –  rene Oct 7 '13 at 20:41
6  
...which suggests the distinctly incongruous workaround of marking as "Favorite" everything you vote to delete. –  Josh Caswell Oct 7 '13 at 20:52
    
Don't forget that the DE only updates once a week. So even if there was a way to query this, your delete votes won't "exist" until the data refreshes. –  Mysticial Oct 10 '13 at 3:34
    
@Mysticial: that's ok. Right now, it's showing questions I voted to delete a year ago. I'm hoping that an improved query might help clear out many old "just one delete vote left" questions. –  John Saunders Oct 10 '13 at 3:35
    
As it stands I can't think of any way to do this, except as Josh said to favorite everything because userId's are removed the the DataExplorer votes table. –  Bad Wolf Oct 10 '13 at 4:03
    
This is the same problem people run into trying to make data explorer queries for Sportsmanship - the data on voting, even your own, just isn't there. –  AakashM Oct 10 '13 at 9:21

1 Answer 1

up vote 5 down vote accepted
+500

Authentication in the data explorer is decoupled from Stack Exchange, so there is never any data on SEDE that isn't publicly available on SE.

Delete votes are not public information. You cannot see other people's delete votes, any more than you can see their close votes, upvotes or downvotes. Close and delete votes are only made public after the post gets closed or deleted, and only by querying the post. So this information will not appear in SEDE.

If you want to filter out questions that you've already voted to delete, you'll have to use an external filter that authenticates to Stack Exchange. The logical method would be the API, however currently (as of version 2.1) there is no API to track votes. So you need to hit the site directly and do a bit of scraping.


Yes, sorting out questions warranting delete votes is a pain. I've just given up on them until a delete review queue comes along.

share|improve this answer

You must log in to answer this question.

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