5
$\begingroup$

As I said yesterday in chat, I've got plenty of free time over the coming weeks, so I'm going to try to work on something useful for us. Specifically, SEDE.

What statistics could we make use of here? What do we want to know about the habits of the community?

You can look at the database schema on the right hand side of this page on SEDE to get an idea of what data we might have access to.

What queries would be useful?

(Please note: if you're looking for the queries themselves, they are now in this meta post)

$\endgroup$
9
  • 1
    $\begingroup$ There may be some queries worth mining and/or adapting here: meta.workplace.stackexchange.com/q/2268/325 (see the "community health queries" in particular). $\endgroup$
    – Monica Cellio
    Apr 14, 2015 at 16:01
  • $\begingroup$ Wow, I did not know about SEDE until today, but I am really excited to mess with it now. I wrote a query for the difference between average rep per week and rep for this week, only to find that posts don't seem to be up-to-date. That, and Serban Tanasa earns a lot of reputation. $\endgroup$
    – DaaaahWhoosh
    Apr 14, 2015 at 19:39
  • $\begingroup$ @DaaaahWhoosh SEDE gets updated every week, it's not realtime data. That's why a lot of queries you see are per-week $\endgroup$
    – ArtOfCode
    Apr 14, 2015 at 20:13
  • $\begingroup$ @ArtOfCode Yeah, that's what I assumed. Anyway, I modified my query to deal with things seven to fourteen days ago, and that seems to give me useful numbers. $\endgroup$
    – DaaaahWhoosh
    Apr 14, 2015 at 20:16
  • $\begingroup$ @DaaaahWhoosh Hmmm, I like it. Feel free to go edit it into the queries post $\endgroup$
    – ArtOfCode
    Apr 14, 2015 at 20:33
  • $\begingroup$ A general suggestion for all the queries - if we output zeros from the SQL for colulmns where there are 0 then the graph will drop between data points. i.e. the Flora tag graph line is misleading as it stands. $\endgroup$
    – Tim B
    Apr 15, 2015 at 8:39
  • $\begingroup$ @TimB I did notice that but my SQL is not up to that standard $\endgroup$
    – ArtOfCode
    Apr 15, 2015 at 8:45
  • $\begingroup$ @ArtOfCode stackoverflow.com/questions/16636433/… $\endgroup$
    – Tim B
    Apr 15, 2015 at 9:17
  • $\begingroup$ @TimB no luck, even with StackOverflow's help $\endgroup$
    – ArtOfCode
    Apr 15, 2015 at 9:41

5 Answers 5

Reset to default
4
$\begingroup$

Promising new users:

With our sometimes-high volumes, it can be easy to miss that newcomer who gave a decent answer to a question or two and hasn't participated more yet. We want to find and encourage users like this. So a query that finds users with, say, 3 or fewer posts, some upvotes, and maybe none (net) downvoted would be helpful.

$\endgroup$
4
  • $\begingroup$ I wrote this just now, not sure if it's what you were looking for. Either way, it was fun to write :) $\endgroup$
    – DaaaahWhoosh
    Apr 14, 2015 at 20:11
  • $\begingroup$ @DaaaahWhoosh thanks! The SQL is a little beyond me, so a question: it looks like it's reporting each user as having exactly one question or answer -- but I don't see multiple rows, so what's going on with new users with multiple posts? (We have some, right?) Also, could you make the user name a link to the profile? $\endgroup$
    – Monica Cellio
    Apr 14, 2015 at 20:18
  • $\begingroup$ There are users with both questions and answers, and with multiples of each, they just aren't as common for small 'daysSinceJoined' values. And I just added the links. $\endgroup$
    – DaaaahWhoosh
    Apr 14, 2015 at 20:24
  • $\begingroup$ @DaaaahWhoosh oh ok; I needed to look farther. I see it now. Thanks for the info and the update! $\endgroup$
    – Monica Cellio
    Apr 14, 2015 at 20:38
2
$\begingroup$

Something I'd be interested in seeing is how the average up and down votes per question has varied over time. I've a feeling votes were generally easier to come by earlier on in the life of the site but I've not seen any solid evidence to support that.

$\endgroup$
1
2
$\begingroup$

Another thing I'm curious about is looking at the distribution of questions with how many answers.

i.e. how many questions have 1 answer, how many 2, how many 3, etc

$\endgroup$
4
  • $\begingroup$ Is Number of questions with respective number of answers anywhere near what you were looking for? (Naming is hard!) $\endgroup$
    – user
    Apr 14, 2015 at 18:26
  • $\begingroup$ @MichaelKjörling Yep - with two tweaks. Excluding closed questions and ideally displayed as a bar chart not a line graph. $\endgroup$
    – Tim B
    Apr 14, 2015 at 18:56
  • $\begingroup$ Fixed. Unfortunately SEDE doesn't seem to support bar charts, and doing it manually (at least the way I tried it) breaks charting. $\endgroup$
    – user
    Apr 14, 2015 at 19:52
  • $\begingroup$ Ok, that's interesting. So it seems most of our questions have 3 answers, then it tails off gradually with an odd spike at 10/11. We have a question with 37 answers? :o The several between 20 and 30 doesn't really surprise me but 37 :o $\endgroup$
    – Tim B
    Apr 15, 2015 at 8:31
2
$\begingroup$

Questions that have gotten "a lot" (TBD) of answers in "a short period of time" (TBD). I speculate that when this happens (e.g. with hot network questions), we end up with two kinds of answers we might want to address: duplicates and one-liners. If that hypothesis holds, then a query to find these questions so the community can figure out what needs to be cleaned up on them would be helpful.

$\endgroup$
4
  • $\begingroup$ How about 40% or more of current answers added in the last 48 hours, and that's a minimum of, say, five answers added? It'd take a bit of SQL trickery to accomplish that, but it would definitely help catch questions that are quickly gathering additional answers without setting any real fixed limit, and it could catch them before they generate autoflags. $\endgroup$
    – user
    Apr 14, 2015 at 18:03
  • $\begingroup$ We could perhaps also weigh answers inversely by length and score, such that poor answers carry more weight in the calculation. This assumes that votes or length is a reasonable measurement by proxy for how good the answer is (low quality answers being those that are short, have a low score, or both), which seems a reasonable first-order approximation. $\endgroup$
    – user
    Apr 14, 2015 at 18:04
  • $\begingroup$ @MichaelKjörling remember that SEDE operates on historical data, not the live site, so think of durations or date ranges, not "last N hours/days". (Factoring in length and score sounds helpful.) $\endgroup$
    – Monica Cellio
    Apr 14, 2015 at 18:11
  • $\begingroup$ Oh, right about historical data. I guess I'm spoiled! $\endgroup$
    – user
    Apr 14, 2015 at 18:11
1
$\begingroup$

How much a tag has been used over a period of time, and cross-tag comparisons.

This could let us see which tags are becoming popular and should be monitored, which tags are no longer being used and should potentially be phased out, and which tags are used in conjunction with each other.

This could also be used to analyze tags before, during and after Weekly Topic Challenges.

$\endgroup$
6
  • $\begingroup$ This is completed now; see the data queries post under "Tags". $\endgroup$
    – ArtOfCode
    Apr 14, 2015 at 21:06
  • $\begingroup$ @ArtOfCode Oh, good. $\endgroup$
    – HDE 226868 Mod
    Apr 14, 2015 at 21:07
  • $\begingroup$ And I'm seeing some very indicative results that the topic challenges really do boost tags $\endgroup$
    – ArtOfCode
    Apr 14, 2015 at 21:11
  • $\begingroup$ @ArtOfCode No kidding. flora had a huge explosion. $\endgroup$
    – HDE 226868 Mod
    Apr 14, 2015 at 21:17
  • $\begingroup$ You haven't seen creature-design yet... $\endgroup$
    – ArtOfCode
    Apr 14, 2015 at 21:20
  • $\begingroup$ @ArtOfCode I actually had, but . . . yeah. It's incredible. $\endgroup$
    – HDE 226868 Mod
    Apr 14, 2015 at 21:21

You must log in to answer this question.

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