Translation Velocity

-- models/translation_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
      )
  )
  SELECT
      authorize.site_id
    , authorize.target_locale_id
    , authorize.hashcode
    , authorize.date_authorized
    , assign.date_assigned
    , actions.date_translation
    , actions.date_edited
    , actions.date_reviewed
    , actions.date_published
    , authorize.request_url
    , str.request_id
    , str.word_count
    , authorize_lookup.hour_seq AS authorized_seq_num
    , assign_lookup.hour_seq AS assigned_seq_num
    , review_lookup.hour_seq AS reviewed_seq_num
    , edit_lookup.hour_seq AS edited_seq_num
    , published_lookup.hour_seq AS published_seq_num
    , (authorize.site_id || '|' || CAST(authorize.target_locale_id AS VARCHAR(5)) || '|' || CAST(authorize.hashcode AS VARCHAR(32))) AS action_key
  FROM
    (
      SELECT site_id, target_locale_id, hashcode, request_url, MIN(created) as date_authorized
      FROM {{ this }}.log_authorize_translations
      GROUP BY site_id, target_locale_id, request_url, hashcode
    ) authorize
  LEFT JOIN
    (
     SELECT site_id, target_locale_id, hashcode, MIN(created) as date_assigned
     FROM {{ this }}.log_assign_translations
     GROUP BY site_id, target_locale_id, hashcode
    ) assign
  ON assign.site_id = authorize.site_id AND assign.target_locale_id = authorize.target_locale_id AND assign.hashcode = authorize.hashcode
  LEFT JOIN
    (
     SELECT lwa.site_id, lwa.target_locale_id, lwa.hashcode
          , MIN(CASE WHEN lwa.action_type = 5 THEN lwa.created ELSE NULL END) as date_translation
          , MIN(CASE WHEN lwa.action_type = 6 THEN lwa.created ELSE NULL END) as date_reviewed
          , MIN(CASE WHEN lwa.action_type = 7 THEN lwa.created ELSE NULL END) as date_edited
          , MIN(CASE WHEN slookup.workflow_step_class_id = 5 THEN lwa.created ELSE NULL END) as date_published
     FROM {{ this }}.log_workflow_action lwa
       INNER JOIN {{ this }}.workflow_tracking_steps_lookup slookup
       ON slookup.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       INNER JOIN {{ this }}.workflow_step_classes wsc
       ON wsc.workflow_step_class_id = slookup.workflow_step_class_id
     GROUP BY lwa.site_id, lwa.target_locale_id, lwa.hashcode
    ) actions
  ON actions.site_id = authorize.site_id AND actions.target_locale_id = authorize.target_locale_id AND actions.hashcode = authorize.hashcode
  INNER JOIN {{ this }}.strings str
  ON str.site_id = authorize.site_id AND str.hashcode = authorize.hashcode
  LEFT JOIN date_lookup as authorize_lookup
  ON authorize_lookup.dhour = DATE_TRUNC('hour', date_authorized)
  LEFT JOIN date_lookup as assign_lookup
  ON assign_lookup.dhour = DATE_TRUNC('hour', date_assigned)
  LEFT JOIN date_lookup as review_lookup
  ON review_lookup.dhour = DATE_TRUNC('hour', date_reviewed)
  LEFT JOIN date_lookup as edit_lookup
  ON edit_lookup.dhour = DATE_TRUNC('hour', date_edited)
  LEFT JOIN date_lookup as published_lookup
  ON published_lookup.dhour = DATE_TRUNC('hour', date_published)
  LEFT JOIN {{ this }}.log_smart_match lsm
  ON lsm.site_id = authorize.site_id AND lsm.target_locale_id = authorize.target_locale_id AND lsm.hashcode = authorize.hashcode
  LEFT JOIN
    (
      SELECT site_id, hashcode, MAX(created) as last_action_date
      FROM {{ this }}.log_string_active_history
      GROUP BY site_id, hashcode
    ) lsah
  ON lsah.site_id = authorize.site_id AND lsah.hashcode = authorize.hashcode
  WHERE lsm.log_match_id IS NULL
    AND (lsah.last_action_date IS NULL OR lsah.last_action_date < authorize.date_authorized)

# models/schema.yml
version: 2

models:
  - name: translation_velocity
    columns:
      - name: translation_key
      - name: site_id
      - name: target_locale_id
      - name: hashcode
      - name: authorized_to_published_in_hours
      - name: authorized_to_published_in_hours_no_weekends
      - name: authorized_to_assigned_in_hours
      - name: authorized_to_translated_in_hours
      - name: assigned_to_published_in_hours
      - name: assigned_to_published_in_hours_no_weekends
      - name: assigned_to_translated_in_hours
      - name: translated_to_edited_in_hours
      - name: edited_to_reviwed_in_hours
      - name: edited_to_published_in_hours
      - name: edited_to_published_in_hours_no_weekends
      - name: reviewed_to_published_in_hours
      - name: reviewed_to_published_in_hours_no_weekends
      - name: word_count
      - name: request_url
      - name: request_id

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.

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
      )
  )
  SELECT
      authorize.site_id
    , authorize.target_locale_id
    , authorize.hashcode
    , authorize.date_authorized
    , assign.date_assigned
    , actions.date_translation
    , actions.date_edited
    , actions.date_reviewed
    , actions.date_published
    , authorize.request_url
    , str.request_id
    , str.word_count
    , authorize_lookup.hour_seq AS authorized_seq_num
    , assign_lookup.hour_seq AS assigned_seq_num
    , review_lookup.hour_seq AS reviewed_seq_num
    , edit_lookup.hour_seq AS edited_seq_num
    , published_lookup.hour_seq AS published_seq_num
    , (authorize.site_id || '|' || CAST(authorize.target_locale_id AS VARCHAR(5)) || '|' || CAST(authorize.hashcode AS VARCHAR(32))) AS action_key
  FROM
    (
      SELECT site_id, target_locale_id, hashcode, request_url, MIN(created) as date_authorized
      FROM ${TABLE}.log_authorize_translations
      GROUP BY site_id, target_locale_id, request_url, hashcode
    ) authorize
  LEFT JOIN
    (
     SELECT site_id, target_locale_id, hashcode, MIN(created) as date_assigned
     FROM ${TABLE}.log_assign_translations
     GROUP BY site_id, target_locale_id, hashcode
    ) assign
  ON assign.site_id = authorize.site_id AND assign.target_locale_id = authorize.target_locale_id AND assign.hashcode = authorize.hashcode
  LEFT JOIN
    (
     SELECT lwa.site_id, lwa.target_locale_id, lwa.hashcode
          , MIN(CASE WHEN lwa.action_type = 5 THEN lwa.created ELSE NULL END) as date_translation
          , MIN(CASE WHEN lwa.action_type = 6 THEN lwa.created ELSE NULL END) as date_reviewed
          , MIN(CASE WHEN lwa.action_type = 7 THEN lwa.created ELSE NULL END) as date_edited
          , MIN(CASE WHEN slookup.workflow_step_class_id = 5 THEN lwa.created ELSE NULL END) as date_published
     FROM ${TABLE}.log_workflow_action lwa
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup slookup
       ON slookup.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       INNER JOIN ${TABLE}.workflow_step_classes wsc
       ON wsc.workflow_step_class_id = slookup.workflow_step_class_id
     GROUP BY lwa.site_id, lwa.target_locale_id, lwa.hashcode
    ) actions
  ON actions.site_id = authorize.site_id AND actions.target_locale_id = authorize.target_locale_id AND actions.hashcode = authorize.hashcode
  INNER JOIN ${TABLE}.strings str
  ON str.site_id = authorize.site_id AND str.hashcode = authorize.hashcode
  LEFT JOIN date_lookup as authorize_lookup
  ON authorize_lookup.dhour = DATE_TRUNC('hour', date_authorized)
  LEFT JOIN date_lookup as assign_lookup
  ON assign_lookup.dhour = DATE_TRUNC('hour', date_assigned)
  LEFT JOIN date_lookup as review_lookup
  ON review_lookup.dhour = DATE_TRUNC('hour', date_reviewed)
  LEFT JOIN date_lookup as edit_lookup
  ON edit_lookup.dhour = DATE_TRUNC('hour', date_edited)
  LEFT JOIN date_lookup as published_lookup
  ON published_lookup.dhour = DATE_TRUNC('hour', date_published)
  LEFT JOIN ${TABLE}.log_smart_match lsm
  ON lsm.site_id = authorize.site_id AND lsm.target_locale_id = authorize.target_locale_id AND lsm.hashcode = authorize.hashcode
  LEFT JOIN
    (
      SELECT site_id, hashcode, MAX(created) as last_action_date
      FROM ${TABLE}.log_string_active_history
      GROUP BY site_id, hashcode
    ) lsah
  ON lsah.site_id = authorize.site_id AND lsah.hashcode = authorize.hashcode
  WHERE lsm.log_match_id IS NULL
    AND (lsah.last_action_date IS NULL OR lsah.last_action_date < authorize.date_authorized)

view: translation_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
      )
  )
  SELECT
      authorize.site_id
    , authorize.target_locale_id
    , authorize.hashcode
    , authorize.date_authorized
    , assign.date_assigned
    , actions.date_translation
    , actions.date_edited
    , actions.date_reviewed
    , actions.date_published
    , authorize.request_url
    , str.request_id
    , str.word_count
    , authorize_lookup.hour_seq AS authorized_seq_num
    , assign_lookup.hour_seq AS assigned_seq_num
    , review_lookup.hour_seq AS reviewed_seq_num
    , edit_lookup.hour_seq AS edited_seq_num
    , published_lookup.hour_seq AS published_seq_num
    , (authorize.site_id || '|' || CAST(authorize.target_locale_id AS VARCHAR(5)) || '|' || CAST(authorize.hashcode AS VARCHAR(32))) AS action_key
  FROM
    (
      SELECT site_id, target_locale_id, hashcode, request_url, MIN(created) as date_authorized
      FROM ${TABLE}.log_authorize_translations
      GROUP BY site_id, target_locale_id, request_url, hashcode
    ) authorize
  LEFT JOIN
    (
     SELECT site_id, target_locale_id, hashcode, MIN(created) as date_assigned
     FROM ${TABLE}.log_assign_translations
     GROUP BY site_id, target_locale_id, hashcode
    ) assign
  ON assign.site_id = authorize.site_id AND assign.target_locale_id = authorize.target_locale_id AND assign.hashcode = authorize.hashcode
  LEFT JOIN
    (
     SELECT lwa.site_id, lwa.target_locale_id, lwa.hashcode
          , MIN(CASE WHEN lwa.action_type = 5 THEN lwa.created ELSE NULL END) as date_translation
          , MIN(CASE WHEN lwa.action_type = 6 THEN lwa.created ELSE NULL END) as date_reviewed
          , MIN(CASE WHEN lwa.action_type = 7 THEN lwa.created ELSE NULL END) as date_edited
          , MIN(CASE WHEN slookup.workflow_step_class_id = 5 THEN lwa.created ELSE NULL END) as date_published
     FROM ${TABLE}.log_workflow_action lwa
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup slookup
       ON slookup.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       INNER JOIN ${TABLE}.workflow_step_classes wsc
       ON wsc.workflow_step_class_id = slookup.workflow_step_class_id
     GROUP BY lwa.site_id, lwa.target_locale_id, lwa.hashcode
    ) actions
  ON actions.site_id = authorize.site_id AND actions.target_locale_id = authorize.target_locale_id AND actions.hashcode = authorize.hashcode
  INNER JOIN ${TABLE}.strings str
  ON str.site_id = authorize.site_id AND str.hashcode = authorize.hashcode
  LEFT JOIN date_lookup as authorize_lookup
  ON authorize_lookup.dhour = DATE_TRUNC('hour', date_authorized)
  LEFT JOIN date_lookup as assign_lookup
  ON assign_lookup.dhour = DATE_TRUNC('hour', date_assigned)
  LEFT JOIN date_lookup as review_lookup
  ON review_lookup.dhour = DATE_TRUNC('hour', date_reviewed)
  LEFT JOIN date_lookup as edit_lookup
  ON edit_lookup.dhour = DATE_TRUNC('hour', date_edited)
  LEFT JOIN date_lookup as published_lookup
  ON published_lookup.dhour = DATE_TRUNC('hour', date_published)
  LEFT JOIN ${TABLE}.log_smart_match lsm
  ON lsm.site_id = authorize.site_id AND lsm.target_locale_id = authorize.target_locale_id AND lsm.hashcode = authorize.hashcode
  LEFT JOIN
    (
      SELECT site_id, hashcode, MAX(created) as last_action_date
      FROM ${TABLE}.log_string_active_history
      GROUP BY site_id, hashcode
    ) lsah
  ON lsah.site_id = authorize.site_id AND lsah.hashcode = authorize.hashcode
  WHERE lsm.log_match_id IS NULL
    AND (lsah.last_action_date IS NULL OR lsah.last_action_date < authorize.date_authorized) ;;
  }

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

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

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

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

  dimension: authorized_to_published_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_published) ;;
  }

  dimension: authorized_to_published_in_hours_no_weekends {
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.authorized_seq_num ;;
  }

  dimension: authorized_to_assigned_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_assigned) ;;
  }

  dimension: authorized_to_translated_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_translation) ;;
  }

  dimension: assigned_to_published_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_assigned, ${TABLE}.date_published) ;;
  }

  dimension: assigned_to_published_in_hours_no_weekends {
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.assigned_seq_num ;;
  }

  dimension: assigned_to_translated_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_assigned, ${TABLE}.date_translation) ;;
  }

  dimension: translated_to_edited_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_translation, ${TABLE}.date_edited) ;;
  }

  dimension: edited_to_reviwed_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_edited, ${TABLE}.date_reviewed) ;;
  }

  dimension: edited_to_published_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_edited, ${TABLE}.date_published) ;;
  }

  dimension: edited_to_published_in_hours_no_weekends {
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.edited_seq_num ;;
  }

  dimension: reviewed_to_published_in_hours {
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_reviewed, ${TABLE}.date_published) ;;
  }

  dimension: reviewed_to_published_in_hours_no_weekends {
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.reviewed_seq_num ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

  measure: average_edited_to_published_in_hours {
    description: "Show how long takes review step. Works for simple workflow - when edit step placed before review. This can not be used for all cases."
    type: average
    drill_fields: [detail*]
    sql: CASE
  WHEN ${edited_to_published_in_hours} < 0
  THEN NULL
  ELSE ${edited_to_published_in_hours}
END ;;
  }

  measure: average_edited_to_published_in_hours_no_weekends {
    description: "Show how long takes review step withot weekends. Works for simple workflow - when edit step placed before review. This can not be used for all cases."
    type: average
    drill_fields: [detail*]
    sql: CASE
  WHEN ${edited_to_published_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${edited_to_published_in_hours_no_weekends}
END ;;
  }

  measure: total_word_count {
    type: sum
    drill_fields: [detail*]
    sql: ${word_count} ;;
  }
}

- view: translation_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
      )
  )
  SELECT
      authorize.site_id
    , authorize.target_locale_id
    , authorize.hashcode
    , authorize.date_authorized
    , assign.date_assigned
    , actions.date_translation
    , actions.date_edited
    , actions.date_reviewed
    , actions.date_published
    , authorize.request_url
    , str.request_id
    , str.word_count
    , authorize_lookup.hour_seq AS authorized_seq_num
    , assign_lookup.hour_seq AS assigned_seq_num
    , review_lookup.hour_seq AS reviewed_seq_num
    , edit_lookup.hour_seq AS edited_seq_num
    , published_lookup.hour_seq AS published_seq_num
    , (authorize.site_id || '|' || CAST(authorize.target_locale_id AS VARCHAR(5)) || '|' || CAST(authorize.hashcode AS VARCHAR(32))) AS action_key
  FROM
    (
      SELECT site_id, target_locale_id, hashcode, request_url, MIN(created) as date_authorized
      FROM ${TABLE}.log_authorize_translations
      GROUP BY site_id, target_locale_id, request_url, hashcode
    ) authorize
  LEFT JOIN
    (
     SELECT site_id, target_locale_id, hashcode, MIN(created) as date_assigned
     FROM ${TABLE}.log_assign_translations
     GROUP BY site_id, target_locale_id, hashcode
    ) assign
  ON assign.site_id = authorize.site_id AND assign.target_locale_id = authorize.target_locale_id AND assign.hashcode = authorize.hashcode
  LEFT JOIN
    (
     SELECT lwa.site_id, lwa.target_locale_id, lwa.hashcode
          , MIN(CASE WHEN lwa.action_type = 5 THEN lwa.created ELSE NULL END) as date_translation
          , MIN(CASE WHEN lwa.action_type = 6 THEN lwa.created ELSE NULL END) as date_reviewed
          , MIN(CASE WHEN lwa.action_type = 7 THEN lwa.created ELSE NULL END) as date_edited
          , MIN(CASE WHEN slookup.workflow_step_class_id = 5 THEN lwa.created ELSE NULL END) as date_published
     FROM ${TABLE}.log_workflow_action lwa
       INNER JOIN ${TABLE}.workflow_tracking_steps_lookup slookup
       ON slookup.workflow_tracking_step_lookup_id = lwa.target_workflow_tracking_step_lookup_id
       INNER JOIN ${TABLE}.workflow_step_classes wsc
       ON wsc.workflow_step_class_id = slookup.workflow_step_class_id
     GROUP BY lwa.site_id, lwa.target_locale_id, lwa.hashcode
    ) actions
  ON actions.site_id = authorize.site_id AND actions.target_locale_id = authorize.target_locale_id AND actions.hashcode = authorize.hashcode
  INNER JOIN ${TABLE}.strings str
  ON str.site_id = authorize.site_id AND str.hashcode = authorize.hashcode
  LEFT JOIN date_lookup as authorize_lookup
  ON authorize_lookup.dhour = DATE_TRUNC('hour', date_authorized)
  LEFT JOIN date_lookup as assign_lookup
  ON assign_lookup.dhour = DATE_TRUNC('hour', date_assigned)
  LEFT JOIN date_lookup as review_lookup
  ON review_lookup.dhour = DATE_TRUNC('hour', date_reviewed)
  LEFT JOIN date_lookup as edit_lookup
  ON edit_lookup.dhour = DATE_TRUNC('hour', date_edited)
  LEFT JOIN date_lookup as published_lookup
  ON published_lookup.dhour = DATE_TRUNC('hour', date_published)
  LEFT JOIN ${TABLE}.log_smart_match lsm
  ON lsm.site_id = authorize.site_id AND lsm.target_locale_id = authorize.target_locale_id AND lsm.hashcode = authorize.hashcode
  LEFT JOIN
    (
      SELECT site_id, hashcode, MAX(created) as last_action_date
      FROM ${TABLE}.log_string_active_history
      GROUP BY site_id, hashcode
    ) lsah
  ON lsah.site_id = authorize.site_id AND lsah.hashcode = authorize.hashcode
  WHERE lsm.log_match_id IS NULL
    AND (lsah.last_action_date IS NULL OR lsah.last_action_date < authorize.date_authorized)
  fields:
  - dimension: translation_key
    sql: ${TABLE}.translation_key
  - dimension: site_id
    type: int
    sql: ${TABLE}.site_id
  - dimension: target_locale_id
    sql: ${TABLE}.target_locale_id
  - dimension: hashcode
    sql: ${TABLE}.hashcode
  - dimension: authorized_to_published_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_published)
  - dimension: authorized_to_published_in_hours_no_weekends
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.authorized_seq_num
  - dimension: authorized_to_assigned_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_assigned)
  - dimension: authorized_to_translated_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_authorized, ${TABLE}.date_translation)
  - dimension: assigned_to_published_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_assigned, ${TABLE}.date_published)
  - dimension: assigned_to_published_in_hours_no_weekends
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.assigned_seq_num
  - dimension: assigned_to_translated_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_assigned, ${TABLE}.date_translation)
  - dimension: translated_to_edited_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_translation, ${TABLE}.date_edited)
  - dimension: edited_to_reviwed_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_edited, ${TABLE}.date_reviewed)
  - dimension: edited_to_published_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_edited, ${TABLE}.date_published)
  - dimension: edited_to_published_in_hours_no_weekends
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.edited_seq_num
  - dimension: reviewed_to_published_in_hours
    type: number
    sql: DATEDIFF(hour, ${TABLE}.date_reviewed, ${TABLE}.date_published)
  - dimension: reviewed_to_published_in_hours_no_weekends
    type: number
    sql: ${TABLE}.published_seq_num - ${TABLE}.reviewed_seq_num
  - dimension: word_count
    sql: ${TABLE}.word_count
  - dimension: request_url
    sql: ${TABLE}.request_url
  - dimension: request_id
    type: int
    sql: ${TABLE}.request_id
  - dimension_group: authorized
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.date_authorized
  - dimension_group: assigned
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.date_assigned
  - dimension_group: translation
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.date_translation
  - dimension_group: published
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow_num
    - dom
    - hod
    sql: ${TABLE}.date_published
  - measure: average_authorized_to_published_in_hours
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${authorized_to_published_in_hours} < 0
  THEN NULL
  ELSE ${authorized_to_published_in_hours}
END
  - measure: average_authorized_to_published_in_hours_no_weekends
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${authorized_to_published_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${authorized_to_published_in_hours_no_weekends}
END
  - measure: average_authorized_to_assigned_in_hours
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${authorized_to_assigned_in_hours} < 0
  THEN NULL
  ELSE ${authorized_to_assigned_in_hours}
END
  - measure: average_assigned_to_published_in_hours
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${assigned_to_published_in_hours} < 0
  THEN NULL
  ELSE ${assigned_to_published_in_hours}
END
  - measure: average_assigned_to_published_in_hours_no_weekends
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${assigned_to_published_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${assigned_to_published_in_hours_no_weekends}
END
  - measure: average_authorized_to_translated_in_hours
    type: average
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${authorized_to_translated_in_hours} < 0
  THEN NULL
  ELSE ${authorized_to_translated_in_hours}
END
  - measure: average_edited_to_published_in_hours
    type: average
    description: Show how long takes review step. Works for simple workflow - when edit step placed before review. This can not be used for all cases.
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${edited_to_published_in_hours} < 0
  THEN NULL
  ELSE ${edited_to_published_in_hours}
END
  - measure: average_edited_to_published_in_hours_no_weekends
    type: average
    description: Show how long takes review step withot weekends. Works for simple workflow - when edit step placed before review. This can not be used for all cases.
    drill_fields:
    - detail*
    sql: CASE
  WHEN ${edited_to_published_in_hours_no_weekends} < 0
  THEN NULL
  ELSE ${edited_to_published_in_hours_no_weekends}
END
  - measure: total_word_count
    type: sum
    drill_fields:
    - detail*
    sql: ${word_count}