Nat TaylorBlog, Product Management & Tinkering

Duplicative time periods in dbt

Published on . Updated on

Imagine you’re implementing a dbt model that requires columns of counts of things that occurred in a trailing 1, 7, 14, …n day window. Worse yet you have to do this for 2 classes of things. You will need to implement basically the same SQL over 14 lines. Of course if you need to tweak the pattern, you have to edit in 14 places.

Jinja’s for-loops (docs) can save you. Below is an example of how to implement.

{% set periods = [1, 7, 14, 30, 45, 60, 90, 365] %}
  MIN(datetime_created) AS first_thing,
  MAX(datetime_created) AS last_thing,
  CAST(count(*)/count(distinct date_trunc('month', datetime_created)) AS INT) avg_monthly_things,
  {% for n in periods %}
    COUNT(iff(DATEDIFF(days, owner_datetime_created, datetime_created)<={{n}}, 1, null)) things_first_{{n}}_days,
  {% endfor %}
  {% for n in periods %}
    COUNT(iff(DATEDIFF(days, thing.datetime_created, current_timestamp)<={{n}}, 1, null)) things_last_{{n}}_days,
  {% endfor %}
  COUNT(*) AS things
FROM thing


Popular Posts

Post Navigation