Rolling Fact Table With Dynamic Look-back Window

[colin]

Here’s a quick pattern for building a dynamic fact table with some parameterization. The use case would be trying to understand spend over a 7, 14, 28 day look-back window, and potentially even cohort against those users.

Here we’ll use Postgres SQL:

# Reference this view as user_rolling_sales
sql: |-
  SELECT 
      oi.user_id user_id,
      DATE_TRUNC('day', oi.created_at) as date,
      SUM(oi.sale_price) as day_sales,
      SUM(oi2.sale_price) as n_day_sales
  FROM order_items oi
  JOIN order_items oi2
      ON oi.user_id = oi2.user_id
      AND oi2.created_at::date BETWEEN 
          DATE_TRUNC('day', oi.created_at)  - make_interval(days => {{ filters.user_rolling_sales.rolling_window.value }}::integer) 
          AND DATE_TRUNC('day', oi.created_at)
  GROUP BY 1,2

dimensions:
  user_id: {}
  date: {}
  day_sales: {}
  n_day_sales: {}

measures:
  count:
    aggregate_type: count

  sum_n_day_sales:
    sql: ${n_day_sales}
    aggregate_type: sum

# this filter is dynamically injecting the join window for our rolling facts
# note we have a display label on the fields but trim 1 day for the date math
# we also set a default value to ensure the SQL parses without the filter
# filter_single_select_only will make it so users can only select from the list
filters:
  rolling_window:
    type: string
    suggestion_list:
      - value: "6"
        label: 7 days
      - value: "13"
        label: 14 days
    default_filter:
      is: "6"
    filter_single_select_only: true

The result is a fact table with every user_id, order_date. Note this will be sparse, but will reduce processing time. For use, you’d like want to use an inequality join and/or window function to ensure you can sum over all users properly.