Useful data.stackexchange.com queries for regular users of the site.

Question

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.

Answer 1

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? )

Answer 2

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

Answer 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

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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

Answer 10

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:

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

Answer 11

The Critics: most downvotes of total number of cast votes

Voting is fun, and downvoting is even more fun.

User list: Downvotes by percentage

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