Is there any query yet at Stack Exchange Data Explorer that allows me to get the number of closed votes I cast for a given time period?
2 Answers
That data is not exactly in SEDE as pointed out by Jon and hichris123 in the comments. As the data from deleted posts is missing (obviously) you only get a very rough idea of the numbers. Some data though becomes only available when the post is closed. You could describe this query as the overview of the total number of posts that got closed on a certain day and the number of posts that were closed because I used my close vote on that post.
Not exactly what you want but it might be a step in the right direction. At least it shows once more that we need more people close voting.
The results for the first quarter of 2014 and my own votes
Query
-- userid: your user id on the site "found in the URL of your profile"
-- sd: Start date "yyyy-mm-dd format"
-- ed: End date "yyyy-mm-dd format"
declare @userid int = ##userid:int##
declare @sdate nvarchar(10) = ##sd:string##
declare @edate nvarchar(10) = ##ed:string##
declare @startdate date = cast(@sdate as date)
declare @enddate date = cast(@edate as date)
declare @userwithid nvarchar(20) = '"Id":' + cast(@userid as nvarchar) + ','
print @userwithid
select cast(creationdate as date)
, count(*) as total
, sum(case
when Charindex(@userwithid, [text] )>0 then 1
else 0
end) as [user]
from posthistory
where posthistorytypeid = 10
and creationdate between @startdate and @enddate
group by cast(creationdate as date)
order by cast(creationdate as date)
How does this work?
The [Text] field holds an JSON encoded string with all the close voters for example:
{"Voters":[{"Id":833890,"DisplayName":"Ollie"},{"Id":578411,"DisplayName":"rene"},{"Id":213550,"DisplayName ...
Given an userid, ie 578411 I combine that to a search term "Id":578411,
which makes it uniquely identifiable in the [text]
field. If the charindex
function returns a number bigger than 0 we have found a post where you are the close voter.
CreationDate
holds the close time as well, hence the cast to a date. Use a dateformat for other granularity.
Alternative 2021 solution
I've come a long way since 2014. Today that query would employ the OPENJSON function to get a table from the JSON in the text field to get all the voters involved and then do the magic. That leads to this improved query with a bonus that it also show how many voters there were on that day for still visible questions.
-- userid: your user id on the site "found in the url of your profile"
-- sd: Start date "yyyy-mm-dd format"
-- ed: End date "yyyy-mm-dd format"
declare @userid int = ##userid:int##
declare @sdate nvarchar(10) = ##sd:string##
declare @edate nvarchar(10) = ##ed:string##
declare @startdate date = cast(@sdate as date)
declare @enddate date = cast(@edate as date)
print @userwithid
select cast(creationdate as date)
, count(distinct postid) as total
, count(distinct voters.id) as [# voters]
, sum(case
when voters.id = @userid
then 1
else 0
end) as [user]
from posthistory
cross apply openjson(text, '$.Voters') with (Id int '$.Id') as voters
where posthistorytypeid = 10
and creationdate between @startdate and @enddate
group by cast(creationdate as date)
order by cast(creationdate as date)
And from the above query we now get this graph:
Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the unforgettable Monica Cellio.
Say "Hi" in SEDE chat.
-
2This data isn't accurate though as it doesn't include deleted posts. Jun 29, 2014 at 15:11
-
-
1No, I know, but I'm just saying that the numbers will be way off. Your query is good, but the data doesn't include everything so it won't give you the actual numbers. Jun 29, 2014 at 15:33
-
The SEDE query by @rene is the closest you can get; close votes are anonymized before they enter the database and only through other means (close notice information) they become visible. Also, many closed questions end up being deleted and don't appear in SEDE.
You can see close votes in your profile. If you have 10k reputation, you can even see close votes on deleted questions! That allows you to find the total number of close votes you cast in a given time period: just find the first page with votes from that period (e.g. with binary search), find the last page, multiple the difference in page number by 30 (the number of close votes per page) and add/substract a few votes (depending on where the votes from that period begin/end on the first/last page) and you know the number of close votes you cast in that period (on all questions if you have 10k, on non-deleted questions otherwise).