dbt Ecommerce ticketing

User Status

User Status model for ecommerce ticketing.

inferred

Blocks

dbt SQL LKML LookML

user_status.sql · 58 lines

inferred

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

SELECT 
        a.user_id,
        a.month,
        a.created_at,
        n.status as status_new,
        r.status as status_reactivated,
        c.status as status_churned,
        COALESCE(n.status, r.status, c.status) as status
      FROM (
        SELECT 
          DISTINCT user_id,
          TO_DATE(ts, 'YYYY-MM-DD') as created_at,
          TO_CHAR(ts, 'YYYY-MM') as month
        FROM ts_user_statuses
      ) a
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'new'
      ) n ON n.user_id = a.user_id AND n.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'reactivated'
      ) r ON r.user_id = a.user_id AND r.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'churned'
      ) c ON c.user_id = a.user_id AND c.month = a.month
      WHERE n.status IS NOT NULL OR r.status IS NOT NULL OR c.status IS NOT NULL
      ORDER BY 1, 2

# models/schema.yml
version: 2

models:
  - name: user_status
    columns:
      - name: user_id
      - name: status_date
      - name: status_month
      - name: status_new
      - name: status_reactivated
      - name: status_churned
      - name: status

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_status.sql · 41 lines

source-preserved validation: parses

SELECT 
        a.user_id,
        a.month,
        a.created_at,
        n.status as status_new,
        r.status as status_reactivated,
        c.status as status_churned,
        COALESCE(n.status, r.status, c.status) as status
      FROM (
        SELECT 
          DISTINCT user_id,
          TO_DATE(ts, 'YYYY-MM-DD') as created_at,
          TO_CHAR(ts, 'YYYY-MM') as month
        FROM ts_user_statuses
      ) a
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'new'
      ) n ON n.user_id = a.user_id AND n.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'reactivated'
      ) r ON r.user_id = a.user_id AND r.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'churned'
      ) c ON c.user_id = a.user_id AND c.month = a.month
      WHERE n.status IS NOT NULL OR r.status IS NOT NULL OR c.status IS NOT NULL
      ORDER BY 1, 2

user_status.view.lkml · 115 lines

source-preserved

view: user_status {
  derived_table: {
    sql: SELECT 
        a.user_id,
        a.month,
        a.created_at,
        n.status as status_new,
        r.status as status_reactivated,
        c.status as status_churned,
        COALESCE(n.status, r.status, c.status) as status
      FROM (
        SELECT 
          DISTINCT user_id,
          TO_DATE(ts, 'YYYY-MM-DD') as created_at,
          TO_CHAR(ts, 'YYYY-MM') as month
        FROM ts_user_statuses
      ) a
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'new'
      ) n ON n.user_id = a.user_id AND n.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'reactivated'
      ) r ON r.user_id = a.user_id AND r.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'churned'
      ) c ON c.user_id = a.user_id AND c.month = a.month
      WHERE n.status IS NOT NULL OR r.status IS NOT NULL OR c.status IS NOT NULL
      ORDER BY 1, 2 ;;
  }

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

  dimension: status_date {
    type: date
    sql: ${TABLE}.created_at ;;
  }

  dimension: status_month {
    type: string
    sql: ${TABLE}.month ;;
  }

  dimension: status_new {
    type: string
    sql: ${TABLE}.status_new ;;
  }

  dimension: status_reactivated {
    type: string
    sql: ${TABLE}.status_reactivated ;;
  }

  dimension: status_churned {
    type: string
    sql: ${TABLE}.status_churned ;;
  }

  dimension: status {
    type: string
    sql: ${TABLE}.status ;;
  }

  measure: count_users_status {
    type: number
    sql: CASE WHEN ${status} = 'churned' THEN -1.0 * ${count_users} ELSE ${count_users} END ;;
  }

  measure: count_users_new {
    type: count_distinct
    sql: CASE WHEN ${status} = 'new' THEN ${user_id} ELSE NULL END ;;
  }

  measure: count_users_reactivated {
    type: count_distinct
    sql: CASE WHEN ${status} = 'reactivated' THEN ${user_id} ELSE NULL END ;;
  }

  measure: count_users_churned {
    type: number
    sql: -1.0 * COUNT(DISTINCT CASE WHEN ${status} = 'churned' THEN ${user_id} ELSE NULL END) ;;
  }

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

  measure: count_user_adj {
    type: number
    sql: ${count_users_new} + ${count_users_reactivated} + ${count_users_churned} ;;
  }

  measure: quick_ratio {
    type: number
    sql: 1.0 * (${count_users_new} + ${count_users_reactivated}) / NULLIF((-1.0 * ${count_users_churned}), 0) ;;
  }
}

user_status.view.lookml · 86 lines

inferred

- view: user_status
  derived_table:
    sql: SELECT 
        a.user_id,
        a.month,
        a.created_at,
        n.status as status_new,
        r.status as status_reactivated,
        c.status as status_churned,
        COALESCE(n.status, r.status, c.status) as status
      FROM (
        SELECT 
          DISTINCT user_id,
          TO_DATE(ts, 'YYYY-MM-DD') as created_at,
          TO_CHAR(ts, 'YYYY-MM') as month
        FROM ts_user_statuses
      ) a
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'new'
      ) n ON n.user_id = a.user_id AND n.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'reactivated'
      ) r ON r.user_id = a.user_id AND r.month = a.month
      LEFT JOIN (
        SELECT 
          user_id,
          TO_CHAR(ts, 'YYYY-MM') as month,
          status
        FROM ts_user_statuses
        WHERE status = 'churned'
      ) c ON c.user_id = a.user_id AND c.month = a.month
      WHERE n.status IS NOT NULL OR r.status IS NOT NULL OR c.status IS NOT NULL
      ORDER BY 1, 2
  fields:
  - dimension: user_id
    type: string
    sql: ${TABLE}.user_id
  - dimension: status_date
    type: date
    sql: ${TABLE}.created_at
  - dimension: status_month
    type: string
    sql: ${TABLE}.month
  - dimension: status_new
    type: string
    sql: ${TABLE}.status_new
  - dimension: status_reactivated
    type: string
    sql: ${TABLE}.status_reactivated
  - dimension: status_churned
    type: string
    sql: ${TABLE}.status_churned
  - dimension: status
    type: string
    sql: ${TABLE}.status
  - measure: count_users_status
    type: number
    sql: CASE WHEN ${status} = 'churned' THEN -1.0 * ${count_users} ELSE ${count_users} END
  - measure: count_users_new
    type: count_distinct
    sql: CASE WHEN ${status} = 'new' THEN ${user_id} ELSE NULL END
  - measure: count_users_reactivated
    type: count_distinct
    sql: CASE WHEN ${status} = 'reactivated' THEN ${user_id} ELSE NULL END
  - measure: count_users_churned
    type: number
    sql: -1.0 * COUNT(DISTINCT CASE WHEN ${status} = 'churned' THEN ${user_id} ELSE NULL END)
  - measure: count_users
    type: count_distinct
    sql: ${user_id}
  - measure: count_user_adj
    type: number
    sql: ${count_users_new} + ${count_users_reactivated} + ${count_users_churned}
  - measure: quick_ratio
    type: number
    sql: 1.0 * (${count_users_new} + ${count_users_reactivated}) / NULLIF((-1.0 * ${count_users_churned}), 0)

user_status.sql
user_status.sql
user_status.view.lkml
user_status.view.lookml

User Status

Blocks

Assumptions

Contents