Take a look at the StackExchange Data Explorer, a way to browse Stack Overflow data, and the data on other networks, in any shape or form.
StackExchange has something called StackExchange Data Explorer, it gives you control to search data using SQL instead of a silly search bar.
The tool can retrieve the results in text or a pretty format, and you're able to download the results in a CSV (that you can convert to any data format without trouble these days).
What is the StackExchange Data Explorer?
The StackExchange Data Explorer gives you moderate access to the StackExchange database and lets you select data from it. There are certain restrictions, for the good, as nobody would want sensitive data exposed there.
Anyone can use the StackExchange Data Explorer, and you don't even need an account on the website, being able to create your queries as an anonymous user.
If you use the explorer as an anonymous user, you will face captchas here and then, but the features are the same.
How to query the data explorer
It only takes a few steps to start experimenting with the data explorer.
- Access the StackExchange Data Explorer
- From the list of sites on the network, pick the one you want to get data for, the default is Stack Overflow
- Click on the Compose Query button at top-right
- Fill in the title and the query, for the query, use SQL
- Press the Run Query button at the bottom to run your query and get the results
On the right side of the query composer, you have the Database Scheme available, you can take a look at the tables and columns for the database, which is useful when creating your query.
Example of Stack Overflow queries
Let's take a look at some queries you're able to do to get data from Stack Overflow.
Querying data from Users
select * from Users where DisplayName like '%pewdiepie%'
This simple query will show all users with pewdiepie somewhere on their display name. As you're able to see, the query can be as flexible as you want, so even the LIKE operator is possible.
You can check that query here: pewdiepie query.
select * from Users where WebsiteUrl like '%plaguer%'
This will show all users with the WebsiteUrl set to something with Plaguer on it.
You can check here: website query.
Querying data from Posts
select top 50 * from Posts where AnswerCount > 50 order by AnswerCount desc
This query will find the 50 posts with more answers and order them by the answer count.
You can check the query here: most answers query.
How the queries are made
Stack Overflow/Stack Exchange uses Microsoft SQL Server, so you will be using MSSQL on the querying tool.
If you need to refresh your mind on SQL, take a look at the Microsoft Docs for Select that applies for MSSQL.
You won't be inserting, deleting tables, or updating, for obvious reasons! But the Data Explorer is a great way to sharpen your select skills as you can try all kinds of shenanigans with the Stack Exchange database.
You can also browse queries made by other users on the queries page.
Keep in mind that any user and even anonymous users can save queries there, it's a great place to see how creative people get with the tool, but they may not be following any kind of best practice.
If you're new to SQL and think that the tool is great for practice, you can start with simple queries as listed in the previous section, using the asterisk you're able to return all values from a row and use it as you wish.
Take care of the data explorer
The Stack Exchange Data Explorer is an awesome tool available for free, that you can use to try all sorts of queries, get CSV data of information you want, and discover all kinds of curiosities.
I was quite surprised when I discovered this tool, as it is direct access to the database, although it has the needed limits, so I am not aware how it is not more popular.
But I would take care to not break the website with it, with great power comes great responsibility.
You can check all examples made for this article on Github.