Few people know about this very very awesome feature of the Stack Exchange platform. The Stack Exchange Data Explorer
To be found here:
http://data.stackexchange.com
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