# View: validation_codes

**View Name:** validation_codes
**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/validation_codes.view.lkml`

## Overview

- **File Size:** 23651 bytes
- **Lines of Code:** 772
- **Dimensions:** 40
- **Measures:** 7
- **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 |
| `validation_code` | 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_combined` | 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 |

## 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,
    vh.validation_code

    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)

;;
```