Data Explorer (SEDE)

« first day (906 days earlier) ← previous day next day → last day (1426 days later) »

Martin

11:48

A: Do we use the downvote button more than other sites?

We 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

12:02

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

@Glorfindel I have tried such query in my answer.

It did not time out.

Here is link to the query: data.stackexchange.com/mathoverflow/query/1359819/…

rene

@Feeds answered by me

Glorfindel

@Martin thanks, I'll have another look

Martin

Other than using COUNT(DISTINCT (v.PostId)), the other difference is that I count the ratios form #result.

rene

@Feeds answered by Robert Longson

Martin

12:07

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

→ 2 messages moved to Sandbox/Trash Bin/Something

Martin

I do not know whether this might influence the speed in some way.

Glorfindel

oh wait

I realize I made a big blunder.

i.e. not using ' + quotename(name) + '.dbo.

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

@Glorfindel been there, done that ;)

Martin

12:10

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

data.stackexchange.com/mathoverflow/query/1359832/…

of course Meta.SE is the most unwelcoming site in the network :P

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

yes it does

see data.stackexchange.com/meta.stackexchange/query/1359805

Martin

Thanks for updating the query in that answer.

I guess time for me to get some lunch. See you later!

rene

while time is stripped wouldn't it be interesting to see # votes on the 1 st day, 2nd day, etc?

Martin

12:58

@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/…

data.stackexchange.com/mathoverflow/query/1236798/…

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

13:26

@Martin yeah, that is very likely.

« first day (906 days earlier) ← previous day next day → last day (1426 days later) »

all rooms