Over a million developers have joined DZone.

Using SQL to Calculate the Popularity (on Stack Overflow) of Derby, H2, and HSQLDB

As you may know, much of the Stack Exchange platform runs on SQL Server, and the team has made a lot of data publicly available through an SQL web API. So 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: Derby, H2, or HSQLDB?

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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:

se-data-explorer-schema

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:

se-data-explorer-derby-h2-hsqldb

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)

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
sql ,derby ,h2 ,hsqldb ,stack overflow ,stackexchange

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}