« A Metro-Style Dashboard as a PowerPoint Slide | Main | Interlude: The Comic Sans Song by gunnarolla »
Friday
Jun012012

Finding all Visio Questions on StackOverflow using the DataExplorer  

The wonderful StackOverflow team has been making it easy to look through their data using their Data Explorer (read more about it here). I am a frequent visitor to StackOverflow (and SuperUser) and a fan of Visio and wanted to take an opportunity to learn about the Data Explorer and how I could use it to learn something about the kind of questions asked about Visio. A further analysis is come soon, so until then here is a link to the query:

http://data.stackexchange.com/stackoverflow/query/71597

Here is the query as of 2012–06–01.

WITH TargetTags AS (
  SELECT Tags.Id
  FROM Tags
  WHERE Tags.TagName = 'visio'
)

Select
  Posts.Id,
  'http://stackoverflow.com/questions/' + REPLACE(STR(Posts.Id),' ', '') As PostURL,
  Posts.Title,
  Posts.OwnerDisplayName,
  Posts.AcceptedAnswerId,
  Posts.CreationDate,
  Posts.ClosedDate
 
From Posts
INNER JOIN PostTags
  ON Posts.Id = PostTags.PostId
INNER JOIN TargetTags
  ON PostTags.TagId = TargetTags.Id

Please note that I am hardly a SQL expert, so there are –  I'm sure –  simpler and more efficient ways of achieving the same outcome.

Currently it returns just over 350 questions. The results look something link this:

 

(click to see the larger version)

Snap2

 

You can run the query yourself and download the raw CSV.

 

How it works

  • First, I need to find all the Tag Ids I care about. In this case those that are exactly “visio”.
  • Second, based on that Tag Id, find all the posts that have been tagged
  • NOTE: I had to use REPLACE() instead of LTRIM() because LTRIM() is not supported in the Data Explorer … though why that is the case is not clear to me

It's a pretty simple script, but served as a good learning exercise for me. I hope you find it useful in your own analysis.

Parting Thoughts

  • If you build software products that are programmable, service like the Data Explorer will help you get a better understanding or “telemetry data” regarding what developers are interested in or struggling with. You can take advantage of this information to provide better support or steer them in a productive direction.

 

 

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>