6

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?

1

2 Answers 2

6

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

graph showing peak to 2500

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:

graph of close votes per day, showing a peak at start of march

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.

4
  • 2
    This data isn't accurate though as it doesn't include deleted posts.
    – hichris123
    Jun 29, 2014 at 15:11
  • @hichris123 I didn't claim it is accurate...
    – rene
    Jun 29, 2014 at 15:32
  • 1
    No, 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.
    – hichris123
    Jun 29, 2014 at 15:33
  • OK, I added an extra sentence on that...
    – rene
    Jun 29, 2014 at 15:37
1

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).

You must log in to answer this question.

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