12

There's a web site at http://data.stackexchange.com/ where one can run SQL queries against the database to make some interesting reports. Some of these are things like "What do I need to get a certain badge", which is cool. But there's others that I think could benefit the site as a whole.

That is, queries that provide useful information for users looking for somewhere to contribute, or else which display data from the site in a way that showcases the great content we have.

Note that data is updated monthly. So, queries may be less useful because the results are out of date. Maybe some of the more useful ones could eventually become Actual Site Features.

Since I don't know anywhere else to put them, I figured I'd put them here as a kind of community-wiki post, although I don't see a CW checkbox. If there's somewhere else this should go, or if this can be made CW, that'd be cool.

  • 3
    I did not know you could directly run SQL against SE, that is uber nerdy and awesome. – dpollitt May 10 '11 at 2:11

11 Answers 11

7

Active Users with Low Participation in Voting

Users active in the last two months with reputation over 500 and less than one vote per day since joining the site.

If you're on this list, consider a voting a little more.

http://data.stackexchange.com/photography/s/1181/active-users-with-low-participation-in-voting

Related:

Photo.SE Voter Index

An index rating of all voters at Photo.SE, ranked by their ratio of votes to one-tenth their reputation. Users with more than 100% ratio will be ranked positive, while users with less than 100% ratio will be ranked negative. Negative voters should work on their voting to fully integrate into the community.

http://data.stackexchange.com/photography/s/732/photo-se-voter-index

(See Why aren't people voting? )

5

Nice Questions without Nice Answers

All non-CW questions with scored at least 10 with no non-CW answer that high.

Basically, these are things people are interested in knowing, where the results haven't been satisfying to the community, either because the question was overlooked or because higher-quality answers could be provided.

(Thanks to Evan Krall for this one.)

http://data.stackexchange.com/photography/s/1162/nice-questions-without-nice-answers

  • Fascinating results. This may be a function of the smallness of this site. For a while I have been perturbed by the low voting patterns. I would prefer to normalise the results by showing them as a percentage of the number of views (by registered members, since only they can vote) the questions received. A while ago a another query showed that high reputation members had low voting patters. Maybe we should re-open that debate. – labnut Apr 1 '11 at 8:22
  • Another thought, perhaps we could address the low voter turnout by allowing anonymous(unregistered) votes? – labnut Apr 1 '11 at 8:25
  • I think tying voting to reputation is a huge reason Stack Exchanges works. And, while I agree we could do better on voting, I actually think we do pretty well compared to the other SE site I'm really active on (unix.stackexchange.com). Hmmm, a query comparing voting across SE sites would be interesting! But I'm also going to add the voter list query here. – Please Read Profile Apr 1 '11 at 17:22
3

Top 100 Answers of All Time

There's an easy link for Questions by Vote — what about the top (non-CW) answers?

http://data.stackexchange.com/photography/s/1159/top-100-answers-of-all-time

  • I'd like to see a list of questions most marked as favourite question. "Top 100 most favourited questions". But I can't make the code, so asking here. – Esa Paulasto Apr 12 '13 at 7:38
  • 1
    @Esa That's not really a comment on "Top 100 Answers of All Time" :) Someone made a query like that already; try data.stackexchange.com/photography/query/90759/… or data.stackexchange.com/photography/query/85906/…. It's fairly similar to the list of top 100 questions already. (Maybe "highly-favorited questions not in the top voting" would be more interesting.) – Please Read Profile Apr 12 '13 at 13:04
  • Distantly related tough. Thanks for those links, both are just what I wanted to see. – Esa Paulasto Apr 12 '13 at 21:10
2

Badges to Aspire To

Forget Gold / Silver / Bronze — how often are these badges really being handed out? Score is relative to the most common badge. Does not include tag badges.

http://data.stackexchange.com/photography/s/1306/badges-to-aspire-to

Since badges are created to encourage certain desired behavior, it follows that, if you're looking for something to do, trying to get some of the badges high on this list would be a decent way to spend some time. While Tumbleweed might not really fit (since we're so good at making sure every question gets addressed!), it looks like we could, for example, benefit from more participation in chat (Outspoken) and more offering of bounties (Altruist and Investor — rarely-awarded bronze badges.)

And of course there's a few badges no one has yet — those aren't in the database, so it doesn't hurt to just look at the list, too. As the site matures and the community grows larger, the big numbers required for most of those missing badges will turn up eventually.


I also made Users by Weighted Badge Score; this is less useful for making the site as a whole better, but is kind of fun as a game. Basically, it's the top 100 users ranked by the weighted value of the badges they've earned.

1

Popular Questions without Nice Answers

All non-CW questions with at least 1000 views but no non-CW answer scored at least 10.

http://data.stackexchange.com/photography/s/1217/popular-questions-without-nice-answers

1

Most controversial posts on the site

Looks for posts with more than half the amount of downvotes as they have upvotes.

http://data.stackexchange.com/photography/s/87/most-controversial-posts-on-the-site

Controversy is usually interesting. :) More experts looking at these questions and answers should help clarify the situation.

This particular query could probably be improved to focus more closely on the posts which are useful to review from that point of view.

1

Who Brings in the Crowds?

Users sorted by total number of views of their questions per day (with a slight softener to keep very new posts from skewing the results).

http://data.stackexchange.com/photography/s/1316/who-brings-in-the-crowds

Of course, good answers are the most important, but there's no way to actually see view stats for those, since they're all part of the same page. And questions are the "top level" thing that come up in search results and so on. So this is an interesting view into the user base.

1

Questions ripe for the Revival and Necromancer badges

Searches for open, non-CW posts older than 60 days with no accepted answer and no answer scoring 2 or more.

http://data.stackexchange.com/photography/query/56730/find-questions-ripe-for-the-revival-and-necromancer-badges

We have close to a 100% answer rate on the site, but some of the old questions could use better answers, or maybe just more attention. And you can get a shiny, shiny trinket in your user profile as a result.

1

Tags missing a tag wiki or tag excerpt

This one shows tag wikis or tag wiki excerpts which are blank, sorted by use. Filling out the top ones would be a worthwhile activity.

http://data.stackexchange.com/photography/query/157704/tags-missing-a-tag-wiki-or-tag-excerpt-sorted-by-use

0

Who are the most concise users in the high reputation group (>2000)?

http://data.stackexchange.com/photography/q/4432/keystrokes-per-rep-point

And the prize goes to the following 9 people who earned a high reputation with few words:

enter image description here
The above query divides keystrokes by reputation and the resulting number is the number of keystrokes to earn one reputation point.

  • 3
    I'm curious about this one — is being concise a goal, or are great long answers equally valid? And I'm not just saying that because I'm on the wordy end of the list. :) – Please Read Profile May 4 '11 at 13:40
0

The Critics: most downvotes of total number of cast votes

Voting is fun, and downvoting is even more fun.

User list: Downvotes by percentage

enter image description here

Highest percentages of downvotes/total number of votes, when user has cast min. 25 downvotes.

You must log in to answer this question.

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