Find questions where I had the final Close vote

Question

I'm doing my part to burn down the Close Vote queue.

Is there a way to find all of the (non-deleted) questions where my Vote to Close was the final vote?

Answer 1

That data is not directly available in SEDE but by sheer luck the query Where close voted I as the last one gives you that result.

-- UserId: Your User id "That is the id in url on the site"
declare @userid as nvarchar(32)
declare @uid as int = ##UserId##
select @userid='"Id":'+cast(@uid as nvarchar)+','

-- if we reverse the text field the last voter is first 
-- we determine the Id field position
-- and the Id+userid position. If they differ by the lenght
-- of the userid the user was the last one voting
select postid as [Post Link]
, @userid, len(@userid)
, [text]
, reverse([text])
, charindex(':"dI"{', reverse([text]))
, charindex(reverse(@userid), reverse([text]))
from posthistory
where posthistorytypeid = 10 -- close
and [text] like '%'+@userid+'%'
and (charindex(':"dI"{', reverse([text])) - (len(@userid)-5) = 
    charindex(reverse(@userid), reverse([text])))

The success (or failure) of this query depends on the EXACT json structure in the [text] field and the assumption the users are added in order they voted. If for some reason additional data is added that also has an 'id' as one of his members then this query would need to do a full parse of the text field...

Example of the content in that field:

{"Voters":[{"Id":207421,"DisplayName":"EJP"},{"Id":647772,"DisplayName":"Tichodroma"},{"Id":131433,"DisplayName":"bmargulies"},{"Id":717341,"DisplayName":"Lukas Knuth"},{"Id":21960,"DisplayName":"Al Everett"}]}