dbt Saas translation

Workflow Step Velocity

Workflow Step Velocity model for saas translation.

inferred

Blocks

dbt SQL LKML LookML

workflow_step_velocity.sql · 87 lines

inferred

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

WITH date_lookup AS
  (
      SELECT dhour
            , EXTRACT(dow FROM dhour) AS day_of_week
            , SUM(CASE
                    WHEN EXTRACT(dow FROM dhour) NOT IN (6,0)
                    THEN 1
                    ELSE 0
                  END) OVER (ORDER BY dhour rows unbounded preceding) AS hour_seq
      FROM
      (
        SELECT DISTINCT(DATE_TRUNC('hour', DATEADD(hour, s + s2 * 10 + s3 * 100 + s4 * 1000 + s5 * 10000 + 1, (DATE_TRUNC('year', getDate()) + interval '-3 year') ))) AS dhour
                    from (SELECT 0 AS s UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s,
                    (SELECT 0 AS s2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s2,
                    (SELECT 0 AS s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s3,
                    (SELECT 0 AS s4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s4,
                    (SELECT 0 AS s5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s5
      )
  ),
  workflow as
  (
     SELECT wl.workflow_tracking_lookup_id, workflow_tracking_step_lookup_id, wl.account_id, workflow_step_class_id
     FROM {{ this }}.workflow_tracking_lookup wl
       INNER JOIN {{ this }}.workflow_tracking_steps_lookup wsl
       ON wsl.workflow_tracking_lookup_id = wl.workflow_tracking_lookup_id
  ),
  actions as
  (
    SELECT t1.*
    FROM
      (
        SELECT w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
        FROM {{ this }}.log_authorize_translations lat
          INNER JOIN workflow w ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
        GROUP BY w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.request_url, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
       FROM {{ this }}.log_assign_translations lat
       INNER JOIN workflow w
          ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode, MIN(lwa.created) as action_date
       FROM {{ this }}.log_workflow_action lwa
         INNER JOIN workflow w
         ON w.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode

     ) as t1
   )
   SELECT sa.workflow_tracking_lookup_id
    , sa.workflow_tracking_step_lookup_id as source_tracking_step_lookup_id
    , ta.workflow_tracking_step_lookup_id as target_tracking_step_lookup_id
    , sa.site_id, sa.target_locale_id, sa.hashcode
    , sa.action_date as source_action_date
    , ta.action_date as target_action_date
    , source_lookup.hour_seq AS source_seq_num
    , target_lookup.hour_seq AS target_seq_num
   FROM actions sa
     INNER JOIN actions ta
     ON sa.workflow_tracking_lookup_id = ta.workflow_tracking_lookup_id AND sa.site_id = ta.site_id AND sa.target_locale_id = ta.target_locale_id AND sa.hashcode = ta.hashcode
     LEFT JOIN date_lookup as source_lookup
     ON source_lookup.dhour = DATE_TRUNC('hour', sa.action_date)
     LEFT JOIN date_lookup as target_lookup
     ON target_lookup.dhour = DATE_TRUNC('hour', ta.action_date)

# models/schema.yml
version: 2

models:
  - name: workflow_step_velocity
    columns:
      - name: workflow_tracking_lookup_id
      - name: source_tracking_step_lookup_id
      - name: target_tracking_step_lookup_id
      - name: site_id
      - name: target_locale_id
      - name: hashcode
      - name: velocity_in_hours
      - name: velocity_in_hours_no_weekends

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.

workflow_step_velocity.sql · 69 lines

source-preserved validation: not checked

WITH date_lookup AS
  (
      SELECT dhour
            , EXTRACT(dow FROM dhour) AS day_of_week
            , SUM(CASE
                    WHEN EXTRACT(dow FROM dhour) NOT IN (6,0)
                    THEN 1
                    ELSE 0
                  END) OVER (ORDER BY dhour rows unbounded preceding) AS hour_seq
      FROM
      (
        SELECT DISTINCT(DATE_TRUNC('hour', DATEADD(hour, s + s2 * 10 + s3 * 100 + s4 * 1000 + s5 * 10000 + 1, (DATE_TRUNC('year', getDate()) + interval '-3 year') ))) AS dhour
                    from (SELECT 0 AS s UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s,
                    (SELECT 0 AS s2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s2,
                    (SELECT 0 AS s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s3,
                    (SELECT 0 AS s4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s4,
                    (SELECT 0 AS s5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s5
      )
  ),
  workflow as
  (
     SELECT wl.workflow_tracking_lookup_id, workflow_tracking_step_lookup_id, wl.account_id, workflow_step_class_id
     FROM ${TABLE}.workflow_tracking_lookup wl
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup wsl
       ON wsl.workflow_tracking_lookup_id = wl.workflow_tracking_lookup_id
  ),
  actions as
  (
    SELECT t1.*
    FROM
      (
        SELECT w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
        FROM ${TABLE}.log_authorize_translations lat
          INNER JOIN workflow w ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
        GROUP BY w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.request_url, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
       FROM ${TABLE}.log_assign_translations lat
       INNER JOIN workflow w
          ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode, MIN(lwa.created) as action_date
       FROM ${TABLE}.log_workflow_action lwa
         INNER JOIN workflow w
         ON w.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode

     ) as t1
   )
   SELECT sa.workflow_tracking_lookup_id
    , sa.workflow_tracking_step_lookup_id as source_tracking_step_lookup_id
    , ta.workflow_tracking_step_lookup_id as target_tracking_step_lookup_id
    , sa.site_id, sa.target_locale_id, sa.hashcode
    , sa.action_date as source_action_date
    , ta.action_date as target_action_date
    , source_lookup.hour_seq AS source_seq_num
    , target_lookup.hour_seq AS target_seq_num
   FROM actions sa
     INNER JOIN actions ta
     ON sa.workflow_tracking_lookup_id = ta.workflow_tracking_lookup_id AND sa.site_id = ta.site_id AND sa.target_locale_id = ta.target_locale_id AND sa.hashcode = ta.hashcode
     LEFT JOIN date_lookup as source_lookup
     ON source_lookup.dhour = DATE_TRUNC('hour', sa.action_date)
     LEFT JOIN date_lookup as target_lookup
     ON target_lookup.dhour = DATE_TRUNC('hour', ta.action_date)

workflow_step_velocity.view.lkml · 143 lines

inferred

view: workflow_step_velocity {
  derived_table: {
    sql: WITH date_lookup AS
  (
      SELECT dhour
            , EXTRACT(dow FROM dhour) AS day_of_week
            , SUM(CASE
                    WHEN EXTRACT(dow FROM dhour) NOT IN (6,0)
                    THEN 1
                    ELSE 0
                  END) OVER (ORDER BY dhour rows unbounded preceding) AS hour_seq
      FROM
      (
        SELECT DISTINCT(DATE_TRUNC('hour', DATEADD(hour, s + s2 * 10 + s3 * 100 + s4 * 1000 + s5 * 10000 + 1, (DATE_TRUNC('year', getDate()) + interval '-3 year') ))) AS dhour
                    from (SELECT 0 AS s UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s,
                    (SELECT 0 AS s2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s2,
                    (SELECT 0 AS s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s3,
                    (SELECT 0 AS s4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s4,
                    (SELECT 0 AS s5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s5
      )
  ),
  workflow as
  (
     SELECT wl.workflow_tracking_lookup_id, workflow_tracking_step_lookup_id, wl.account_id, workflow_step_class_id
     FROM ${TABLE}.workflow_tracking_lookup wl
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup wsl
       ON wsl.workflow_tracking_lookup_id = wl.workflow_tracking_lookup_id
  ),
  actions as
  (
    SELECT t1.*
    FROM
      (
        SELECT w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
        FROM ${TABLE}.log_authorize_translations lat
          INNER JOIN workflow w ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
        GROUP BY w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.request_url, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
       FROM ${TABLE}.log_assign_translations lat
       INNER JOIN workflow w
          ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode, MIN(lwa.created) as action_date
       FROM ${TABLE}.log_workflow_action lwa
         INNER JOIN workflow w
         ON w.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode

     ) as t1
   )
   SELECT sa.workflow_tracking_lookup_id
    , sa.workflow_tracking_step_lookup_id as source_tracking_step_lookup_id
    , ta.workflow_tracking_step_lookup_id as target_tracking_step_lookup_id
    , sa.site_id, sa.target_locale_id, sa.hashcode
    , sa.action_date as source_action_date
    , ta.action_date as target_action_date
    , source_lookup.hour_seq AS source_seq_num
    , target_lookup.hour_seq AS target_seq_num
   FROM actions sa
     INNER JOIN actions ta
     ON sa.workflow_tracking_lookup_id = ta.workflow_tracking_lookup_id AND sa.site_id = ta.site_id AND sa.target_locale_id = ta.target_locale_id AND sa.hashcode = ta.hashcode
     LEFT JOIN date_lookup as source_lookup
     ON source_lookup.dhour = DATE_TRUNC('hour', sa.action_date)
     LEFT JOIN date_lookup as target_lookup
     ON target_lookup.dhour = DATE_TRUNC('hour', ta.action_date) ;;
  }

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

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

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

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

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

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

  dimension: velocity_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.source_action_date, ${TABLE}.target_action_date) ;;
  }

  dimension: velocity_in_hours_no_weekends {
    type: number
    sql: ${TABLE}.target_seq_num - ${TABLE}.source_seq_num ;;
  }

  dimension_group: source_action_date {
    type: time
    timeframes: [time, date, week, month, dow_num, dom, hod]
    sql: ${TABLE}.source_action_date ;;
  }

  dimension_group: target_action_date {
    type: time
    timeframes: [time, date, week, month, dow_num, dom, hod]
    sql: ${TABLE}.target_action_date ;;
  }

  measure: average_velocity_in_hours {
    type: average
    drill_fields: [detail*]
    sql: CASE
  WHEN ${velocity_in_hours} < 0
  THEN NULL
  ELSE ${velocity_in_hours}
END ;;
  }

  measure: average_velocity_in_hours_no_weekends {
    type: average
    drill_fields: [detail*]
    sql: CASE
  WHEN ${velocity_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${velocity_in_hours_no_weekends}
END ;;
  }
}

workflow_step_velocity.view.lookml · 134 lines

source-preserved

- view: workflow_step_velocity
  derived_table:
    sql: WITH date_lookup AS
  (
      SELECT dhour
            , EXTRACT(dow FROM dhour) AS day_of_week
            , SUM(CASE
                    WHEN EXTRACT(dow FROM dhour) NOT IN (6,0)
                    THEN 1
                    ELSE 0
                  END) OVER (ORDER BY dhour rows unbounded preceding) AS hour_seq
      FROM
      (
        SELECT DISTINCT(DATE_TRUNC('hour', DATEADD(hour, s + s2 * 10 + s3 * 100 + s4 * 1000 + s5 * 10000 + 1, (DATE_TRUNC('year', getDate()) + interval '-3 year') ))) AS dhour
                    from (SELECT 0 AS s UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s,
                    (SELECT 0 AS s2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s2,
                    (SELECT 0 AS s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s3,
                    (SELECT 0 AS s4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s4,
                    (SELECT 0 AS s5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) AS s5
      )
  ),
  workflow as
  (
     SELECT wl.workflow_tracking_lookup_id, workflow_tracking_step_lookup_id, wl.account_id, workflow_step_class_id
     FROM ${TABLE}.workflow_tracking_lookup wl
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup wsl
       ON wsl.workflow_tracking_lookup_id = wl.workflow_tracking_lookup_id
  ),
  actions as
  (
    SELECT t1.*
    FROM
      (
        SELECT w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
        FROM ${TABLE}.log_authorize_translations lat
          INNER JOIN workflow w ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
        GROUP BY w.workflow_tracking_lookup_id, lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.request_url, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode, MIN(lat.created) as action_date
       FROM ${TABLE}.log_assign_translations lat
       INNER JOIN workflow w
          ON w.workflow_tracking_step_lookup_id = lat.current_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id,  lat.current_workflow_tracking_step_lookup_id, lat.site_id, lat.target_locale_id, lat.hashcode

      UNION ALL

       SELECT w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id as workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode, MIN(lwa.created) as action_date
       FROM ${TABLE}.log_workflow_action lwa
         INNER JOIN workflow w
         ON w.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       GROUP BY w.workflow_tracking_lookup_id, lwa.target_workflow_tracking_step_lookup_id, lwa.site_id, lwa.target_locale_id, lwa.hashcode

     ) as t1
   )
   SELECT sa.workflow_tracking_lookup_id
    , sa.workflow_tracking_step_lookup_id as source_tracking_step_lookup_id
    , ta.workflow_tracking_step_lookup_id as target_tracking_step_lookup_id
    , sa.site_id, sa.target_locale_id, sa.hashcode
    , sa.action_date as source_action_date
    , ta.action_date as target_action_date
    , source_lookup.hour_seq AS source_seq_num
    , target_lookup.hour_seq AS target_seq_num
   FROM actions sa
     INNER JOIN actions ta
     ON sa.workflow_tracking_lookup_id = ta.workflow_tracking_lookup_id AND sa.site_id = ta.site_id AND sa.target_locale_id = ta.target_locale_id AND sa.hashcode = ta.hashcode
     LEFT JOIN date_lookup as source_lookup
     ON source_lookup.dhour = DATE_TRUNC('hour', sa.action_date)
     LEFT JOIN date_lookup as target_lookup
     ON target_lookup.dhour = DATE_TRUNC('hour', ta.action_date)
  fields:
  - dimension: workflow_tracking_lookup_id
    type: int
    sql: ${TABLE}.workflow_tracking_lookup_id
  - dimension: source_tracking_step_lookup_id
    type: int
    sql: ${TABLE}.source_tracking_step_lookup_id
  - dimension: target_tracking_step_lookup_id
    type: int
    sql: ${TABLE}.target_tracking_step_lookup_id
  - dimension: site_id
    type: int
    sql: ${TABLE}.site_id
  - dimension: target_locale_id
    sql: ${TABLE}.target_locale_id
  - dimension: hashcode
    sql: ${TABLE}.hashcode
  - dimension: velocity_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.source_action_date, ${TABLE}.target_action_date)
  - dimension: velocity_in_hours_no_weekends
    type: number
    sql: ${TABLE}.target_seq_num - ${TABLE}.source_seq_num
  - dimension_group: source_action_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.source_action_date
  - dimension_group: target_action_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.target_action_date
  - measure: average_velocity_in_hours
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${velocity_in_hours} < 0
  THEN NULL
  ELSE ${velocity_in_hours}
END
  - measure: average_velocity_in_hours_no_weekends
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${velocity_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${velocity_in_hours_no_weekends}
END

workflow_step_velocity.sql
workflow_step_velocity.sql
workflow_step_velocity.view.lkml
workflow_step_velocity.view.lookml

Workflow Step Velocity

Blocks

Assumptions

Contents