18

The Stack Exchange Data Explorer is a great tool. I've noticed many queries regarding site statistics, top lists, and also user-specific queries such as badge progress.

Nice examples giving insights about the TeX.SX site and its content are:

I thought about TeX related queries, involving tags, votes, and views or even syntax for example. They could be a demonstration of how we could use the data explorer.

What ideas do people have for a useful, interesting, TeX-related query? And what queries have people created?

For those interested in creating queries, here's a useful "question" on meta.SO about the database schema; moreover there are quite a few questions on meta.SO about the data explorer, they are generally tagged "data-explorer" so a good place to start is with this search.

  • This is exactly the type of question I've been wondering about for a while (after posting about Site usage statistics), but haven't asked yet - thanks! – Werner Oct 7 '11 at 22:30
  • 3
    Is there a good tutorial on creating queries for the query-language impaired? – Alan Munn Oct 7 '11 at 23:39
  • @AlanMunn: Unfortunately not.. Queries are composed as SQL queries, and the table/column names to choose from are on the right side of the page when you go to Edit/Create new. Take a look at this page from Wikibooks, where the SELECT command syntax is explained - pretty much all you need to know from SQL to use the Data Explorer. You need also some grasp on how relational databases are organized. Also look at other (similar or not) queries in the Data Explorer for some additional insight. – Martin Tapankov Oct 8 '11 at 6:21
11

I just created some queries:

Of course there could be much more sophisticated queries than these one-liners.

4

I've been trying out the explorer a little (ably aided by Paulo Cereda). The focus of my queries is to try to build up a profile of the site. My particular hypothesis is that a healthy SE site is one where there is a single population composed of questioners-and-answerers, and that an unhealthy SE site is one where there is a population of questioners and a separate population of answerers. To that end, I'm trying to develop a query that will give a "profile" of the population in terms of people's question-to-answer "ratio" (which I need to define). My theory is that TeX-SX will score very highly in this regard (with a few obvious outliers .. naming no names, *cough* egreg *cough*) and that some other sites with better numbers of questions and views will not fare so well.

I have not made much progress as of yet (mainly because I find the syntax a bit opaque). You can see my list of saved queries on the explorer. Not all are for this project. At time of writing they include:

  1. Number of non-duplicate closed questions. The purpose here is to measure how "well defined" a site is. If it is clear to the casual user what is or isn't on topic, there will be a low rate of questions closed for reasons other than duplication. We score well on this, compared to other sites.

  2. Proportion of answerers. This looks at all people with reputation over 1000 and looks at the proportion of those who have never asked a question. This is a very crude measure of what I describe above.

4
  • 1
    If you want the truly aspirational badges, aim for the Beta and Precognitive badges. :-) – Alan Munn Oct 11 '11 at 13:47
  • @AlanMunn: That might well turn out to be a rather transpirational task ... – doncherry Oct 11 '11 at 15:41
3

Perhaps consider this CW "answer" as a place where non-SQL-savvy enthusiasts of TeX.SX can post their interests in possible queries that SQL-savvy enthusiast can play around with. Add a complete description of the query requirement so that it is clear what should be included in the result. Either edit this post with a link to the new query, or cut it from this post and repost as a different answer:

  • Number of votes on the site per time frame

    Tally the number of votes on questions and answers in a specific time frame (say, monthly). Display fields that show Date, Question votes, Answer votes and Total votes.

  • Amount of reputation dolled per time frame

    Tally the total reputation earned/lost on the site in a specific time frame (say, monthly). Display fields that show Date, +Reputation, -Reputation and Total reputation.

    Some examples:

    • upvoting a question would add 10 to +Reputation
    • downvoting a question would add 2 (or -2) to -Reputation while downvoting an answer would add 3 (or -3) to -Reputation (see the FAQ on reputation)
    • accepting an answer would add 17 to +Reputation

You must log in to answer this question.

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