cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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).
Rate this article:
Version history
Last update:
‎02-16-2024 10:12 AM
Updated by:
Contributors