Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Use window functions for time-series percentages

Use window functions for time-series percentages
May 8, 2014

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, seen here in Periscope:

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:

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

Here are the results in table form:

   date    | platform | count  |  sum 
-----------+----------+--------+-------- 
2014-03-10 | android  |  80781 | 273351
2014-03-10 | iOS      | 149751 | 273351
2014-03-10 | web      |  42819 | 273351
2014-03-09 | android  |  91380 | 298649
2014-03-09 | iOS      | 158909 | 298649
2014-03-09 | web      |  48360 | 298649
2014-03-08 | android  |  95363 | 253479
2014-03-08 | iOS      | 108206 | 253479
2014-03-08 | web      |  49910 | 253479
2014-03-07 | android  |  92316 | 249092
2014-03-07 | iOS      | 105756 | 249092
2014-03-07 | web      |  51020 | 249092
2014-03-06 | android  |  74686 | 203062
2014-03-06 | iOS      |  86732 | 203062
2014-03-06 | web      |  41644 | 203062
2014-03-05 | android  |  75386 | 204645
2014-03-05 | iOS      |  88011 | 204645
2014-03-05 | web      |  41248 | 204645
2014-03-04 | android  |  79840 | 214582
2014-03-04 | iOS      |  92207 | 214582
2014-03-04 | web      |  42535 | 214582
2014-03-03 | android  |  99762 | 236902
2014-03-03 | iOS      |  93637 | 236902
2014-03-03 | web      |  43503 | 236902
2014-03-02 | android  | 140850 | 301219
2014-03-02 | iOS      | 109203 | 301219
2014-03-02 | web      |  51166 | 301219
2014-03-01 | android  | 146112 | 290206
2014-03-01 | iOS      |  92163 | 290206
2014-03-01 | web      |  51931 | 290206
2014-02-28 | android  | 138266 | 292095
2014-02-28 | iOS      |  88670 | 292095
2014-02-28 | web      |  65159 | 292095
2014-02-27 | android  | 122277 | 253123
2014-02-27 | iOS      |  74058 | 253123
2014-02-27 | web      |  56788 | 253123
2014-02-26 | android  | 122059 | 253169
2014-02-26 | iOS      |  74322 | 253169
2014-02-26 | web      |  56788 | 253169
2014-02-25 | android  | 125679 | 260141
2014-02-25 | iOS      |  76975 | 260141
2014-02-25 | web      |  57487 | 260141
2014-02-24 | android  | 126005 | 263540
2014-02-24 | iOS      |  72569 | 263540
2014-02-24 | web      |  64966 | 263540
2014-02-23 | android  | 172075 | 416859
2014-02-23 | iOS      | 147799 | 416859
2014-02-23 | web      |  96985 | 416859
2014-02-22 | android  | 149358 | 372507
2014-02-22 | iOS      | 140794 | 372507
2014-02-22 | web      |  82355 | 372507
2014-02-21 | android  | 141200 | 286851
2014-02-21 | iOS      |  78908 | 286851
2014-02-21 | web      |  66743 | 286851
2014-02-20 | android  | 129598 | 259301
2014-02-20 | iOS      |  68517 | 259301
2014-02-20 | web      |  61186 | 259301
2014-02-19 | android  | 123893 | 239528
2014-02-19 | iOS      |  70034 | 239528
2014-02-19 | web      |  45601 | 239528
2014-02-18 | android  |  73724 | 246865
2014-02-18 | iOS      | 118211 | 246865
2014-02-18 | web      |  54930 | 246865
2014-02-17 | android  |  70357 | 219855
2014-02-17 | iOS      | 112051 | 219855
2014-02-17 | web      |  37447 | 219855
2014-02-16 | android  |  85680 | 283088
2014-02-16 | iOS      | 148630 | 283088
2014-02-16 | web      |  48778 | 283088
2014-02-15 | android  |  85691 | 280249
2014-02-15 | iOS      | 147373 | 280249
2014-02-15 | web      |  47185 | 280249
2014-02-14 | android  |  87227 | 282971
2014-02-14 | iOS      | 146470 | 282971
2014-02-14 | web      |  49274 | 282971
2014-02-13 | android  |  70045 | 223062
2014-02-13 | iOS      | 114409 | 223062
2014-02-13 | web      |  38608 | 223062
2014-02-12 | android  |  76612 | 329726
2014-02-12 | iOS      | 191126 | 329726
2014-02-12 | web      |  61988 | 329726
2014-02-11 | android  |  79316 | 231229
2014-02-11 | iOS      |  99215 | 231229
2014-02-11 | web      |  52698 | 231229
2014-02-10 | android  |  81752 | 218674
2014-02-10 | iOS      |  81910 | 218674
2014-02-10 | web      |  55012 | 218674
2014-02-09 | android  | 116451 | 266473
2014-02-09 | iOS      | 106347 | 266473
2014-02-09 | web      |  43675 | 266473
2014-02-08 | android  | 118315 | 289808
2014-02-08 | iOS      | 110231 | 289808
2014-02-08 | web      |  61262 | 289808
2014-02-07 | android  | 122650 | 296501
2014-02-07 | iOS      | 109496 | 296501
2014-02-07 | web      |  64355 | 296501
2014-02-06 | android  | 123741 | 263287
2014-02-06 | iOS      |  94772 | 263287
2014-02-06 | web      |  44774 | 263287
2014-02-05 | android  | 111329 | 255835
2014-02-05 | iOS      |  91229 | 255835
2014-02-05 | web      |  53277 | 255835
2014-02-04 | android  | 107172 | 261922
2014-02-04 | iOS      | 105932 | 261922
2014-02-04 | web      |  48818 | 261922
2014-02-03 | android  |  64341 | 154916
2014-02-03 | iOS      |  57756 | 154916
2014-02-03 | web      |  32819 | 154916
2014-02-02 | android  |  77633 | 191676
2014-02-02 | iOS      |  73370 | 191676
2014-02-02 | web      |  40673 | 191676
2014-02-01 | android  |  71773 | 175624
2014-02-01 | iOS      |  66615 | 175624
2014-02-01 | web      |  37236 | 175624
2014-01-31 | android  |  77058 | 186597
2014-01-31 | iOS      |  69018 | 186597
2014-01-31 | web      |  40521 | 186597
2014-01-30 | android  |  56241 | 145235
2014-01-30 | iOS      |  56060 | 145235
2014-01-30 | web      |  32934 | 145235
2014-01-29 | android  |  49483 | 136663
2014-01-29 | iOS      |  55268 | 136663
2014-01-29 | web      |  31912 | 136663
2014-01-28 | android  |  52564 | 143122
2014-01-28 | iOS      |  57812 | 143122
2014-01-28 | web      |  32746 | 143122
2014-01-27 | android  |  53792 | 144636
2014-01-27 | iOS      |  57275 | 144636
2014-01-27 | web      |  33569 | 144636
2014-01-26 | android  |  57265 | 205445
2014-01-26 | iOS      | 108704 | 205445
2014-01-26 | web      |  39476 | 205445
2014-01-25 | android  |  53710 | 196041
2014-01-25 | iOS      | 105010 | 196041
2014-01-25 | web      |  37321 | 196041
2014-01-24 | android  |  50915 | 186155
2014-01-24 | iOS      |  99319 | 186155
2014-01-24 | web      |  35921 | 186155
2014-01-23 | android  |  51041 | 169967
2014-01-23 | iOS      |  87476 | 169967
2014-01-23 | web      |  31450 | 169967
2014-01-22 | android  |  51185 | 172089
2014-01-22 | iOS      |  87838 | 172089
2014-01-22 | web      |  33066 | 172089
2014-01-21 | android  |  47816 | 164466
2014-01-21 | iOS      |  87029 | 164466
2014-01-21 | web      |  29621 | 164466
2014-01-20 | android  |  54837 | 182314
2014-01-20 | iOS      |  94517 | 182314
2014-01-20 | web      |  32960 | 182314
2014-01-19 | android  |  54216 | 190656
2014-01-19 | iOS      |  99618 | 190656
2014-01-19 | web      |  36822 | 190656
2014-01-18 | android  |  53819 | 211127
2014-01-18 | iOS      | 102127 | 211127
2014-01-18 | web      |  55181 | 211127
2014-01-17 | android  |  82078 | 303621
2014-01-17 | iOS      | 178942 | 303621
2014-01-17 | web      |  42601 | 303621
2014-01-16 | android  |  87465 | 284043
2014-01-16 | iOS      | 163077 | 284043
2014-01-16 | web      |  33501 | 284043
2014-01-15 | android  |  51186 | 215547
2014-01-15 | iOS      | 134435 | 215547
2014-01-15 | web      |  29926 | 215547
2014-01-14 | android  |  64062 | 244624
2014-01-14 | iOS      | 156699 | 244624
2014-01-14 | web      |  23863 | 244624
2014-01-13 | android  |  64924 | 165842
2014-01-13 | iOS      |  74752 | 165842
2014-01-13 | web      |  26166 | 165842
2014-01-12 | android  |  76502 | 186517
2014-01-12 | iOS      |  79783 | 186517
2014-01-12 | web      |  30232 | 186517
2014-01-11 | android  |  71813 | 175001
2014-01-11 | iOS      |  74779 | 175001
2014-01-11 | web      |  28409 | 175001
2014-01-10 | android  |  72715 | 181186
2014-01-10 | iOS      |  78923 | 181186
2014-01-10 | web      |  29548 | 181186
2014-01-09 | android  |  64052 | 156383
2014-01-09 | iOS      |  58777 | 156383
2014-01-09 | web      |  33554 | 156383
2014-01-08 | android  |  64798 | 157293
2014-01-08 | iOS      |  57609 | 157293
2014-01-08 | web      |  34886 | 157293
2014-01-07 | android  |  64322 | 155614
2014-01-07 | iOS      |  58418 | 155614
2014-01-07 | web      |  32874 | 155614
2014-01-06 | android  |  63473 | 155941
2014-01-06 | iOS      |  58412 | 155941
2014-01-06 | web      |  34056 | 155941
2014-01-05 | android  |  52542 | 160094
2014-01-05 | iOS      |  67933 | 160094
2014-01-05 | web      |  39619 | 160094
2014-01-04 | android  |  52493 | 143286
2014-01-04 | iOS      |  52012 | 143286
2014-01-04 | web      |  38781 | 143286
2014-01-03 | android  |  66541 | 167745
2014-01-03 | iOS      |  59088 | 167745
2014-01-03 | web      |  42116 | 167745
2014-01-02 | android  |  52507 | 128684
2014-01-02 | iOS      |  41799 | 128684
2014-01-02 | web      |  34378 | 128684
2014-01-01 | android  |  57267 | 146928
2014-01-01 | iOS      |  42101 | 146928
2014-01-01 | web      |  47560 | 146928
2013-12-31 | android  |  53190 | 169494
2013-12-31 | iOS      |  58772 | 169494
2013-12-31 | web      |  57532 | 169494
2013-12-30 | android  |  71567 | 266118
2013-12-30 | iOS      | 129716 | 266118
2013-12-30 | web      |  64835 | 266118
2013-12-29 | android  | 111095 | 308486
2013-12-29 | iOS      | 120088 | 308486
2013-12-29 | web      |  77303 | 308486
2013-12-28 | android  | 108993 | 249632
2013-12-28 | iOS      |  77440 | 249632
2013-12-28 | web      |  63199 | 249632
2013-12-27 | android  |  81306 | 233276
2013-12-27 | iOS      |  69086 | 233276
2013-12-27 | web      |  82884 | 233276
2013-12-26 | android  |  67273 | 193082
2013-12-26 | iOS      |  78080 | 193082
2013-12-26 | web      |  47729 | 193082
2013-12-25 | android  |  82828 | 200595
2013-12-25 | iOS      |  87415 | 200595
2013-12-25 | web      |  30352 | 200595
2013-12-24 | android  |  42563 |  98963
2013-12-24 | iOS      |  27135 |  98963
2013-12-24 | web      |  29265 |  98963
2013-12-23 | android  |  47027 | 124899
2013-12-23 | iOS      |  47542 | 124899
2013-12-23 | web      |  30330 | 124899
2013-12-22 | android  |  48989 | 140565
2013-12-22 | iOS      |  57179 | 140565
2013-12-22 | web      |  34397 | 140565
2013-12-21 | android  |  46158 | 157330
2013-12-21 | iOS      |  60107 | 157330
2013-12-21 | web      |  51065 | 157330
2013-12-20 | android  |  76636 | 263155
2013-12-20 | iOS      | 132296 | 263155
2013-12-20 | web      |  54223 | 263155
2013-12-19 | android  |  61120 | 182428
2013-12-19 | iOS      |  91466 | 182428
2013-12-19 | web      |  29842 | 182428
2013-12-18 | android  |  64208 | 179792
2013-12-18 | iOS      |  85215 | 179792
2013-12-18 | web      |  30369 | 179792
2013-12-17 | android  |  36518 |  95015
2013-12-17 | iOS      |  36170 |  95015
2013-12-17 | web      |  22327 |  95015
2013-12-16 | android  |  40376 |  99689
2013-12-16 | iOS      |  33725 |  99689
2013-12-16 | web      |  25588 |  99689
2013-12-15 | android  |  53819 | 128184
2013-12-15 | iOS      |  44927 | 128184
2013-12-15 | web      |  29438 | 128184
2013-12-14 | android  |  58316 | 130806
2013-12-14 | iOS      |  43101 | 130806
2013-12-14 | web      |  29389 | 130806
2013-12-13 | android  |  83343 | 216971
2013-12-13 | iOS      |  89920 | 216971
2013-12-13 | web      |  43708 | 216971
2013-12-12 | android  |  73343 | 222891
2013-12-12 | iOS      | 109337 | 222891
2013-12-12 | web      |  40211 | 222891
2013-12-11 | android  |  75764 | 202994
2013-12-11 | iOS      |  92798 | 202994
2013-12-11 | web      |  34432 | 202994
2013-12-10 | android  |  70697 | 157840
2013-12-10 | iOS      |  46336 | 157840
2013-12-10 | web      |  40807 | 157840
2013-12-09 | android  |  78569 | 114220
2013-12-09 | web      |  35651 | 114220
2013-12-08 | android  |  89270 | 116903
2013-12-08 | web      |  27633 | 116903
2013-12-07 | android  |  40153 |  69119
2013-12-07 | web      |  28966 |  69119
2013-12-06 | android  |  37529 |  64032
2013-12-06 | web      |  26503 |  64032
2013-12-05 | android  |  32615 |  54588
2013-12-05 | web      |  21973 |  54588
2013-12-04 | android  |  36957 |  61313
2013-12-04 | web      |  24356 |  61313
2013-12-03 | android  |  30908 |  53347
2013-12-03 | web      |  22439 |  53347
2013-12-02 | android  |  31560 |  54028
2013-12-02 | web      |  22468 |  54028
2013-12-01 | android  |  26875 |  52317
2013-12-01 | web      |  25442 |  52317
2013-11-30 | android  |  28224 |  55810
2013-11-30 | web      |  27586 |  55810
2013-11-29 | android  |  24316 |  50610
2013-11-29 | web      |  26294 |  50610
2013-11-28 | android  |  44822 |  78673
2013-11-28 | web      |  33851 |  78673
2013-11-27 | android  |  28695 |  60725
2013-11-27 | web      |  32030 |  60725
2013-11-26 | android  |  30417 |  58532
2013-11-26 | web      |  28115 |  58532
2013-11-25 | android  |  27803 |  59730
2013-11-25 | web      |  31927 |  59730
2013-11-24 | android  |  37538 |  75350
2013-11-24 | web      |  37812 |  75350
2013-11-23 | android  |  39738 |  62783
2013-11-23 | web      |  23045 |  62783
2013-11-22 | android  |  20994 |  44303
2013-11-22 | web      |  23309 |  44303
2013-11-21 | android  |  15605 |  35931
2013-11-21 | web      |  20326 |  35931
2013-11-20 | android  |  16521 |  44792
2013-11-20 | web      |  28271 |  44792
2013-11-19 | android  |  16136 |  44599
2013-11-19 | web      |  28463 |  44599
2013-11-18 | android  |  18935 |  46375
2013-11-18 | web      |  27440 |  46375
2013-11-17 | android  |  24264 |  58754
2013-11-17 | web      |  34490 |  58754
2013-11-16 | android  |  21588 |  53201
2013-11-16 | web      |  31613 |  53201
2013-11-15 | android  |  21053 |  55471
2013-11-15 | web      |  34418 |  55471
2013-11-14 | android  |  20003 |  68294
2013-11-14 | web      |  48291 |  68294
2013-11-13 | android  |  32041 |  70482
2013-11-13 | web      |  38441 |  70482
2013-11-12 | android  |  42280 |  85386
2013-11-12 | web      |  43106 |  85386
2013-11-11 | android  |  35900 |  85239
2013-11-11 | web      |  49339 |  85239
2013-11-10 | android  |  53245 |  95805
2013-11-10 | web      |  42560 |  95805
2013-11-09 | android  |  34322 |  60661
2013-11-09 | web      |  26339 |  60661
2013-11-08 | android  |  11352 |  31853
2013-11-08 | web      |  20501 |  31853
2013-11-07 | android  |  18796 |  36847
2013-11-07 | web      |  18051 |  36847
2013-11-06 | android  |  15849 |  33285
2013-11-06 | web      |  17436 |  33285
2013-11-05 | android  |  17277 |  35675
2013-11-05 | web      |  18398 |  35675
2013-11-04 | android  |  17817 |  38140
2013-11-04 | web      |  20323 |  38140
2013-11-03 | android  |  18331 |  40885
2013-11-03 | web      |  22554 |  40885
2013-11-02 | android  |  15165 |  39180
2013-11-02 | web      |  24015 |  39180
2013-11-01 | android  |  12878 |  35285
2013-11-01 | web      |  22407 |  35285
2013-10-31 | android  |   9971 |  29616
2013-10-31 | web      |  19645 |  29616
2013-10-30 | android  |  11553 |  31166
2013-10-30 | web      |  19613 |  31166
2013-10-29 | android  |  10605 |  29848
2013-10-29 | web      |  19243 |  29848
2013-10-28 | android  |   8500 |  26749
2013-10-28 | web      |  18249 |  26749
2013-10-27 | android  |   9378 |  30140
2013-10-27 | web      |  20762 |  30140
2013-10-26 | android  |   8840 |  29885
2013-10-26 | web      |  21045 |  29885
2013-10-25 | web      |  21872 |  21872
2013-10-24 | web      |  18772 |  18772
2013-10-23 | web      |  18870 |  18870
2013-10-22 | web      |  18746 |  18746
2013-10-21 | web      |  16685 |  16685
2013-10-20 | web      |  19870 |  19870
2013-10-19 | web      |  20335 |  20335
2013-10-18 | web      |  20344 |  20344
2013-10-17 | web      |  17246 |  17246
2013-10-16 | web      |  18780 |  18780
2013-10-15 | web      |  17613 |  17613
2013-10-14 | web      |  16827 |  16827
2013-10-13 | web      |  20390 |  20390
2013-10-12 | web      |  19539 |  19539
2013-10-11 | web      |  17190 |  17190
2013-10-10 | web      |  14006 |  14006
2013-10-09 | web      |  15264 |  15264
2013-10-08 | web      |  15187 |  15187
2013-10-07 | web      |  14937 |  14937
2013-10-06 | web      |  17741 |  17741
2013-10-05 | web      |  26797 |  26797
2013-10-04 | web      |  35199 |  35199
2013-10-03 | web      |  22551 |  22551
2013-10-02 | web      |  21066 |  21066
2013-10-01 | web      |  24086 |  24086
2013-09-30 | web      |  20505 |  20505
2013-09-29 | web      |  33677 |  33677
2013-09-28 | web      |  13169 |  13169
2013-09-27 | web      |  12909 |  12909
2013-09-26 | web      |  14370 |  14370
2013-09-25 | web      |  13193 |  13193
2013-09-24 | web      |  13467 |  13467
2013-09-23 | web      |  11886 |  11886
2013-09-22 | web      |  17361 |  17361
2013-09-21 | web      |  23638 |  23638
2013-09-20 | web      |  16594 |  16594
2013-09-19 | web      |  14645 |  14645
2013-09-18 | web      |  12975 |  12975
2013-09-17 | web      |   8101 |   8101
2013-09-16 | web      |   9492 |   9492
2013-09-15 | web      |   8669 |   8669
2013-09-14 | web      |   3006 |   3006
2013-09-13 | web      |   1924 |   1924
2013-09-12 | web      |   1607 |   1607
2013-09-11 | web      |   2253 |   2253
(408 rows)

As you can see, there’s 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.

An easier way

If you’re a Periscope user, just click “Proportional Bars” in settings to turn totals into percentages with a single button click:

Sign up for Periscope to try this out on your own data!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?