dbt Saas legislation

User Facts

User Facts model for saas legislation.

inferred

Blocks

dbt SQL LKML LookML

user_facts.sql · 79 lines

inferred

-- models/user_facts.sql
{{ config(materialized='view') }}

select first.id, first.l28 as first_l28, second.l28 as second_l28, third.l28 as third_l28,
  fourth.l28 as fourth_l28, fifth.l28 as fifth_l28, sixth.l28 as sixth_l28, seventh.l28 as seventh_l28 from

(
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as first

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as second on first.id=second.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as third on first.id=third.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fourth on first.id=fourth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fifth on first.id=fifth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as sixth on first.id=sixth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as seventh on first.id=seventh.id

# models/schema.yml
version: 2

models:
  - name: user_facts
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: 1st_l28
      - name: 2nd_l28
      - name: 3rd_l28
      - name: 4th_l28
      - name: 5th_l28
      - name: 6th_l28
      - name: 7th_l28

Assumptions

materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

user_facts.sql · 58 lines

source-preserved validation: parses

select first.id, first.l28 as first_l28, second.l28 as second_l28, third.l28 as third_l28,
  fourth.l28 as fourth_l28, fifth.l28 as fifth_l28, sixth.l28 as sixth_l28, seventh.l28 as seventh_l28 from

(
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as first

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as second on first.id=second.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as third on first.id=third.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fourth on first.id=fourth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fifth on first.id=fifth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as sixth on first.id=sixth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as seventh on first.id=seventh.id

user_facts.view.lkml · 106 lines

inferred

view: user_facts {
  derived_table: {
    sql: select first.id, first.l28 as first_l28, second.l28 as second_l28, third.l28 as third_l28,
  fourth.l28 as fourth_l28, fifth.l28 as fifth_l28, sixth.l28 as sixth_l28, seventh.l28 as seventh_l28 from

(
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as first

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as second on first.id=second.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as third on first.id=third.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fourth on first.id=fourth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fifth on first.id=fifth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as sixth on first.id=sixth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as seventh on first.id=seventh.id ;;
  }

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: 1st_l28 {
    type: int
    sql: ${TABLE}.first_l28 ;;
  }

  dimension: 2nd_l28 {
    type: int
    sql: ${TABLE}.second_l28 ;;
  }

  dimension: 3rd_l28 {
    type: int
    sql: ${TABLE}.third_l28 ;;
  }

  dimension: 4th_l28 {
    type: int
    sql: ${TABLE}.fourth_l28 ;;
  }

  dimension: 5th_l28 {
    type: int
    sql: ${TABLE}.sixth_l28 ;;
  }

  dimension: 6th_l28 {
    type: int
    sql: ${TABLE}.sixth_l28 ;;
  }

  dimension: 7th_l28 {
    type: int
    sql: ${TABLE}.seventh_l28 ;;
  }

  measure: count {
    type: count
  }
}

user_facts.view.lookml · 87 lines

source-preserved

- view: user_facts
  derived_table:
    sql: select first.id, first.l28 as first_l28, second.l28 as second_l28, third.l28 as third_l28,
  fourth.l28 as fourth_l28, fifth.l28 as fifth_l28, sixth.l28 as sixth_l28, seventh.l28 as seventh_l28 from

(
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-27, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as first

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-55, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as second on first.id=second.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-83, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as third on first.id=third.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-111, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fourth on first.id=fourth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-139, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as fifth on first.id=fifth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-167, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as sixth on first.id=sixth.id

left join (
  select distinct users.id, count(distinct(DATE_TRUNC('day', tracks.sent_at))) as l28
  from ember.users as users
  left join ember.tracks as tracks on tracks.user_id = users.id
  where (((tracks.sent_at) >= (DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) )) AND (tracks.sent_at) < (DATEADD(day,28, DATEADD(day,-195, DATE_TRUNC('day',GETDATE()) ) ))))
  group by 1
) as seventh on first.id=seventh.id
  fields:
  - dimension: id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: 1st_l28
    type: int
    sql: ${TABLE}.first_l28
  - dimension: 2nd_l28
    type: int
    sql: ${TABLE}.second_l28
  - dimension: 3rd_l28
    type: int
    sql: ${TABLE}.third_l28
  - dimension: 4th_l28
    type: int
    sql: ${TABLE}.fourth_l28
  - dimension: 5th_l28
    type: int
    sql: ${TABLE}.sixth_l28
  - dimension: 6th_l28
    type: int
    sql: ${TABLE}.sixth_l28
  - dimension: 7th_l28
    type: int
    sql: ${TABLE}.seventh_l28
  - measure: count
    type: count

user_facts.sql
user_facts.sql
user_facts.view.lkml
user_facts.view.lookml

User Facts

Blocks

Assumptions

Contents