5

My theory is that answers which have received upvotes on 15 distinct days spaced across a year were in fact more valuable than answers which received 100 upvotes within a 2 day period (after reaching the HNQ list) and were upvoted on only two later days after the initial surge.

Similar to the "HNQ boost," when an old question gets an edit or a new answer, the post is bumped to the home page of whatever site it's on and may draw additional attention (and votes). This is perfectly fine, but it's different from receiving an upvote from someone who searched (say, on Google) for that exact question (because they were facing that problem), found the post, and marked it as helpful.


I played around with data.stackexchange.com but couldn't work out how to find the dates of upvotes/downvotes. I know this data must be stored, because it's visible in the "reputation" tab on any user account.

How can I query for the posts which have been upvoted on the most days?


To put in pseudocode what I'm trying to do, it would be something like this:

select count(distinct votedate), postid from posts natural join votes
where postuserid = 'mine' and votetype = 'upvote'
group by postid order by count desc

(Which would probably need typecasting or substring function call to make a date out of a timestamp, but that gives the essence of what I want.)

1 Answer 1

6

If I understand your question correct this query should return the answers you're looking for on a single site (I picked Unix and Linux)

select id as [Post Link]
     , count(*) [#days with upvotes]
     , sum(cnt) [total upvotes]
from
(
-- votes on single day 
-- for a post
select v.creationdate
     , p.id 
     , count(*)  cnt
from posts p
inner join votes v on v.postid = p.id
where p.owneruserid = ##userid:int## 
and votetypeid = 2 -- upvote
and posttypeid = 2 -- answers
group by v.creationdate, p.id
) as data
group by id
order by count(*) desc

When run today, this will be the result:

query result

You'll find the Database schema here and an rough ER Diagram here.

Remember that SEDE is only updated once a week, on Sunday.

1
  • Haha, despite my using "votes" in my pseudocode, I hadn't actually found it in the schema. Silly me. I couldn't figure out why "PostFeedback" didn't have as many records per post as there were votes. :)
    – Wildcard
    Jul 18, 2017 at 7:50

You must log in to answer this question.

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