See Also: Main Page - Business Intelligence - Data Mining the StackOverflow Database

Want to answer StackOverflow questions to boost your reputation, but you can't find any old ones that are easy enough for you to answer? This query will hunt them out. You can change these things in the WHERE clause:

• p.Tags LIKE '%sql%' - change the sql keyword to one you're interested in.
• p.AnswerCount <= 1 - only one (or zero) person made a suggested answer, but since p.AcceptedAnswerId IS NULL, we know the answer wasn't accepted.
• p.CommentCount <= 1 - only one (or zero) comments were made, which means it didn't catch anybody's interest.

We sort by p.LastActivityDate because we want to find the questions that fell off the radar. The older they are, the easier they are to swoop in and answer. Plus, keep in mind that the StackOverflow database is only exported periodically - newer questions are more likely to have already been answered by someone since the database export was made.

T-SQL Query

Hover your mouse over the query and you'll see buttons at the top right that allow you to copy/paste the query easily or display it in a new window. MAKE SURE YOU CHANGE THE URL NAME if you're querying a database other than StackOverflow - for example, if you're querying the ServerFault database, change the http://stackoverflow.com link in the query to http://serverfault.com.

SELECT p.Title, p.Tags, 
  'http://stackoverflow.com/questions/' + CAST(p.Id AS VARCHAR(10)) AS URL,
  p.Body, p.LastActivityDate
  FROM dbo.Posts p
  WHERE p.Tags LIKE '%sql%' AND p.CreationDate <= DATEADD(mm, -2, GETDATE()) AND p.AcceptedAnswerId IS NULL AND p.AnswerCount = 1 AND p.CommentCount <= 1
  AND p.ClosedDate IS NULL
  ORDER BY p.LastActivityDate

Results

Here's a screenshot of example results. When you find a question that looks interesting, simply copy the URL into your browser and start earning reputation points.

Variation: Questions Asked by Active Users

If you want to be more selective, you can filter for questions that were asked by a user who has logged in in the last couple of months and has a reputation over 100. These users might be more likely to accept your answer, thereby giving you more reputation, as opposed to questions asked by users who have since abandoned the site.

SELECT p.Title, p.Tags, 
  'http://serverfault.com/questions/' + CAST(p.Id AS VARCHAR(10)) AS URL,
  p.Body, p.LastActivityDate
  FROM dbo.Posts p
    INNER JOIN dbo.Users u ON p.OwnerUserId = u.Id
  WHERE p.Tags LIKE '%sql%' AND p.CreationDate <= DATEADD(mm, -2, GETDATE()) AND p.AcceptedAnswerId IS NULL AND p.AnswerCount = 1 AND p.CommentCount <= 1
  AND p.ClosedDate IS NULL
  AND u.LastAccessDate >= DATEADD(mm, -2, GETDATE()) AND u.Reputation >= 100
  ORDER BY p.LastActivityDate

Related Articles About the StackOverflow Database

• How to Import the StackOverflow XML into SQL Server
• Understanding the StackOverflow Database Schema
• Interesting StackOverflow Database Queries
• Data Mining with Excel - explains the basic concepts behind using Excel 2007 and SQL Server to do data mining. Not specific to StackOverflow, just general concepts.