SQL Design marketplace

Member Statuschangelogentry

Member Statuschangelogentry model for design marketplace.

source-preserved

Blocks

dbt SQL LKML LookML

member_statuschangelogentry.sql · 36 lines

inferred

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

SELECT
subscriber_id
, GROUP_CONCAT(subscriber_status_id ORDER BY timestamp ASC SEPARATOR ',') AS status_change_history
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_status_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS current_status
, SUBSTRING_INDEX(GROUP_CONCAT(changed_by_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_changed_by_id
, SUBSTRING_INDEX(GROUP_CONCAT(note ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_note
, MAX(TIMESTAMP) most_recent_status_change
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_tier_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_subscriber_tier_lock
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_quantity ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_quantity
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_value ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_value
, SUBSTRING_INDEX(GROUP_CONCAT(job_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_job_lock
FROM
member_statuschangelogentry
GROUP BY
subscriber_id

# models/schema.yml
version: 2

models:
  - name: member_statuschangelogentry
    columns:
      - name: subscriber_id
        tests:
          - unique
          - not_null
      - name: status_change_history
      - name: current_status
      - name: most_recent_changed_by_id
      - name: user_that_made_most_recent_change
      - name: most_recent_note
      - name: most_recent_subscriber_tier_lock
      - name: most_recent_job_lock

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.

member_statuschangelogentry.sql · 15 lines

source-preserved validation: not checked

SELECT
subscriber_id
, GROUP_CONCAT(subscriber_status_id ORDER BY timestamp ASC SEPARATOR ',') AS status_change_history
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_status_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS current_status
, SUBSTRING_INDEX(GROUP_CONCAT(changed_by_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_changed_by_id
, SUBSTRING_INDEX(GROUP_CONCAT(note ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_note
, MAX(TIMESTAMP) most_recent_status_change
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_tier_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_subscriber_tier_lock
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_quantity ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_quantity
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_value ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_value
, SUBSTRING_INDEX(GROUP_CONCAT(job_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_job_lock
FROM
member_statuschangelogentry
GROUP BY
subscriber_id

member_statuschangelogentry.view.lkml · 87 lines

inferred

view: member_statuschangelogentry {
  derived_table: {
    sql: SELECT
subscriber_id
, GROUP_CONCAT(subscriber_status_id ORDER BY timestamp ASC SEPARATOR ',') AS status_change_history
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_status_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS current_status
, SUBSTRING_INDEX(GROUP_CONCAT(changed_by_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_changed_by_id
, SUBSTRING_INDEX(GROUP_CONCAT(note ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_note
, MAX(TIMESTAMP) most_recent_status_change
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_tier_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_subscriber_tier_lock
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_quantity ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_quantity
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_value ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_value
, SUBSTRING_INDEX(GROUP_CONCAT(job_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_job_lock
FROM
member_statuschangelogentry
GROUP BY
subscriber_id ;;
  }

  dimension: subscriber_id {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.subscriber_id ;;
  }

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

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

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

  dimension: user_that_made_most_recent_change {
    type: string
    sql: (SELECT
  email
FROM
  user_account
WHERE
  ${TABLE}.most_recent_changed_by_id = user_account.id) ;;
  }

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

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

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

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

  dimension_group: most_recent_limit_job_quantity {
    type: int
    sql: ${TABLE}.most_recent_limit_job_quantity ;;
  }

  dimension_group: most_recent_limit_job_value {
    type: int
    sql: ${TABLE}.most_recent_limit_job_value ;;
  }

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

member_statuschangelogentry.view.lookml · 67 lines

source-preserved

- view: member_statuschangelogentry
  derived_table:
    sql: SELECT
subscriber_id
, GROUP_CONCAT(subscriber_status_id ORDER BY timestamp ASC SEPARATOR ',') AS status_change_history
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_status_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS current_status
, SUBSTRING_INDEX(GROUP_CONCAT(changed_by_id ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_changed_by_id
, SUBSTRING_INDEX(GROUP_CONCAT(note ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_note
, MAX(TIMESTAMP) most_recent_status_change
, SUBSTRING_INDEX(GROUP_CONCAT(subscriber_tier_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_subscriber_tier_lock
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_quantity ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_quantity
, SUBSTRING_INDEX(GROUP_CONCAT(limit_job_value ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_limit_job_value
, SUBSTRING_INDEX(GROUP_CONCAT(job_lock ORDER BY timestamp DESC SEPARATOR '|'),'|',1) AS most_recent_job_lock
FROM
member_statuschangelogentry
GROUP BY
subscriber_id
  fields:
  - dimension: subscriber_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.subscriber_id
  - dimension: status_change_history
    type: string
    sql: ${TABLE}.status_change_history
  - dimension: current_status
    type: string
    sql: ${TABLE}.current_status
  - dimension: most_recent_changed_by_id
    type: int
    sql: ${TABLE}.most_recent_changed_by_id
  - dimension: user_that_made_most_recent_change
    type: string
    sql: (SELECT
  email
FROM
  user_account
WHERE
  ${TABLE}.most_recent_changed_by_id = user_account.id)
  - dimension: most_recent_note
    type: string
    sql: ${TABLE}.most_recent_note
  - dimension: most_recent_subscriber_tier_lock
    type: string
    sql: ${TABLE}.most_recent_subscriber_tier_lock
  - dimension: most_recent_job_lock
    type: string
    sql: ${TABLE}.most_recent_job_lock
  - dimension_group: most_recent_status_change
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.most_recent_status_change
  - dimension_group: most_recent_limit_job_quantity
    type: int
    sql: ${TABLE}.most_recent_limit_job_quantity
  - dimension_group: most_recent_limit_job_value
    type: int
    sql: ${TABLE}.most_recent_limit_job_value
  - measure: count
    type: count
    drill_fields:
    - detail*

member_statuschangelogentry.sql
member_statuschangelogentry.sql
member_statuschangelogentry.view.lkml
member_statuschangelogentry.view.lookml

Member Statuschangelogentry

Blocks

Assumptions

Contents