Nat Taylor — Blog, AI, Product Management & Tinkering

Duplicative time periods in dbt

Published on May 27, 2023.

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] %}
SELECT
  id,
  <strong>MIN</strong>(datetime_created) AS first_thing,
  <strong>MAX</strong>(datetime_created) AS last_thing,
  CAST(<strong>count</strong>(*)/<strong>count</strong>(distinct date_trunc('month', datetime_created)) AS INT) avg_monthly_things,
  {% for n in periods %}
    <strong>COUNT</strong>(iff(DATEDIFF(days, owner_datetime_created, datetime_created)<={{n}}, 1, null)) things_first_{{n}}_days,
  {% endfor %}
  {% for n in periods %}
    <strong>COUNT</strong>(iff(DATEDIFF(days, thing.datetime_created, current_timestamp)<={{n}}, 1, null)) things_last_{{n}}_days,
  {% endfor %}
  <strong>COUNT</strong>(*) AS things
FROM thing
GROUP BY id

asd