Few people know about this very very awesome feature of the Stack Exchange platform. The Stack Exchange Data Explorer
To be found here:
As you may know, much of the Stack Exchange platform runs on SQL Server (interesting architecture details here: http://stackexchange.com/performance), and the team has had the courtesy of making a lot of data publicly available through a SQL web API. Here’s the schmema that you can query:
Using a running total to calculate cumulative daily questions per tag
The amount of analytics possibilities with such a public schema are infinite. Today, we’ll look into a question that has been interesting for a lot of users in the past: What is the most “popular” Java in-memory database among Derby (also known as Java DB, which ships with the JDK), the popular test database H2 (see also our interview with Thomas Müller, its creator), or HSQLDB.
What we’d like to do is sum up the number of questions per database, up to any given date. This should give us one of those nice exponential curves that managers like so much.
Here’s the SQL query that we’ll run:
SELECT d, SUM(h2) OVER (ORDER BY d) AS h2, SUM(hsqldb) OVER (ORDER BY d) AS hsqldb, SUM(derby) OVER (ORDER BY d) AS derby FROM ( SELECT CAST(CreationDate AS DATE) AS d, COUNT(CASE WHEN Tags LIKE '%<h2>%' THEN 1 END) AS h2, COUNT(CASE WHEN Tags LIKE '%<hsqldb>%' THEN 1 END) AS hsqldb, COUNT(CASE WHEN Tags LIKE '%<derby>%' THEN 1 END) AS derby FROM Posts GROUP BY CAST(CreationDate AS DATE) ) AS DailyPosts ORDER BY d ASC
A short explanation:
The nested select
"DailyPosts" creates a PIVOT table with the aggregated number of questions per database and date. We could have used the SQL Server PIVOT clause, if the Stack Exchange platform had stored tagging information in a normalised form, but the equivalent
COUNT(CASE) expressions work just as nicely (see also our article about PostgreSQL’s aggregation
FILTER clause for more inspiration).
Now, that we have the number of posts per tag and day, all that’s left to do is sum up those numbers from the first day to any given day. That is often also called a “running total”, which can be calculated very easily using the
SUM() OVER() window function.
Now we’re done. You can run and play around with this query here:
The raw result is not very interesting. It’s a lot of numbers and dates. But if we plot that result in a graph / chart, we’re getting this nice-looking curve here:
As we can see, all three databases are roughly equivalent in terms of “popularity”, although H2 seems to be catching up momentum while HSQLDB is on a slight decline.
(Obviously, this “popularity” is not representative of true market share. More questions might just mean that people struggle more with the technology, or – less skilled people are using it).
Have fun further exploring the Stack Exchange Data Explorer:
Further articles that are interesting in the context of the displayed query: