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

Martin
Martin
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.)
 
Glorfindel
Glorfindel
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 :(
 
Martin
Martin
@Glorfindel I have tried such query in my answer.
It did not time out.
 
rene
rene
@Feeds answered by me
 
Glorfindel
Glorfindel
@Martin thanks, I'll have another look
 
Martin
Martin
Other than using COUNT(DISTINCT (v.PostId)), the other difference is that I count the ratios form #result.
 
rene
rene
@Feeds answered by Robert Longson
 
Martin
Martin
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.
 
rene
rene
 
Martin
Martin
I do not know whether this might influence the speed in some way.
 
Glorfindel
Glorfindel
oh wait
I realize I made a big blunder.
i.e. not using ' + quotename(name) + '.dbo.
 
Martin
Martin
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.
 
rene
rene
@Glorfindel been there, done that ;)
 
Martin
Martin
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.
 
Glorfindel
Glorfindel
of course Meta.SE is the most unwelcoming site in the network :P
 
Martin
Martin
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.
 
Glorfindel
Glorfindel
yes it does
 
Martin
Martin
Thanks for updating the query in that answer.
I guess time for me to get some lunch. See you later!
 
rene
rene
while time is stripped wouldn't it be interesting to see # votes on the 1 st day, 2nd day, etc?
 
Martin
Martin
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
 
rene
rene
1:26 PM
@Martin yeah, that is very likely.
 

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