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

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?

share|improve this question
    
See here: meta.stackexchange.com/questions/53588/… –  Jon Seigel Jul 4 '10 at 3:41

1 Answer 1

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

enter image description here

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 fouind a post were you are the close voter.

CreationDate holds the close time as well, hence the cast to a date. Use a dateformat for other granularity.

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

You must log in to answer this question.

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