Gameplays by platform over time

When visualizing gameplays by platform over time — a chart every multiplatform game company knows well — it’s typical to start with a simple query:

select date(created_at), platform, count(1)
from gameplays
group by 1, 2

This produces a very fine graph:

Gameplays

While this graph is great at showing the whole pie, it can be difficult to tell how the platforms are performing relative to each other.

Daily percentages

To see the share of gameplays that happened on each platform each day, we’ll want to rewrite the query to get a percentage each day, rather than an absolute count.

On Postgres and Redshift databases, it’s quick and easy to do this with a window function:

select date(created_at),
platform,
count(1)::float / (sum(count(1)) over (partition by date))
from gameplays
group by 1, 2

The number of gameplays for each date and platform is divided by the total number of gameplays on that date. The result is a percentage-of-gameplays breakdown for each day:

Gameplays 2

How it works

The key to the query is the denominator, shown here:

(sum(count(1)) over (partition by date))

This line sums the gameplay counts for each date, even though the query groups by both date and platform. To illustrate, let’s break it out and put it on its own line:

select date(created_at),
platform,
count(1)::float,
(sum(count(1)) over (partition by date))
from gameplays
group by 1, 2

The results are a table with a count for every row — every (date, platform) pair — and a sum for every date. By dividing them, we get a percentage for every platform on every date. Here’s a sample of the data for the first couple of days:

dateplatformcountsum
2014-03-10android 80781273351
2014-03-10iOS 149751273351
2014-03-10web42819273351
2014-03-09android91380 298649
2014-03-09iOS 158909 298649
2014-03-09web48360298649

An easier way

If you’re using Sisense for Cloud Data Teams, just click “Proportional Bars” in the settings menu to turn totals into percentages with a single click:

Proportional bars