# View: completion_reasons_separated

**View Name:** completion_reasons_separated
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Genre, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Subaccount, Dbt_Prod.Awal_Tier_Groups + 3 more`
**File Path:** `views/periscope_migration/content_integrity_team_analytics/completion_reasons_separated.view.lkml`

## Overview

- **File Size:** 24463 bytes
- **Lines of Code:** 779
- **Dimensions:** 40
- **Measures:** 8
- **Dimension Groups:** 6
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `instance_id` | string |
| `tier_row_number` | string |
| `review_queue_id` | string |
| `review_actioned` | string |
| `version` | string |
| `completion_note` | string |
| `upc` | string |
| `outside_sla` | string |
| `days_past_sla` | string |
| `queue_name` | string |
| `previous_queue_name` | string |
| `tier` | string |
| `label_name` | string |
| `genre` | string |
| `brand` | string |
| `assigned_reviewer_id` | string |
| `actioned_by` | string |
| `completed_by` | string |
| `queue_moved_by` | string |
| `escalation_reason` | string |
| `multiple_escalations` | string |
| `label_id` | string |
| `all_validation_codes` | string |
| `product_name` | string |
| `product_primary_artist` | string |
| `product_id` | string |
| `relationship_manager` | string |
| `secondary_relationship_manager` | string |
| `assigned_reviewer` | string |
| `submission_type` | string |
| `submission_count` | string |
| `completion_type` | string |
| `completion_reason` | string |
| `move_note` | string |
| `subaccount_name` | string |
| `subaccount_id` | string |
| `special_instructions` | string |
| `owner` | string |
| `status` | string |
| `review_queue_move` | string |

## Measures

| Name | Type |
|------|------|
| `days_left` | sum |
| `count_reviews` | count_distinct |
| `avg_bd_to_first_action` | average |
| `avg_bd_to_approval` | average |
| `avg_bd_to_rejection` | average |
| `avg_bd_to_escalation` | average |
| `avg_bd_escalation_duration` | average |
| `count_reviewers` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `moved_at_datetime` | time |
| `previous_queue_datetime` | time |
| `first_action` | time |
| `completed_datetime` | time |
| `sale_start_date` | time |
| `submitted_time` | time |

## SQL Comments

- Sat → Fri 12pm
- Sun → Fri 12pm
- Weekday → 12pm same day
- rq.queue_name,
- rq.submitted_by_user_id,

## Derived Table

```sql
sql:
       with reviews_submitted as (
            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)

      SELECT
      rq.id as review_queue_id,
      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.status as submission_status,
      -- rq.queue_name,
      rq.product_id,
      rq.submission_type,
      rq.submission_count,
      -- rq.submitted_by_user_id,
      r.upc,
      r.release_name,
      r.sale_start_date,
      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,
      atg.brand_name as brand,
      atg.owner,
      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,
      r.version as version,
      LISTAGG(distinct vh.validation_code, ', ') WITHIN GROUP (ORDER BY vh.validation_code) AS all_validation_codes

      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 "FACTS"."PROD".SERVICE_TIER st
      ON st.uuid = vst.service_tier_uuid
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  atg
      ON v.vendor_id = atg.labelid
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history vh
      ON vh.review_queue_id = rq.id
      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

      WHERE rq._fivetran_deleted = false
      AND (v.label_identifier != 'Test' OR v.label_identifier IS NULL)
      AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160)
      AND rq.queue_name NOT IN ('integrations')
      GROUP BY ALL
      ),

      not_unsubmitted as (
      SELECT distinct
      rs.review_queue_id as review_queue_id,
      us.review_queue_id as unsubmitted_review

      FROM reviews_submitted rs
      LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_CONTENT_REVIEW_CONTENT_REVIEW.UNSUBMIT us
      ON rs.review_queue_id = us.review_queue_id

      WHERE us.review_queue_id is null
      ),

      escalations as (
      SELECT
      review_queue_id,
      row_number() over (partition by review_queue_id order by moved_at_datetime) as queue_move_count,
      case when moved_at_datetime = max(moved_at_datetime) over (partition by review_queue_id) then TRUE else FALSE end as is_last_escalation,
      qmh.id as move_id,
      previous_queue_name,
      queue_name,
      -- moved_to_queue_by_user_id,
      -- i.id,
      i.name as queue_moved_by,
      lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime) as previous_queue_datetime,
      CASE WHEN DAYOFWEEKISO(lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime)) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime))))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime)) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime))))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', lag(moved_at_datetime) over (partition by review_queue_id order by moved_at_datetime)))    -- Weekday → 12pm same day
      END AS adj_previous,
      moved_at_datetime,
      CASE WHEN DAYOFWEEKISO(moved_at_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', moved_at_datetime)))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(moved_at_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', moved_at_datetime)))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', moved_at_datetime))    -- Weekday → 12pm same day
      END AS adj_moved,
      move_note,
      mt.display_name as escalation_reason,
      case when count(review_queue_id) over (partition by review_queue_id) >1 then TRUE else FALSE end as multiple_escalations,

      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".QUEUE_MOVE_HISTORY qmh
      left join facts.PROD.identity i
      on qmh.moved_to_queue_by_user_id = i.id
      left join "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".move_target mt
      on mt.ID = qmh.MOVED_TO_TARGET_ID
      ),

      completed_reviews as (
      SELECT distinct
      cr.review_queue_id,
      -- cr.user_id as completed_by_id,
      i.name as completed_by,
      cr.completion_type,
      cr.completed_datetime,
      CASE WHEN DAYOFWEEKISO(cr.completed_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', cr.completed_datetime)))
      WHEN DAYOFWEEKISO(cr.completed_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', cr.completed_datetime)))
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', cr.completed_datetime))
      END AS adj_completed,
      dr.completion_reason,
      -- dr.completion_reason_agg,
      cr.completion_note,

      FROM (
      SELECT USER_ID,
      REVIEW_QUEUE_ID,
      'approval' AS COMPLETION_TYPE,
      CREATED_DATETIME AS completed_datetime,
      note as completion_note,
      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,
      note as completion_note,
      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
      WHERE _fivetran_deleted=false) cr
      LEFT JOIN facts.PROD.identity i
      ON cr.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 = cr.review_queue_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')
      ),

      business_day_durations as (
      SELECT
      review_queue_id,
      move_id,

      -- COMPLETION straight from SUBMISSION
      CASE WHEN (adj_previous is null and adj_moved is null) and adj_completed is not null
      THEN
      GREATEST(
      (DATEDIFF(day,adj_created,adj_completed)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_created,adj_completed)
      + DAYOFWEEKISO(adj_created) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_created) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0)

      -- QUEUE MOVE FROM SUBMISSION
      WHEN adj_previous is null and queue_name is not null
      THEN
      GREATEST(
      (DATEDIFF(day,adj_created,adj_moved)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_created,adj_moved)
      + DAYOFWEEKISO(adj_created) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_created) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 6 THEN 1 ELSE 0 END) - 1, 0)

      -- QUEUE MOVES BETWEEN QUEUES WHERE COMPLETION IS NOT DONE FROM INITIAL QUEUE
      WHEN adj_previous is not null and adj_moved is not null and (queue_name != 'initial' or adj_completed is null)
      THEN
      GREATEST(
      (DATEDIFF(day,adj_previous,adj_moved)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_previous,adj_moved)
      + DAYOFWEEKISO(adj_previous) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_previous) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 6 THEN 1 ELSE 0 END) - 1, 0)

      -- QUEUE MOVE TO INITIAL AND COMPLETED FROM INITIAL
      WHEN adj_previous is not null and queue_name = 'initial' and adj_completed is not null
      THEN
      GREATEST(
      (DATEDIFF(day,adj_moved,adj_completed)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_moved,adj_completed)
      + DAYOFWEEKISO(adj_moved) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0)

      ELSE NULL END AS bd_fa, -- Time to First Action

      CASE WHEN completion_type = 'approval' AND (adj_previous is null and adj_moved is null)
      THEN
      (GREATEST(
      (DATEDIFF(day,adj_created,adj_completed)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_created,adj_completed)
      + DAYOFWEEKISO(adj_created) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_created) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0))
      ELSE NULL END AS bd_ap, -- Time to Approval

      CASE WHEN completion_type = 'rejection' AND (adj_previous is null and adj_moved is null)
      THEN
      (GREATEST(
      (DATEDIFF(day,adj_created,adj_completed)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_created,adj_completed)
      + DAYOFWEEKISO(adj_created) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_created) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0))
      ELSE NULL END AS bd_rj, -- Time to Rejection

      CASE WHEN coalesce(adj_previous,adj_moved) is not null and queue_name != 'initial'
      THEN
      (GREATEST(
      (DATEDIFF(day,coalesce(adj_previous,adj_created),adj_moved)+ 1
      - 2 * FLOOR((DATEDIFF(day,coalesce(adj_previous,adj_created),adj_moved)
      + DAYOFWEEKISO(coalesce(adj_previous,adj_created)) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(coalesce(adj_previous,adj_created)) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 6 THEN 1 ELSE 0 END) - 1, 0))
      ELSE NULL END AS bd_es, -- Time to Escalation


      CASE WHEN (queue_name != 'initial' and adj_completed is not null)
      THEN
      (GREATEST(
      (DATEDIFF(day,adj_moved,adj_completed)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_moved,adj_completed)
      + DAYOFWEEKISO(adj_moved) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0))

      WHEN previous_queue_name != 'initial'
      THEN
      (GREATEST(
      (DATEDIFF(day,adj_previous,adj_moved)+ 1
      - 2 * FLOOR((DATEDIFF(day,adj_previous,adj_moved)
      + DAYOFWEEKISO(adj_previous) - 1) / 7)
      - CASE WHEN DAYOFWEEKISO(adj_previous) = 7 THEN 1 ELSE 0 END
      - CASE WHEN DAYOFWEEKISO(adj_moved) = 6 THEN 1 ELSE 0 END) - 1, 0))
      ELSE NULL END AS bd_ed, -- Escalation Duration

      from (
      select distinct
      rs.review_queue_id,
      rs.adj_created,
      e.move_id,
      e.previous_queue_name,
      e.queue_name,
      e.adj_previous,
      e.adj_moved,
      cr.adj_completed,
      cr.completion_type,
      from reviews_submitted rs
      left join escalations e
      on rs.review_queue_id = e.review_queue_id
      left join completed_reviews cr
      on rs.review_queue_id = cr.review_queue_id
      and (e.is_last_escalation is null or e.is_last_escalation = TRUE)
      )
      )

      select distinct
      concat(rs.review_queue_id,'_',ifnull(e.queue_move_count,0)) as instance_id,
      rs.*,
      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,
      concat(e.queue_move_count, ' of ', max(e.queue_move_count) over (partition by rs.review_queue_id)) as review_queue_move,
      e.* exclude (review_queue_id),
      cr.* exclude (review_queue_id),
      bd.* exclude (review_queue_id, move_id),
      CASE WHEN bd.bd_fa is not null THEN
      (CASE
      WHEN rs.account_service_tier = 'Tier 2'
      THEN CASE WHEN bd.bd_fa > 3 THEN TRUE ELSE FALSE END
      WHEN rs.account_service_tier = 'Tier 3'
      THEN CASE WHEN bd.bd_fa > 5 THEN TRUE ELSE FALSE END
      WHEN rs.account_service_tier IN ('Basic','Managed Basic')
      THEN CASE WHEN bd.bd_fa > 7 THEN TRUE ELSE FALSE END
      WHEN rs.account_service_tier IN ('Premium Services','Tier 1')
      THEN CASE WHEN bd.bd_fa > 2 THEN TRUE ELSE FALSE END
      ELSE NULL END) else null end AS outside_sla,
      ROUND(CASE
      WHEN rs.account_service_tier = 'Tier 2' THEN 3 - bd.bd_fa
      WHEN rs.account_service_tier = 'Tier 3' THEN 5 - bd.bd_fa
      WHEN rs.account_service_tier IN ('Basic','Managed Basic') THEN 7 - bd.bd_fa
      WHEN rs.account_service_tier IN ('Premium Services','Tier 1') THEN 2 - bd.bd_fa
      ELSE NULL END,0) AS days_left,
      CASE WHEN coalesce(e.moved_at_datetime,cr.completed_datetime) is null then FALSE else TRUE end as review_actioned,
      coalesce(case when e.queue_name != 'initial' then e.queue_moved_by when e.queue_name = 'initial' then coalesce(cr.completed_by,e.queue_moved_by) end, cr.completed_by) as actioned_by,

      from reviews_submitted rs
      inner join not_unsubmitted nu
      on rs.review_queue_id = nu.review_queue_id
      left join escalations e
      on rs.review_queue_id = e.review_queue_id
      left join completed_reviews cr
      on rs.review_queue_id = cr.review_queue_id
      and (e.is_last_escalation is null or e.is_last_escalation = TRUE)
      left join business_day_durations bd
      on rs.review_queue_id = bd.review_queue_id
      and (e.move_id is null or e.move_id = bd.move_id)

      ;;
```