Few people know about this very, very awesome feature of the Stack Exchange platform, The Stack Exchange Data Explorer.

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 made a lot of data publicly available through an SQL web API. Here’s the schema that you can query:

Using a Running Total to Calculate Cumulative Daily Questions Per Tag

The number of analytics possibilities with such a public schema are infinite. Today, we’ll look into a question that has interested a lot of users in the past: What is the most "popular" Java in-memory database out of 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:
http://data.stackexchange.com/stackoverflow/query/469392/java-in-memory-database-popularity-by-time

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 gaining momentum while HSQLDB is on a slight decline.

Obviously, this "popularity" is not representative of a true market share. More questions might just mean that people struggle more with the technology, or less skilled people are using it.

(all Stack Exchange Subscriber Content is licensed under the terms of the CC BY-SA 3.0 license. For more details, see http://stackexchange.com/legal)