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