Finding Unanswered StackExchange Questions with SQL

You love Q&A sites like StackOverflow.com and DBA.StackExchange.com, but sometimes it’s hard to find interesting questions that need to be answered. So many people just sit around hitting refresh, knocking out the new incoming questions as soon as they come in. What’s a database person to do?

Use the power of the SQL.

Data.StackExchange.com lets you run real T-SQL queries against a recently restored copy of the StackExchange databases. Here’s my super-secret 3-step process to find questions that I have a shot at answering.

Step 1. Find out how old the restored database is.

Run this query to get a list of site databases and the newest post date in each one:

Most recent posts in each database

Most recent posts in each database

I take a glance at StackExchange.Dba and StackOverflow to make sure I’m dealing with a relatively recent database restore. These database restores are done weekly, but you know how it goes – sometimes scheduled jobs fail.

Step 2. Find questions everybody’s talking about.

Run this query to find questions with many comments, but no accepted answer. Note that for this one, you do have to pick the site you want to focus on – this doesn’t run across all databases.

Questions with many comments but no accepted answer

Questions with many comments but no accepted answer

If you click on the Post Link, you’re taken to the live question. If the database restore was a few days ago, keep in mind that the live question may have changed or been answered by now.

The other columns help me see at a glance if this is a question I’m interested in. In the above screenshot, the third question, “New database server hardware,” has 2 answers, but none of them have been upvoted, and there hasn’t been any comments since 2014-05-13. It’s old, and the questioner has probably moved on, but you can revive the question by posting a really good answer. You can also take your time, knowing nobody else is really active on it right now. These are great opportunities to post a really in-depth answer for other folks to find as they search the web later.

Which brings me to my next favorite query:

Step 3. Find questions that people keep looking at.

Run this query to find questions with many views, but no accepted answer. This one is filtered by a specific tag because the SQL Server ones don’t usually show up in the top views. Use tags from the DBA.se tag list or the StackOverflow tag list.

Most-viewed unanswered questions for a tag

Most-viewed unanswered questions for a tag

Obscure error numbers are usually going to pop up here because there’s not much public information about ’em. Now’s your chance to write an answer explaining how to troubleshoot that error number, and presto, you’re the next Jon Skeet.

Previous Post
Teaching Tips: Help Your Audience Be Active Learners
Next Post
Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary

2 Comments. Leave new

Menu
{"cart_token":"","hash":"","cart_data":""}