dbt Electronics kids

Subsequent Order Facts

Subsequent Order Facts model for electronics kids.

inferred

Blocks

dbt SQL LKML LookML

subsequent_order_facts.sql · 40 lines

inferred

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

SELECT
   so.id AS order_id
  ,so.user_id AS user_id
  ,so.created_at AS created_at
  ,SUM(CASE WHEN so.created_at < subsequent_so.created_at THEN 1 ELSE 0 END) AS subsequent_entries_number
  ,SUM(CASE WHEN so.created_at > subsequent_so.created_at THEN 1 ELSE 0 END) AS order_sequence_number
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 90 DAY) THEN 1 ELSE 0 END) subsequent_entries_90
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 180 DAY) THEN 1 ELSE 0 END) subsequent_entries_180
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 1 YEAR) THEN 1 ELSE 0 END) subsequent_entries_365
  ,MIN(CASE WHEN so.created_at < subsequent_so.created_at THEN subsequent_so.created_at ELSE NULL END) AS next_order_date

FROM spree_orders so

LEFT JOIN spree_orders subsequent_so
    on so.user_id = subsequent_so.user_id


WHERE DATE(so.locked_at) > 0 AND DATE(subsequent_so.locked_at) > 0
GROUP BY 1,2,3
ORDER BY so.id, so.created_at

# models/schema.yml
version: 2

models:
  - name: subsequent_order_facts
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: user_id
      - name: subsequent_entries_number
      - name: order_sequence_number
      - name: subsequent_entries_90
      - name: subsequent_entries_180
      - name: subsequent_entries_365

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.

subsequent_order_facts.sql · 20 lines

source-preserved validation: parses

SELECT
   so.id AS order_id
  ,so.user_id AS user_id
  ,so.created_at AS created_at
  ,SUM(CASE WHEN so.created_at < subsequent_so.created_at THEN 1 ELSE 0 END) AS subsequent_entries_number
  ,SUM(CASE WHEN so.created_at > subsequent_so.created_at THEN 1 ELSE 0 END) AS order_sequence_number
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 90 DAY) THEN 1 ELSE 0 END) subsequent_entries_90
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 180 DAY) THEN 1 ELSE 0 END) subsequent_entries_180
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 1 YEAR) THEN 1 ELSE 0 END) subsequent_entries_365
  ,MIN(CASE WHEN so.created_at < subsequent_so.created_at THEN subsequent_so.created_at ELSE NULL END) AS next_order_date

FROM spree_orders so

LEFT JOIN spree_orders subsequent_so
    on so.user_id = subsequent_so.user_id


WHERE DATE(so.locked_at) > 0 AND DATE(subsequent_so.locked_at) > 0
GROUP BY 1,2,3
ORDER BY so.id, so.created_at

subsequent_order_facts.view.lkml · 115 lines

inferred

view: subsequent_order_facts {
  derived_table: {
    sql: SELECT
   so.id AS order_id
  ,so.user_id AS user_id
  ,so.created_at AS created_at
  ,SUM(CASE WHEN so.created_at < subsequent_so.created_at THEN 1 ELSE 0 END) AS subsequent_entries_number
  ,SUM(CASE WHEN so.created_at > subsequent_so.created_at THEN 1 ELSE 0 END) AS order_sequence_number
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 90 DAY) THEN 1 ELSE 0 END) subsequent_entries_90
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 180 DAY) THEN 1 ELSE 0 END) subsequent_entries_180
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 1 YEAR) THEN 1 ELSE 0 END) subsequent_entries_365
  ,MIN(CASE WHEN so.created_at < subsequent_so.created_at THEN subsequent_so.created_at ELSE NULL END) AS next_order_date

FROM spree_orders so

LEFT JOIN spree_orders subsequent_so
    on so.user_id = subsequent_so.user_id


WHERE DATE(so.locked_at) > 0 AND DATE(subsequent_so.locked_at) > 0
GROUP BY 1,2,3
ORDER BY so.id, so.created_at ;;
  }

  dimension: order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: user_id {
    sql: ${TABLE}.user_id ;;
  }

  dimension: subsequent_entries_number {
    type: int
    sql: ${TABLE}.subsequent_entries_number ;;
  }

  dimension: order_sequence_number {
    type: int
    sql: ${TABLE}.order_sequence_number ;;
  }

  dimension: subsequent_entries_90 {
    sql: ${TABLE}.subsequent_entries_90 ;;
  }

  dimension: subsequent_entries_180 {
    sql: ${TABLE}.subsequent_entries_180 ;;
  }

  dimension: subsequent_entries_365 {
    sql: ${TABLE}.subsequent_entries_365 ;;
  }

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: count_users {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: count_orders_with_sub {
    type: count
    filters: [subsequent_entries_number: ">0"]
  }

  measure: count_orders_no_sub {
    type: count
    filters: [subsequent_entries_number: "0"]
  }

  measure: repeat_in_90_days {
    type: count_distinct
    filters: [subsequent_entries_90: "1"]
    sql: ${user_id} ;;
  }

  measure: repeat_in_180_days {
    type: count_distinct
    filters: [subsequent_entries_180: "1"]
    sql: ${user_id} ;;
  }

  measure: repeat_in_365_days {
    type: count_distinct
    filters: [subsequent_entries_365: "1"]
    sql: ${user_id} ;;
  }

  measure: repeat_rate_90 {
    type: number
    sql: 100.0*${repeat_in_90_days}/ NULLIF(${count_users},0) ;;
  }

  measure: repeat_rate_180 {
    type: number
    sql: 100.0*${repeat_in_180_days}/ NULLIF(${count_users},0) ;;
  }

  measure: repeat_rate_365 {
    type: number
    sql: 100.0*${repeat_in_365_days}/ NULLIF(${count_users},0) ;;
  }
}

subsequent_order_facts.view.lookml · 88 lines

source-preserved

- view: subsequent_order_facts
  derived_table:
    sql: SELECT
   so.id AS order_id
  ,so.user_id AS user_id
  ,so.created_at AS created_at
  ,SUM(CASE WHEN so.created_at < subsequent_so.created_at THEN 1 ELSE 0 END) AS subsequent_entries_number
  ,SUM(CASE WHEN so.created_at > subsequent_so.created_at THEN 1 ELSE 0 END) AS order_sequence_number
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 90 DAY) THEN 1 ELSE 0 END) subsequent_entries_90
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 180 DAY) THEN 1 ELSE 0 END) subsequent_entries_180
  ,MAX(CASE WHEN so.created_at < subsequent_so.created_at AND subsequent_so.created_at < DATE_ADD(so.created_at, INTERVAL 1 YEAR) THEN 1 ELSE 0 END) subsequent_entries_365
  ,MIN(CASE WHEN so.created_at < subsequent_so.created_at THEN subsequent_so.created_at ELSE NULL END) AS next_order_date

FROM spree_orders so

LEFT JOIN spree_orders subsequent_so
    on so.user_id = subsequent_so.user_id


WHERE DATE(so.locked_at) > 0 AND DATE(subsequent_so.locked_at) > 0
GROUP BY 1,2,3
ORDER BY so.id, so.created_at
  fields:
  - dimension: order_id
    type: number
    primary_key: true
    sql: ${TABLE}.order_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: subsequent_entries_number
    type: int
    sql: ${TABLE}.subsequent_entries_number
  - dimension: order_sequence_number
    type: int
    sql: ${TABLE}.order_sequence_number
  - dimension: subsequent_entries_90
    sql: ${TABLE}.subsequent_entries_90
  - dimension: subsequent_entries_180
    sql: ${TABLE}.subsequent_entries_180
  - dimension: subsequent_entries_365
    sql: ${TABLE}.subsequent_entries_365
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_users
    type: count_distinct
    sql: ${user_id}
  - measure: count_orders_with_sub
    type: count
    filters:
    - subsequent_entries_number: '>0'
  - measure: count_orders_no_sub
    type: count
    filters:
    - subsequent_entries_number: 0
  - measure: repeat_in_90_days
    type: count_distinct
    filters:
    - subsequent_entries_90: 1
    sql: ${user_id}
  - measure: repeat_in_180_days
    type: count_distinct
    filters:
    - subsequent_entries_180: 1
    sql: ${user_id}
  - measure: repeat_in_365_days
    type: count_distinct
    filters:
    - subsequent_entries_365: 1
    sql: ${user_id}
  - measure: repeat_rate_90
    type: number
    sql: 100.0*${repeat_in_90_days}/ NULLIF(${count_users},0)
  - measure: repeat_rate_180
    type: number
    sql: 100.0*${repeat_in_180_days}/ NULLIF(${count_users},0)
  - measure: repeat_rate_365
    type: number
    sql: 100.0*${repeat_in_365_days}/ NULLIF(${count_users},0)

subsequent_order_facts.sql
subsequent_order_facts.sql
subsequent_order_facts.view.lkml
subsequent_order_facts.view.lookml

Subsequent Order Facts

Blocks

Assumptions

Contents