« first day (906 days earlier)      last day (372 days later) » 

11:48 AM
2
A: Do we use the downvote button more than other sites?

GlorfindelWe can use the Data Explorer to see how often we up- and downvote. This query says a post receives (on average) 5.29 upvotes and 0.46 downvotes. With the 'site switcher' you can compare the figures to other sites in the network, e.g. Stack Overflow which has 2.24 upvotes and 0.29 downvotes. Unfo...

@Glorfindel Would perhaps using COUNT(DISTINCT PostId) FROM Votes to count posts improve chances that the cross-site query would not time out?
Since in this way we're only working with the table Votes, and not with two tables Votes and Posts. (Although the number would not be perfectly accurate - it would not count posts which had no votes.)
 
12:02 PM
I'd be surprised if COUNT(DISTINCT PostId) would be faster than a simple COUNT(*) on another table with much less records.
Unfortunatey, I didn't save the query :(
 
@Glorfindel I have tried such query in my answer.
It did not time out.
 
@Feeds answered by me
 
@Martin thanks, I'll have another look
 
Other than using COUNT(DISTINCT (v.PostId)), the other difference is that I count the ratios form #result.
 
@Feeds answered by Robert Longson
 
12:07 PM
I.e., I use ROUND(CAST(countupvotes AS decimal)/CAST(countposts AS decimal), 2) in the final query rather than putting this into the query which is run on each site.
 
I do not know whether this might influence the speed in some way.
 
oh wait
I realize I made a big blunder.
i.e. not using ' + quotename(name) + '.dbo.
 
I got the different number as you - but some difference between COUNT(DISTINCT PostId) from Votes and COUNT(Id) from Posts is to be expected.
 
@Glorfindel been there, done that ;)
 
12:10 PM
Of course, if you manage to get the multisite query with the correct numbers, then my query is no longer needed.
I simply thought that using query which only looks into Votes (rather than a query which uses both Votes and Posts) might make a slight difference, so I tried this.
 
of course Meta.SE is the most unwelcoming site in the network :P
 
Does the Votes table contain also votes on deleted posts?
Just asking whether that should be compared with the count from Posts or from PostsWithDeleted.
 
Thanks for updating the query in that answer.
I guess time for me to get some lunch. See you later!
 
while time is stripped wouldn't it be interesting to see # votes on the 1 st day, 2nd day, etc?
 
12:58 PM
@rene Isn't that going to be almost surely something with big peak on the first day and much smaller numbers on other days? Like here: data.stackexchange.com/mathoverflow/query/1236796/…
I have tried to check the what percentage of upvotes come on each day. What I get looks simply wrong to me: data.stackexchange.com/mathoverflow/query/edit/1359845#graph
Sorry it was supposed to be this link: data.stackexchange.com/mathoverflow/query/1359845#graph
When I included PostsWithDeleted, it looks a bit more believable: data.stackexchange.com/mathoverflow/query/1359847#graph
 
1:26 PM
@Martin yeah, that is very likely.
 

« first day (906 days earlier)      last day (372 days later) »