Data Explorer (SEDE)

8:21 AM

0

Q: How many posts are down voted without a comment?

A returning theme on our beautiful and productive Meta sites is the topic on Down voting without leaving a comment. Yes, that is a bit cheeky and admit it: you've done that as well. In a recent post it was claimed that 90% of the down votes weren't accompanied by a comment. That percentage seeme...

Martin

9:14 AM

Let me copy here a few comments for some context:

It would be interesting to see a tag's quantile, with tags sorted according to the above indicators. — Arnaud Mortier 23 hours ago

@ArnaudMortier Do you mean something similar to the query from this answer: Best and worst tags for % answered rate? The query from that post shows percentage of unanswered questions for various tags. So what you're looking is something similar, but with percentage of closed/deleted questions? — Martin Sleziak 23 hours ago

I was thinking of replacing UnansweredCount with DeletedCount or something like that. I'll try to see if I can figure it out by looking at the syntax of other queries. Thanks for the great help! — Arnaud Mortier 23 hours ago

@ArnaudMortier For closed questions I found a query in an older post on meta: Which tags have the largest proportion of closed questions? It seems that this query does not include duplicate close votes, only other closures. I have tried to create a query of my own which can be restricted by date range and counts also duplicates. — Martin Sleziak 1 min ago

I think that creating similar query for deleted questions might be more difficult. Or at least it cannot be done in a similar way as the above query for closed posts - the PostTags table does not contain deleted posts. I have asked in the SEDE chatroom — Martin Sleziak 1 min ago

Unless I mixed up something, the queries linked in the last comment should show percentage of closed questions for various tags.

I can get percentage of deleted questions for a single tag - I just look in PostWithDeleted and divide (COUNT(p.DeletionDate) by (COUNT(p.Id).

But if I want to do something similar for multiple tags, I cannot do this the same way as I did for closed questions - PostTags does not include deleted questions.

The linked post suggests some workaround how to get tags for deleted posts, but that goes above my SQL skills.

Question. Is it possible to get percentages of deleted questions for various tags in the same query - similarly as here and here for closed questions?

It is not too important, but if somebody knows how to do it and has some spare time, just le tme know.

rene

12:30 PM

@Martin what you can do is a STRING_SPLIT(Tags,'<') as that gives you a table with tags as if you had a posttags table. Do replace(tags,'>','') in the projection to see proper tagnames.

Tags in the field in the postswithdeleted

Martin

I tried do that as a CTE, but it did not work - maybe I did something wrong there.

I tried something along these lines:

; with tags_cte
(SELECT
  Id PostId,
  value TagName
FROM
  PostsWithDeleted
  CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(Tags,'><',','),'<',''),'>',''),',')
)

And then I tried using this cte in further queries. But I got some errors.

rene

CROSS APPLY STRING_SPLIT(Tags,'<')

and then move the replace around that Value you have in the select

Martin

So with CROSS APPLY STRING_SPLIT(Tags,'<') I get at least pairs between post id and tagname, but still with calculus> instead of calculus. data.stackexchange.com/math/query/1162634/…

rene

this works as a replcament for that CTE data.stackexchange.com/mathoverflow/query/1162640

Martin

Ok, so at least the initial CTE is working: data.stackexchange.com/math/query/1162642/…

Thanks for that - let me now try to use this to obtain the percentages.

With your help I managed to do at least something: Percentage of deleted question per tag.

rene

12:47 PM

Awesome!

Martin

Unless I made some mistake, I think this should be what I wanted - percentages of deltes posts.

Maybe more interesting results are if the query is restricted to bigger tags: data.stackexchange.com/math/query/1162648/…

I did not save the query which I was trying yesterday. (And where I was getting errors.)

I was trying to use CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(Tags,'><',','),'<',''),'>',''),',') which I copied from Jason C's answer.

Now I suspect that reason why the query I was trying yesterday might have been something else - and there was no problem with the cte.

I have to admit that I am using CROSS APPLY without knowing what it does - simply using the syntax that you have shown me (and that I've seen in posts on meta). But I'll try to read a bit more about it, when I have some time.

Thanks again!

rene

1:29 PM

@Martin think cross apply if you want a Cartesian product of the rows of two tables. In this case you want a cartesian product for each row in the posts table and the values from its tags field (which table is obtained from the string_split function).

@Feeds I asked and answered the question

rene

3:08 PM

@Martin if you have time would you fancy to write a query and post an answer for this suggestion:

Wouldn’t it be easier to compare the number of downvotes a user has with the number of posts they’ve commented on and aggregate it across users? If there is a problem with people leaving a lot of downvotes without explanation, that would be a rough estimate of the magnitude of the problem, and whether it’s a lot of people doing it a few times or a few users doing it a lot. We should leverage the fact that users can only vote once per post. Don’t ask me to write the query for it though; I can barely remember how to join. ;) — ColleenV parted ways 5 mins ago

I don't want to post another answer (or a write a query for it) myself

Feeds

0

Q: Is it ok to ask question that could be solved with a SEDE query?

If there is some information in SO that I want to know and definitely can be done with SEDE, should I ask it here?

discussion data-explorer

Martin

3:53 PM

To be honest, it's a bit unclear to me what ColleenV's comment is trying to say.

There are some users who have downvotes and no comments, we can find those: Users with no comments.

There are some users who have many downvotes and only a few comments, we can find those: Ratio between downvoted and commented posts.

So is the question whether there are many users with high ratio between number of downvotes and number of comments (or number of posts where they commented)?

Although I suspect this might be a bit misleading - since some users might have many downvotes and many comments, but those the comments and downvotes are on different posts. (Which we cannot find out from SEDE.)

Maybe if we restrict this to users which have more at least 100 downvotes, we would even be able to get the numbers for all of them: data.stackexchange.com/stackoverflow/query/1162790/…

Probably it would make sense to ask ColleenV what she means - and whether the above queries are a step in the right direction.

But before pinging here I'll wait a bit to see whether somebody else has better idea of what that comment is actually suggesting.

Sonic the Reinstate Monica-hog

4:31 PM

@rene Trying to fix the links in this answer to be valid links to SEDE. However, I ran into an issue because locate is undefined.

Tried replacing the instance in the first one with charindex(…) = -1, but it just displayed a blank row.

rene

4:43 PM

@SonictheReinstateMonica-hog charindex is 1 based, so it returns 0 if nothing is found.

Sonic the Reinstate Monica-hog

@rene Thanks. Is it just a drop-in replacement for locate, then?

rene

looks like it, yes.

Wasn't locate a function in DbaseIII? Or Foxpro.

@Martin yes, I think that is what is meant, and then we need those ratio's per user to aggregate into a number for a whole site.

@Martin yeah, there are all kind of issues with obtaining that info. I don't think an exact number is needed. If we have something that says reliably: about 20% of all downvotes come without an comment then I think I reached my goal.

When someone comes to meta claiming a 90% number I want to be able to say: whatever the number is, it is not 90%.

Martin

It's a bit unclear to me what is meant by aggregate into a number for the whole site.

Simply calculating SUM(u.DownVotes) and SUM(CommentedPosts)?

Or something like average ratio?

rene

well, that is always what I find tricky. But one of those, yes.

I have the feeling summing / avgeraging ratios don't work well for a large set where you have many outliers

I hoped you would know how to solve that ;)

Martin

Still, my query is missing all users which have no comments. Those are in the other query I linked: data.stackexchange.com/stackoverflow/query/1162772/…

rene

4:56 PM

@Martin I think that one should work

@Martin you can do a left outer join

instead of the inner join

but then you have to come up with a solution to prevent a divsion by zero as count(distinct c.postid) will then return 0 for those user without comments

Martin

You mean using left outer joint to get the users with no comments?

rene

yes

an outer join gives you all elements from set A and only those from set B where the join clause is true

the left and right determines what A and B will be

Martin

5:24 PM

Ratio between downvoted and commented posts (including users with no comments)

Total number of downvotes and commented posts

Although I am getting the impression that by summing them together we are simply calculating in a more complicated way something which could be found in an easier way. At least Total Downvotes are easy to calculate without this.

Probably the first query tells me something about habits of users - since I can see, for example, how many users have ration over 100.

@ColleenV When you are around, could you check whether the above queries are in the direction what you intended.

The first one gets for each users the number of downvotes and the number of posts where the user commented.

I restricted it to users who have at least 100 downvotes, otherwise the query would run out of time.

I have invite ColleenV to this room - so she should get some notification.

ColleenV parted ways

5:40 PM

@Martin I was thinking vaguely of binning the users together statistically. For example, 10% of users have significantly more downvotes than posts they’ve commented on. Or, the median user comments on posts twice as often as they downvoted them

I haven’t really thought it through

Martin

I should have rounded the ratio to a few decimal spaces, the query is now edited in that way.

ColleenV parted ways

It may require taking the results of the query and analyzing them outside of SEDE

Martin

Is the ratio at least somewhat in the direction you wanted?

By "binning together" you mean something like counting the users who have ratio above 100, between 10 and 100, between 1 and 10, etc.?

ColleenV parted ways

Yes something like that

There might be a need to weight based on number of posts somehow

but maybe not the first cut

It would be interesting to know if most users sometimes comment and sometimes don’t, or if there are two groups, those that almost always comment, and those that almost always don’t

So maybe a median measure of downvoting with out comment and the distribution of users relative to the median

Martin

6:01 PM

I have checked number of users100 withing some categories in a rather naive way: data.stackexchange.com/stackoverflow/query/1162887/test

There are 23 users with ratio above 100, 397 between 10 and 100, 6083 between 1 and 10, 9160 between 0.1 and 1 and 494 below 0.1.

I'll probably have to leave further attempts for somebody with better grasp of ways to analyzing data (and also with better SQL skills).

Sorry about that.

Transcript for

Data Explorer (SEDE)