Rolling Averages Using Window Functions

[intapiuser]

Want to show a rolling average for your data, but not the granular breakdown? We can use the average window function to run this calculation.

First, I created a CTE that contains the number of new users per day on a fictional gaming platform. If you want to read more on CTEs, check out our post here!

with
  new_users as (
    select
      [created_at:date] as created_date
      , count(*) as number_users
    from
      users
    group by
      1
    order by
      1
  )

Next, we run this window function to get a rolling average from the preceding 9 rows to the current row. To read more on window function, check out this post here!

avg(number_users) over(order by created_date rows 9 preceding) as rolling_avg

Putting everything together: 

with
  new_users as (
    select
      [created_at:date] as created_date
      , count(*) as number_users
    from
      users
    group by
      1
    order by
      1
  )
select
  created_date
  , number_users
  , avg(number_users) over(order by created_date rows 9 preceding) as rolling_avg
from
  new_users

Now, we can plot only the rolling average line, as shown here:

Tip: If you want to display both the raw data and the rolling average, Periscope's built-in visualizations has a quick "Show Rolling Average" check box that you can toggle on. 

If your version of SQL doesn't support window functions, then you can use Sisense for Cloud Data Teams' Python/R integration to run the same calculation in R (as shown here), or using Python (shown here).