view: review_volume {
  derived_table: {
    sql:
   with full_names as (
    SELECT id,
        concat(f_name, ' ', l_name) as full_name
    FROM orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users
),

      reviews_submitted as (
      SELECT rq.created_datetime,
      CASE WHEN DAYOFWEEKISO(rq.created_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', rq.created_datetime)))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(rq.created_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', rq.created_datetime)))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', rq.created_datetime))   -- Weekday → 12pm same day
      END AS adj_created,
      rq.id as review_id,
      rq.status,
      rq.queue_name,
      rq.product_id,
      rq.locked_by_user_id,
      rq.locked_at_datetime,
      rq.locked_until_datetime,
      rq.moved_to_queue_by_user_id,
      rq.moved_to_target_id,
      rq.move_note,
      mt.display_name as escalation_reason,
      rq.moved_at_datetime,
      CASE WHEN DAYOFWEEKISO(rq.moved_at_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', rq.moved_at_datetime)))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(rq.moved_at_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', rq.moved_at_datetime)))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', rq.moved_at_datetime))    -- Weekday → 12pm same day
      END AS adj_moved,
      rq.submission_type,
      rq.submission_count,
      rq.submitted_by_user_id,
      r.upc,
      r.release_name,
      r.special_instructions,
      v.vendor_id as label_id,
      coalesce(NULLIF(v.name,''),NULLIF(v.company,'')) as label_name,
      v.assigned_reviewer as assigned_reviewer_id,
      ar.full_name as assigned_reviewer,
      v.owner,
      v.assigned_to,
      iff(r.release_id is not NULL, r.release_name, 'DELETED PRODUCT') as product_name,
      a.name as product_primary_artist,
      g.genre,
      s.subaccount_name,
      s.subaccount_id,
      st.display_name as account_service_tier,
      lm.full_name as relationship_manager,
      slm.full_name as secondary_relationship_manager
      -- atg.brand_name,

      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REVIEW_QUEUE rq
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".RELEASES r
      ON rq.PRODUCT_ID = r.RELEASE_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".PROJECT p
      ON r.PROJECT_ID = p.PROJECT_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v
      ON p.VENDOR_ID = v.VENDOR_ID
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre g
      ON r.genre_id = g.genre_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.artist_info a
      ON r.artist_id = a.artist_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.subaccount s
      ON r.subaccount_id = s.subaccount_id
      LEFT JOIN "FACTS"."PROD".VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst
      ON v.vendor_id = vst.vendor_id
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".move_target mt
      ON mt.ID = rq.MOVED_TO_TARGET_ID
      LEFT JOIN "FACTS"."PROD".SERVICE_TIER st
      ON st.uuid = vst.service_tier_uuid
      LEFT JOIN full_names lm
      ON v.assigned_to = lm.id
      LEFT JOIN full_names slm
      ON v.quarterback_label_manager = slm.id
      LEFT JOIN full_names ar
      ON v.assigned_reviewer = ar.id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  atg
      ON v.vendor_id = atg.labelid
      WHERE rq._fivetran_deleted = false
      AND (v.label_identifier != 'Test' OR v.label_identifier IS NULL)
      AND v.assigned_reviewer IN (2903,3754,3753)
      -- 2903 CR, 3753 India, 3754 Vietnam
      AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160)
      AND rq.queue_name NOT IN ('integrations')
      ),

      completed_reviews as (
      SELECT
      rs.*,
      ar.user_id as completed_by_id,
      CASE WHEN rs.status = 'complete' AND (rs.moved_at_datetime IS NULL AND ar.completed_datetime IS NULL) THEN 'unsubmitted' ELSE ar.completion_type END as completion_type,
      ar.completed_datetime,
      CASE WHEN DAYOFWEEKISO(ar.completed_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', ar.completed_datetime)))
                 WHEN DAYOFWEEKISO(ar.completed_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', ar.completed_datetime)))
                 ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', ar.completed_datetime))
        END AS adj_completed,
      i.name as completed_by,
      dr.completion_reason,
      dr.completion_reason_agg,
      vh.validation_code,
      listagg(distinct vh.validation_code, ', ') as all_validation_codes,
      FROM reviews_submitted rs
      INNER JOIN (
          SELECT USER_ID,
          REVIEW_QUEUE_ID,
          'approval' AS COMPLETION_TYPE,
          CREATED_DATETIME AS completed_datetime,
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".APPROVAL
          WHERE _fivetran_deleted=false
          UNION ALL
          SELECT USER_ID,
          REVIEW_QUEUE_ID,
          'rejection' AS COMPLETION_TYPE,
          CREATED_DATETIME AS completed_datetime,
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
          WHERE _fivetran_deleted=false) ar
            ON ar.REVIEW_QUEUE_ID = rs.review_ID
      LEFT JOIN facts.PROD.identity i
            ON ar.user_id = i.id and i.id = rs.locked_by_user_id
      LEFT JOIN (
          SELECT
          his.review_queue_id,
          res.keyword AS completion_reason,
          LISTAGG(distinct res.keyword, ', ')
          WITHIN GROUP (ORDER BY res.keyword)
          OVER (PARTITION BY his.review_queue_id) AS completion_reason_agg
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response res
          LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_category cat
          ON res.category_ID = cat.ID
          LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_history his
          ON his.CANNED_RESPONSE_ID = res.id
      ) dr
        ON dr.review_queue_id = rs.review_id
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history vh
        ON vh.review_queue_id = rs.review_id
      WHERE i.name NOT IN ('Conrad Capalbo','Jay Fidlow','Gayae Manukyan','mbalan@theorchard.com','Malini Balan','piannone@theorchard.com','James Dooley','Troy Denkinger','Stefan Duberg','Gregory Tavarez','ingride ngaku','Arushi Jaiswal','Tristan Morris','Khannah Bint Saliym','Valentina Gilly','Joseph Oladimeji','Benjamin Kao')
      AND i.id NOT IN ('794821a1-048b-4f3d-93dd-213a46362e0a')
      group by all
      ),

      session_durations as (
      with indv_sessions as (
          select distinct cs.review_queue_id,
          cs.actioned_by,
          cs.created_datetime,
          cs.session_open,
          cs.session_close,
          cs.session_duration_min,
          cs.session_progress,
          listagg(cs.session_progress,', ') within group (order by cs.session_open) over (partition by cs.review_queue_id) as full_progress,
          min(case when session_progress LIKE '%Change Queue To Escalation%' OR session_progress LIKE '%Change Queue To Under_Investigation%' THEN session_close END) OVER (partition by cs.review_queue_id) as escalation_session,
          min(case when session_progress LIKE '%Complete Review%' THEN session_close END) OVER (partition by cs.review_queue_id) as completion_session,
          cs.completion_type,
          FIRST_VALUE(cs.actioned_by) OVER (PARTITION BY cs.review_queue_id ORDER BY CASE WHEN cs.session_progress LIKE '%Change Queue To Escalation%' OR cs.session_progress LIKE '%Change Queue To Under_Investigation%' OR cs.session_progress LIKE '%Complete Review%' THEN cs.session_open ELSE NULL END ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_actioned_by,

          from (SELECT
          rmd.review_queue_id,
          rmd.user_id as actioned_by_id,
          coalesce(i.name,fn.full_name) as actioned_by,
          cr.created_datetime,
          CONVERT_TIMEZONE('UTC','America/New_York',DATE_TRUNC('second', TO_TIMESTAMP(rmd.SESSION_START_TIME / 1000))) AS session_open,
          CONVERT_TIMEZONE('UTC','America/New_York',DATE_TRUNC('second', TO_TIMESTAMP(rmd.SESSION_END_TIME / 1000))) AS session_close,
          round(rmd.total_duration_seconds/60,2) as session_duration_min,
          initcap(CASE WHEN label IS NULL THEN 'Closed Window' ELSE label END) AS session_progress,
          cr.completion_type,
          FROM SEGMENT_EVENTS.PROD_FRONTEND_CONTENT.REVIEW_MODE_DURATION rmd
          inner join completed_reviews cr
          on cr.review_id = rmd.review_queue_id
          left JOIN facts.PROD.identity i
          ON rmd.user_id = i.id
          left join full_names fn
          on rmd.user_id = to_varchar(fn.id)
          WHERE round(rmd.total_duration_seconds/60,2) > 0
          and review_queue_id IN (
          SELECT DISTINCT review_queue_id,
          FROM SEGMENT_EVENTS.PROD_FRONTEND_CONTENT.REVIEW_MODE_DURATION
          WHERE label LIKE '%Complete Review%')
          and completion_type not in ('unsubmitted')
          ORDER BY 1,2) cs
          -- group by 1,2,3,4,5,6,7
          order by 1,2)
      select distinct
      review_queue_id,
      first_actioned_by,
      full_progress,
      created_datetime,
      CASE WHEN DAYOFWEEKISO(min(created_datetime) over (partition by review_queue_id)) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', min(created_datetime) over (partition by review_queue_id))))
      WHEN DAYOFWEEKISO(min(created_datetime) over (partition by review_queue_id)) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', min(created_datetime) over (partition by review_queue_id))))
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', min(created_datetime) over (partition by review_queue_id)))
      END AS created_datetime_adj,
      min(session_open) over (partition by review_queue_id) as first_open,
      CASE WHEN DAYOFWEEKISO(min(session_open) over (partition by review_queue_id)) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', min(session_open) over (partition by review_queue_id))))
      WHEN DAYOFWEEKISO(min(session_open) over (partition by review_queue_id)) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', min(session_open) over (partition by review_queue_id))))
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', min(session_open) over (partition by review_queue_id)))
      END AS first_open_adj,
      escalation_session,
      CASE WHEN DAYOFWEEKISO(min(escalation_session) over (partition by review_queue_id)) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', min(escalation_session) over (partition by review_queue_id))))
      WHEN DAYOFWEEKISO(min(escalation_session) over (partition by review_queue_id)) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', min(escalation_session) over (partition by review_queue_id))))
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', min(escalation_session) over (partition by review_queue_id)))
      END AS escalation_session_adj,
      completion_session,
      CASE WHEN DAYOFWEEKISO(min(completion_session) over (partition by review_queue_id)) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', min(completion_session) over (partition by review_queue_id))))
      WHEN DAYOFWEEKISO(min(completion_session) over (partition by review_queue_id)) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', min(completion_session) over (partition by review_queue_id))))
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', min(completion_session) over (partition by review_queue_id)))
      END AS completion_session_adj,
      completion_type,
      SUM(CASE WHEN session_close <= escalation_session THEN session_duration_min END) OVER (PARTITION BY review_queue_id) AS time_to_escalation_min,
      SUM(CASE WHEN session_close <= completion_session THEN session_duration_min END) OVER (PARTITION BY review_queue_id) AS time_to_completion_min,
      SUM(CASE WHEN session_open >= escalation_session THEN session_duration_min end) over (PARTITION BY review_queue_id)  as escalation_duration,
      from indv_sessions
      order by 1,3
      ),

      durations as (
        select
      review_queue_id,
      first_actioned_by,
      full_progress,
      first_open,
      escalation_session,
      completion_session,
      coalesce(time_to_escalation_min,time_to_completion_min) as time_to_first_action,
      case when completion_type = 'approval' and time_to_escalation_min is null
        then time_to_completion_min end as time_to_approval,
      case when completion_type = 'rejection' and time_to_escalation_min is null
        then time_to_completion_min end as time_to_rejection,
      time_to_escalation_min as time_to_escalation,
      escalation_duration as escalation_duration,
      time_to_completion_min as total_duration
      from session_durations
      ),

      business_day_durations as (
              SELECT
            review_queue_id,
            GREATEST(
                (DATEDIFF(day,created_datetime_adj,coalesce(escalation_session_adj,completion_session_adj))+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime_adj,coalesce(escalation_session_adj,completion_session_adj))
                + DAYOFWEEKISO(created_datetime_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(coalesce(escalation_session_adj,completion_session_adj)) = 6 THEN 1 ELSE 0 END) - 1, 0)
            AS ttfa, -- Time to First Action

            CASE WHEN completion_type = 'approval' AND escalation_session_adj is null
            THEN
            (GREATEST(
                (DATEDIFF(day,created_datetime_adj,completion_session_adj)+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime_adj,completion_session_adj)
                + DAYOFWEEKISO(created_datetime_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(completion_session_adj) = 6 THEN 1 ELSE 0 END) - 1, 0)) ELSE NULL END
            AS tta, -- Time to Approval

            CASE WHEN completion_type = 'rejection' AND escalation_session_adj is null
            THEN
            (GREATEST(
                (DATEDIFF(day,created_datetime_adj,completion_session_adj)+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime_adj,completion_session_adj)
                + DAYOFWEEKISO(created_datetime_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(completion_session_adj) = 6 THEN 1 ELSE 0 END) - 1, 0)) ELSE NULL END
            AS ttr, -- Time to Rejection

            CASE WHEN escalation_session_adj is not null THEN
            (GREATEST(
                (DATEDIFF(day,created_datetime_adj,escalation_session_adj)+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime_adj,escalation_session_adj)
                + DAYOFWEEKISO(created_datetime_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(escalation_session_adj) = 6 THEN 1 ELSE 0 END) - 1, 0)) ELSE NULL END
            AS tte, -- Time to Escalation

            CASE WHEN escalation_session_adj is not null THEN
            (GREATEST(
                (DATEDIFF(day,escalation_session_adj,completion_session_adj)+ 1
                - 2 * FLOOR((DATEDIFF(day,escalation_session_adj,completion_session_adj)
                + DAYOFWEEKISO(escalation_session_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(escalation_session_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(completion_session_adj) = 6 THEN 1 ELSE 0 END) - 1, 0)) ELSE NULL END
            AS ed, -- Escalation Duration

            GREATEST(
                (DATEDIFF(day,created_datetime_adj,first_open_adj)+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime_adj,first_open_adj)
                + DAYOFWEEKISO(created_datetime_adj) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime_adj) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(first_open_adj) = 6 THEN 1 ELSE 0 END) - 1, 0)
            AS ttfo, -- Time to First Open

        FROM session_durations sd
      )

      SELECT DISTINCT
      rs.review_id as review_id,
      rs.status as status,
      rs.queue_name as queue_name,
      CASE
      WHEN rs.status = 'complete' AND rs.moved_at_datetime IS NULL AND cr.completed_datetime IS NULL THEN 'unsubmitted'
      ELSE cr.completion_type END as completion_type,
      cr.validation_code,
      cr.all_validation_codes,
      rs.submission_type as submission_type,
      rs.upc as upc,
      CASE rs.account_service_tier
      WHEN 'Premium Services' THEN 1
      WHEN 'Tier 1' THEN 2
      WHEN 'Tier 2' THEN 3
      WHEN 'Tier 3' THEN 4
      WHEN 'Managed Basic' THEN 5
      WHEN 'Basic' THEN 6
      ELSE 7 END as tier_row_number,
      rs.account_service_tier as account_service_tier,
      CASE
      WHEN rs.account_service_tier = 'Tier 2'
      THEN CASE WHEN bdr.ttfa > 3 THEN 'Yes' ELSE 'No' END
      WHEN rs.account_service_tier = 'Tier 3'
      THEN CASE WHEN bdr.ttfa > 5 THEN 'Yes' ELSE 'No' END
      WHEN rs.account_service_tier IN ('Basic','Managed Basic')
      THEN CASE WHEN bdr.ttfa > 7 THEN 'Yes' ELSE 'No' END
      WHEN rs.account_service_tier IN ('Premium Services','Tier 1')
      THEN CASE WHEN bdr.ttfa > 2 THEN 'Yes' ELSE 'No' END
      ELSE NULL END AS outside_sla,
      ROUND(CASE
      WHEN rs.account_service_tier = 'Tier 2' THEN 3 - bdr.ttfa
      WHEN rs.account_service_tier = 'Tier 3' THEN 5 - bdr.ttfa
      WHEN rs.account_service_tier IN ('Basic','Managed Basic') THEN 7 - bdr.ttfa
      WHEN rs.account_service_tier IN ('Premium Services','Tier 1') THEN 2 - bdr.ttfa
      ELSE NULL END,0) AS days_left,
      d.full_progress as full_progress,
      rs.created_datetime as created_datetime,
      d.first_actioned_by,
      d.first_open as first_open,
      d.escalation_session as escalation_session,
      d.completion_session as completion_session,
      coalesce(d.escalation_session,d.completion_session) as first_action_session,
      d.time_to_first_action as time_to_first_action,
      d.time_to_approval as time_to_approval,
      d.time_to_rejection as time_to_rejection,
      d.time_to_escalation as time_to_escalation,
      d.escalation_duration as escalation_duration,
      d.total_duration as total_duration,
      bdr.ttfa as bd_to_first_action,
      bdr.tta as bd_to_approval,
      bdr.ttr as bd_to_rejection,
      bdr.tte as bd_to_escalation,
      bdr.ed as bd_escalation_duration,
      bdr.ttfo as bd_time_to_first_open,
      COALESCE((bdr.tte+bdr.ed),bdr.tta,bdr.ttr) as bd_total_business_days,
      rs.submission_count as submission_count,
      rs.product_id as product_id,
      rs.locked_by_user_id as locked_by_user_id,
      rs.locked_at_datetime as locked_at_datetime,
      rs.locked_until_datetime as locked_until_datetime,
      rs.moved_to_queue_by_user_id as moved_to_queue_by_user_id,
      rs.moved_to_target_id as move_to_target_id,
      rs.move_note as move_note,
      rs.escalation_reason as escalation_reason,
      rs.submitted_by_user_id as submitted_by_user_id,
      rs.release_name as release_name,
      rs.special_instructions as special_instructions,
      rs.label_id as label_id,
      rs.label_name as label_name,
      rs.assigned_reviewer as assigned_reviewer,
      rs.assigned_reviewer_id as assigned_reviewer_id,
      rs.owner as owner,
      rs.assigned_to as assigned_to,
      rs.product_name as product_name,
      rs.product_primary_artist as product_primary_artist,
      rs.genre as genre,
      rs.subaccount_name as subaccount_name,
      rs.subaccount_id as subaccount_id,
      rs.relationship_manager as relationship_manager,
      rs.secondary_relationship_manager as secondary_relationship_manager,
      cr.completed_by_id as completed_by_id,
      cr.completed_by as completed_by,
      cr.completion_reason as completion_reason,
      cr.completion_reason_agg as completion_reason_combined
      FROM reviews_submitted rs
      LEFT JOIN completed_reviews cr
      ON rs.review_id = cr.review_id
      LEFT JOIN durations d
      ON rs.review_id = d.review_queue_id
      LEFT JOIN business_day_durations bdr
      ON rs.review_id = bdr.review_queue_id
;;
  }

  dimension: tier_row_number{
    label: "Tier Row Number"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.tier_row_number ;;
    hidden: no
  }

  dimension: review_id{
    label: "Review ID"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.review_id ;;
  }

  dimension: upc{
    label: "UPC"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.upc;;
  }

  dimension_group: created_date {
    label: "Submitted"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.created_datetime;;
  }

  dimension_group: first_action_session {
    label: "First Action"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.first_action_session;;
  }

  dimension: outside_sla{
    label: "Outside SLA"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.outside_sla;;
  }

  measure: days_left{
    label: "Days Left"
    view_label: "Reviews"
    type: sum
    sql: ${TABLE}.days_left;;
  }

  dimension: days_past_sla{
    label: "Days Past SLA"
    view_label: "Reviews"
    type: string
    sql: CASE
          WHEN ${TABLE}.days_left = -1 THEN '1 Day'
          WHEN ${TABLE}.days_left = -2 THEN '2 Days'
          WHEN ${TABLE}.days_left = -3 THEN '3 Days'
          WHEN ${TABLE}.days_left < -3 THEN 'Over 3 Days'
          ELSE NULL END;;
  }

  dimension: queue_name{
    label: "Queue Name"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.queue_name;;
  }

  dimension: tier{
    label: "Account Service Tier"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.account_service_tier;;
  }

  dimension: label_name{
    label: "Label Name"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.label_name;;
  }

  dimension: genre{
    label: "Genre"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.genre;;
  }

  dimension: brand_Name{
    label: "Brand Name"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.brand_name;;
  }

  dimension: assigned_reviewer_id{
    label: "Assigned Reviewer ID"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.assigned_reviewer_id;;
  }

  dimension: completed_by{
    label: "Completed By"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.completed_by;;
  }

  dimension: escalation_reason{
    label: "Escalation Reason"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.escalation_reason;;
  }

  dimension: label_id{
    label: "Label ID"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.label_id;;
  }

  dimension: product_name{
    label: "Product Name"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.product_name;;
  }

  dimension: product_primary_artist{
    label: "Product Primary Artist"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.product_primary_artist;;
  }

  dimension: product_id{
    label: "Product ID"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.product_id;;
  }

  dimension: relationship_manager{
    label: "Relationship Manager"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.relationship_manager;;
  }

  dimension: secondary_relationship_manager{
    label: "Secondary Relationship Manager"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.secondary_relationship_manager;;
  }

  dimension: assigned_reviewer{
    label: "Assigned Reviewer"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.assigned_reviewer;;
  }

  dimension: submission_type{
    label: "Submission Type"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.submission_type;;
  }

  dimension_group: submitted_time {
    label: "Submitted"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.created_datetime;;
  }

  dimension: submission_count{
    label: "Submission Count"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.submission_count;;
  }

  dimension: completion_type{
    label: "Completion Type"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.completion_type;;
  }

  dimension: completion_reason{
    label: "Completion Reason"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.completion_reason;;
  }

  dimension: completion_reason_combined{
    label: "Completion Reason Combined"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.completion_reason_combined;;
  }

  dimension: move_note{
    label: "Move Note"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.move_note;;
  }

  dimension: subaccount_name{
    label: "Sub-Account Name"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.subaccount_name;;
  }

  dimension: subaccount_id{
    label: "Sub-Account ID"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.subaccount_id;;
  }

  dimension: special_instructions{
    label: "Special Instructions"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.special_instructions;;
  }

  dimension: owner{
    label: "Owner"
    view_label: "Metadata"
    type: string
    sql: ${TABLE}.owner;;
  }

  measure: avg_time_to_first_action{
    label: "Average Time to First Action"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.time_to_first_action;;
  }

  measure: avg_bd_to_first_action{
    label: "Average BDs to First Action"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.bd_to_first_action;;
  }

  measure: avg_time_to_approval{
    label: "Average Time to Approval"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.time_to_approval;;
  }

  measure: avg_bd_to_approval{
    label: "Average BDs to Approval"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.bd_to_approval;;
  }

  measure: avg_time_to_rejection{
    label: "Average Time to Rejection"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.time_to_rejection;;
  }

  measure: avg_bd_to_rejection{
    label: "Average BDs to Rejection"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.bd_to_rejection;;
  }

  measure: avg_time_to_escalation{
    label: "Average Time to Escalation"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.time_to_escalation;;
  }

  measure: avg_bd_to_escalation{
    label: "Average BDs to Escalation"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.bd_to_escalation;;
  }

  measure: avg_escalation_duration{
    label: "Average Time Escalation Duration"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.escalation_duration;;
  }

  measure: avg_bd_escalation_duration{
    label: "Average BDs Escalation Duration"
    view_label: "Reviews"
    type: average
    sql: ${TABLE}.bd_escalation_duration;;
  }

  dimension: full_progress{
    label: "Full Progress"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.full_progress;;
  }

  dimension_group: first_open {
    label: "First Open"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.first_open;;
  }

  measure: count_reviews{
    label: "Count of Reviews"
    view_label: "Reviews"
    type: count_distinct
    sql: ${TABLE}.review_id;;
  }

  measure: count_products{
    label: "Count of Products"
    view_label: "Reviews"
    type: count_distinct
    sql: ${TABLE}.upc;;
  }

  dimension: status{
    label: "Review Status"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.status;;
  }

}