# View: content_review_sla

**View Name:** content_review_sla
**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, Art_Relations_Prod_Art_Relations_Log.Vendor_Log + 3 more`
**File Path:** `views/periscope_migration/content_integrity_team_analytics/content_review_sla.view.lkml`

## Overview

- **File Size:** 41862 bytes
- **Lines of Code:** 1232
- **Dimensions:** 54
- **Measures:** 7
- **Dimension Groups:** 9
- **Filters:** 1

## Comments & Notes

- dimension: review_contributor{
- label: "Review Contributor"
- view_label: "Reviews"

## Dimensions

| Name | Type |
|------|------|
| `instance_id` | string |
| `tier_row_number` | string |
| `review_queue_id` | string |
| `review_actioned` | string |
| `version` | string |
| `completion_note` | string |
| `upc` | string |
| `product_link` | string |
| `escalated_to` | string |
| `product_format` | string |
| `label_type` | string |
| `global_mktg_highlights` | string |
| `is_active` | yesno |
| `outside_sla` | string |
| `business_days_left` | number |
| `days_past_sla` | string |
| `queue_name` | string |
| `previous_queue_name` | string |
| `tier` | string |
| `label_tier_upon_action` | 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` | number |
| `completion_type` | string |
| `completion_reason_combined` | string |
| `move_note` | string |
| `subaccount_name` | string |
| `subaccount_id` | string |
| `special_instructions` | string |
| `label_tier_upon_submission` | string |
| `owner` | string |
| `status` | string |
| `review_queue_move` | string |
| `bulk_session_ids` | string |
| `days_from_last_rejection` | number |
| `label_tier_changed` | yesno |
| `assigned_reviewer_changed` | yesno |
| `itunes_ticket` | yesno |

## Measures

| Name | Type |
|------|------|
| `count_label_id` | count_distinct |
| `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 |
|------|------|
| `label_created_date` | time |
| `preorder_date` | time |
| `moved_at_datetime` | time |
| `previous_queue_datetime` | time |
| `first_action` | time |
| `completed_datetime` | time |
| `sale_start_date` | time |
| `submitted_time` | time |
| `adj_ar_date_of_change` | time |

## Filters

- `contributor_search`

## SQL Comments

- Sat → Fri 12pm
- Sun → Fri 12pm
- Weekday → 12pm same day
- Sat → Fri 12pm
- Sun → Fri 12pm

## 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.product_id,
    rq.submission_type,
    rq.submission_count,
    mt.full_name as escalated_to,
    r.preorder_date,
    r.format,
    r.upc,
    r.product_code,
    (CASE WHEN r.release_status = 'in_content' AND r.deletions = 'N' AND r.not_for_distribution = 'N'  THEN TRUE ELSE FALSE END) AS is_active,
    r.release_name,
    r.sale_start_date,
    r.special_instructions,
    r.release_status,
    v.vendor_id as label_id,
    coalesce(NULLIF(v.name,''),NULLIF(v.company,'')) as label_name,
    v.assigned_reviewer as assigned_reviewer_id,
    v.label_identifier,
    v.date_created as label_created_date,
    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,
    rsd.global_mktg_highlights,
    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 INTELLIGENCE.DBT_PROD.RELEASE_SCHEDULE_DASHBOARD rsd
    ON rsd.release_id = r.release_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
    LEFT JOIN full_names mt
    ON rq.moved_to_orchadmin_user_id = mt.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,
    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,
    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_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
    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')
),

assigned_reviewer_change as (
    WITH raw_logs AS (
        SELECT
            vl.log_timestamp,
            vl.vendor_id,
            vl.last_modified_by,
            vl.assigned_reviewer
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS_LOG.VENDOR_LOG vl
        WHERE vl._fivetran_deleted = FALSE
          AND vl.log_timestamp > '2023-07-31 20:43:09'::TIMESTAMP_NTZ
    ),

    -- 1. Condense history down to just the distinct timestamps
    timestamp_states AS (
        SELECT
            vendor_id,
            log_timestamp,
            MAX(assigned_reviewer) AS grouped_reviewer
        FROM raw_logs
        GROUP BY vendor_id, log_timestamp
    ),

    -- 2. Grab the reviewer from the strictly older timestamp
    previous_states AS (
        SELECT
            vendor_id,
            log_timestamp,
            LAG(grouped_reviewer) OVER (PARTITION BY vendor_id ORDER BY log_timestamp ASC) AS prev_reviewer
        FROM timestamp_states
    ),

    -- 3. Run the pattern match with your new custom sort logic
    reviewer_changelog AS (
        SELECT
            mr.*,
            ou.f_name,
            ou.l_name
        FROM (
            -- Join our "previous state" helper column back to the raw logs
            SELECT
                r.log_timestamp,
                r.vendor_id,
                r.last_modified_by,
                r.assigned_reviewer,
                p.prev_reviewer
            FROM raw_logs r
            LEFT JOIN previous_states p
                ON r.vendor_id = p.vendor_id
                AND r.log_timestamp = p.log_timestamp
        ) x
        MATCH_RECOGNIZE(
            PARTITION BY x.vendor_id
            ORDER BY
                x.log_timestamp ASC,
                -- TIE BREAKER: If tied, put the row that matches the previous state first (0), and the changed row second (1)
                CASE WHEN x.assigned_reviewer = x.prev_reviewer THEN 0 ELSE 1 END ASC
            MEASURES
                LAST(log_timestamp) AS updated_log_timestamp,
                LAST(last_modified_by) AS updated_by_id,
                FIRST(assigned_reviewer) AS previous_assigned_reviewer_id,
                LAST(assigned_reviewer) AS updated_assigned_reviewer_id
            ONE ROW PER MATCH
            AFTER MATCH SKIP TO NEXT ROW
            PATTERN(source updated_source)
            DEFINE
                source AS TRUE,
                updated_source AS assigned_reviewer <> LAG(assigned_reviewer)
        ) mr
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users ou
            ON mr.updated_assigned_reviewer_id = ou.id
    )

SELECT
    TO_TIMESTAMP_NTZ(arc.updated_log_timestamp) AS Date_of_Change,
    CASE WHEN DAYOFWEEKISO(arc.UPDATED_LOG_TIMESTAMP) = 6 THEN TO_TIMESTAMP_NTZ(DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', arc.UPDATED_LOG_TIMESTAMP))))  -- Sat → Fri 12pm
    WHEN DAYOFWEEKISO(arc.UPDATED_LOG_TIMESTAMP) = 7 THEN TO_TIMESTAMP_NTZ(DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', arc.UPDATED_LOG_TIMESTAMP))))  -- Sun → Fri 12pm
    ELSE TO_TIMESTAMP_NTZ(DATEADD(hour, 12, DATE_TRUNC('DAY', arc.UPDATED_LOG_TIMESTAMP)))   -- Weekday → 12pm same day
    END AS adj_ar_date_of_change,
    CONCAT(u_by.f_name, ' ', u_by.l_name) AS UPDATED_BY,
    CONCAT(u_prev.f_name, ' ', u_prev.l_name) AS PREVIOUS_ASSIGNED_REVIEWER,
    CONCAT(arc.f_name, ' ', arc.l_name) AS UPDATED_ASSIGNED_REVIEWER,
    arc.updated_assigned_reviewer_id as updated_assigned_reviewer_id,
    arc."X.VENDOR_ID" AS Account_ID,
FROM reviewer_changelog arc
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users u_by
    ON arc.updated_by_id = u_by.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users u_prev
    ON arc.previous_assigned_reviewer_id = u_prev.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor v
    ON arc."X.VENDOR_ID" = v.vendor_id
WHERE v.status = 'signed'
  AND (v.label_identifier != 'Test' OR v.label_identifier IS NULL)
ORDER BY Date_of_Change DESC
),

business_day_durations as (
    SELECT
    review_queue_id,
    move_id,
    adj_ar_date_of_change,

    -- RULE 1: 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,coalesce(adj_ar_date_of_change,adj_created),adj_completed)+ 1
    - 2 * FLOOR((DATEDIFF(day,coalesce(adj_ar_date_of_change,adj_created),adj_completed)
    + DAYOFWEEKISO(coalesce(adj_ar_date_of_change,adj_created)) - 1) / 7)
    - CASE WHEN DAYOFWEEKISO(coalesce(adj_ar_date_of_change,adj_created)) = 7 THEN 1 ELSE 0 END
    - CASE WHEN DAYOFWEEKISO(adj_completed) = 6 THEN 1 ELSE 0 END) - 1, 0)

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

    -- RULE 3: QUEUE MOVED BACK TO INITIAL AND COMPLETED DIRECTLY FROM THERE
    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)

  -- RULE 4: QUEUE MOVES THAT ARE NOT FROM UNDER INVESTIGATION
    WHEN previous_queue_name in ('initial', 'escalation')
    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 MOVES FROM UNDER INVESTIGATION
    WHEN previous_queue_name = 'under_investigation'
    THEN
    GREATEST(
    (DATEDIFF(day,adj_moved,adj_moved)+ 1
    - 2 * FLOOR((DATEDIFF(day,adj_moved,adj_moved)
    + DAYOFWEEKISO(adj_moved) - 1) / 7)
    - CASE WHEN DAYOFWEEKISO(adj_moved) = 7 THEN 1 ELSE 0 END
    - CASE WHEN DAYOFWEEKISO(adj_moved) = 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,coalesce(adj_ar_date_of_change,adj_created),adj_completed)+ 1
    - 2 * FLOOR((DATEDIFF(day,coalesce(adj_ar_date_of_change,adj_created),adj_completed)
    + DAYOFWEEKISO(coalesce(adj_ar_date_of_change,adj_created)) - 1) / 7)
    - CASE WHEN DAYOFWEEKISO(coalesce(adj_ar_date_of_change,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,coalesce(adj_ar_date_of_change,adj_created),adj_completed)+ 1
    - 2 * FLOOR((DATEDIFF(day,coalesce(adj_ar_date_of_change,adj_created),adj_completed)
    + DAYOFWEEKISO(coalesce(adj_ar_date_of_change,adj_created)) - 1) / 7)
    - CASE WHEN DAYOFWEEKISO(coalesce(adj_ar_date_of_change,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,coalesce(adj_ar_date_of_change,adj_created)),adj_moved)+ 1
    - 2 * FLOOR((DATEDIFF(day,coalesce(adj_previous,coalesce(adj_ar_date_of_change,adj_created)),adj_moved)
    + DAYOFWEEKISO(coalesce(adj_previous,coalesce(adj_ar_date_of_change,adj_created))) - 1) / 7)
    - CASE WHEN DAYOFWEEKISO(coalesce(adj_previous,coalesce(adj_ar_date_of_change,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,
            arc.adj_ar_date_of_change,
        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)
        left join assigned_reviewer_change arc
            on rs.label_id =arc.account_id
                and rs.assigned_reviewer_id = updated_assigned_reviewer_id
                and rs.adj_created <= arc.adj_ar_date_of_change
                and cr.adj_completed >= arc.adj_ar_date_of_change
        )
),

bulk_session_ids as (
    SELECT distinct bsip.product_code, LISTAGG(DISTINCT BS.BULK_SESSION_ID, ', ') over (partition by bsip.product_code) as bulk_session_ids,
    FROM ORCHARD_APP_REPORTING_V2.PROD_OWS_PRODUCT_STAGING_OWS_PRODUCT_STAGING.BULK_SESSIONS bs
    JOIN ORCHARD_APP_REPORTING_V2.PROD_OWS_PRODUCT_STAGING_OWS_PRODUCT_STAGING.BULK_SESSION_INGESTIONS  bsi
        ON bsi.bulk_session_id = BS.BULK_SESSION_ID
    JOIN ORCHARD_APP_REPORTING_V2.PROD_OWS_PRODUCT_STAGING_OWS_PRODUCT_STAGING.BULK_SESSION_INGESTION_PRODUCTS bsip
        ON bsip.bulk_session_ingestion_id = bsi.bulk_session_ingestion_id
),

tier_changes as (
    WITH raw_table AS (
    SELECT DISTINCT
        CASE
            WHEN COUNT(DISTINCT t.display_name) OVER (
                PARTITION BY COALESCE(
                    RECORD_CONTENT:event:start:keys:Vendor[0]:id:I64::INT,
                    RECORD_CONTENT:event:start:keys:Vendor[1]:id:I64::INT
                )
            ) > 1 THEN TRUE
            ELSE FALSE
        END AS label_change,
        t.display_name AS label_tier,
        RECORD_CONTENT:event:end:keys:ServiceTier[0]:uuid:S::STRING AS service_tier_uuid,
        COALESCE(
            RECORD_CONTENT:event:start:keys:Vendor[0]:id:I64::INT,
            RECORD_CONTENT:event:start:keys:Vendor[1]:id:I64::INT
        ) AS vendor_id,
        RECORD_CONTENT:metadata:txStartTime:TZDT::TIMESTAMP_TZ AS txStartTime
    FROM FACTS.PROD.CDC__MUSIC_GRAPH_V5__VENDOR_IN_SERVICE_TIER tc
    LEFT JOIN FACTS.PROD.SERVICE_TIER t
        ON tc.RECORD_CONTENT:event:end:keys:ServiceTier[0]:uuid:S::STRING = t.uuid
),
ranked_times AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY vendor_id ORDER BY txStartTime ASC) AS time_rank
    FROM raw_table
    WHERE label_change = TRUE
),
with_prev_status AS (
    SELECT
        curr.vendor_id,
        curr.txStartTime,
        curr.label_tier,
        CASE WHEN prev.label_tier IS NOT NULL THEN TRUE ELSE FALSE END AS existed_in_prev_timestamp
    FROM ranked_times curr
    LEFT JOIN ranked_times prev
        ON curr.vendor_id = prev.vendor_id
        AND curr.time_rank = prev.time_rank + 1
        AND curr.label_tier = prev.label_tier
),
with_history AS (
    SELECT DISTINCT
        vendor_id AS label_id,
        txStartTime,
        LISTAGG(label_tier, ' to ') WITHIN GROUP (
            ORDER BY CASE WHEN existed_in_prev_timestamp THEN 0 ELSE 1 END ASC
        ) OVER (PARTITION BY vendor_id, txStartTime) AS tier_change_history
    FROM with_prev_status
    QUALIFY COUNT(DISTINCT label_tier) OVER (PARTITION BY vendor_id, txStartTime) > 1
),
earliest_tiers AS (
    SELECT
        label_id,
        SPLIT_PART(tier_change_history, ' to ', 1) AS earliest_from_tier
    FROM with_history
    QUALIFY ROW_NUMBER() OVER (PARTITION BY label_id ORDER BY txStartTime ASC) = 1
)
SELECT
    wh.label_id,
    wh.txStartTime,
    wh.tier_change_history,
    et.earliest_from_tier
FROM with_history wh
LEFT JOIN earliest_tiers et
    ON wh.label_id = et.label_id
ORDER BY wh.txStartTime DESC
),

tier_at_submission as (
    select
        rs.review_queue_id,
        e.move_id,
        COALESCE(
            split_part(tc_match.tier_change_history, ' to ', 2),
            et.earliest_from_tier,
            rs.account_service_tier
        ) as active_tier
    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)
    left join tier_changes tc_match
        on tc_match.label_id = rs.label_id
        and tc_match.txstarttime <= rs.created_datetime
    left join (
        select label_id, earliest_from_tier
        from tier_changes
        qualify ROW_NUMBER() OVER (PARTITION BY label_id ORDER BY txstarttime ASC) = 1
    ) et
        on et.label_id = rs.label_id
    qualify ROW_NUMBER() OVER (
        PARTITION BY rs.review_queue_id, e.move_id
        ORDER BY tc_match.txstarttime DESC NULLS LAST
    ) = 1
),

itunes_tickets as (
    SELECT DISTINCT
        facts_prod_itunes_tickets.contentupc  AS upc
    FROM facts.prod.itunes_tickets  AS facts_prod_itunes_tickets
    LEFT JOIN facts.prod.itunes_ticket_defect_codes  AS facts_prod_itunes_ticket_defect_codes ON (TO_CHAR(TO_DATE(facts_prod_itunes_tickets.file_date ), 'YYYY-MM-DD')) = (TO_CHAR(TO_DATE(facts_prod_itunes_ticket_defect_codes.file_date ), 'YYYY-MM-DD'))
          AND facts_prod_itunes_tickets.ticketid = facts_prod_itunes_ticket_defect_codes.ticketid
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases ON (TRY_TO_NUMERIC(COALESCE(facts_prod_itunes_tickets.contentupc, facts_prod_itunes_tickets.contentvendorid))) = orch_app_ar_releases.upc
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info ON orch_app_ar_artist_info.artist_id = orch_app_ar_releases.artist_id
    LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor ON orch_app_ar_artist_info.vendor_id = orch_app_ar_vendor.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager ON orch_app_ar_vendor.assigned_to = label_manager.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS assigned_reviewer ON orch_app_ar_vendor.assigned_reviewer = assigned_reviewer.id
    WHERE LENGTH(facts_prod_itunes_ticket_defect_codes.defect_code ) <> 0 AND ((COALESCE( (assigned_reviewer.f_name||' '||assigned_reviewer.l_name) , '') != 'SME Content') AND ((COALESCE( (label_manager.f_name||' '||label_manager.l_name) , '') != 'SME Content') AND (COALESCE( (label_manager.f_name||' '||label_manager.l_name) , '') != 'AA No Closer'))) AND (facts_prod_itunes_ticket_defect_codes.defect_code ) IS NOT NULL AND facts_prod_itunes_tickets.contentupc is not null
    GROUP BY ALL
    )


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 LAG(cr.completion_type, 1) OVER (PARTITION BY rs.upc ORDER BY rs.created_datetime asc, e.queue_move_count asc) = 'rejection'
    THEN CAST(
        DATEDIFF(
            second,
            LAG(cr.completed_datetime, 1) OVER (PARTITION BY rs.upc ORDER BY rs.created_datetime asc, e.queue_move_count asc),
            rs.created_datetime
        ) / 86400.0 AS DECIMAL(10, 2)
    )
END AS days_from_last_rejection,

CASE WHEN coalesce(e.moved_at_datetime, cr.completed_datetime) is not null THEN ta.active_tier END as label_tier_upon_submission,

CASE WHEN bd.bd_fa is not null THEN
    (CASE
        WHEN ta.active_tier = 'Tier 2' THEN CASE WHEN bd.bd_fa > 3 THEN TRUE ELSE FALSE END
        WHEN ta.active_tier = 'Tier 3' THEN CASE WHEN bd.bd_fa > 5 THEN TRUE ELSE FALSE END
        WHEN ta.active_tier IN ('Basic','Managed Basic') THEN CASE WHEN bd.bd_fa > 7 THEN TRUE ELSE FALSE END
        WHEN ta.active_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,

CASE
    WHEN COALESCE(e.moved_at_datetime, cr.completed_datetime) IS NULL
    THEN (
        (DATEDIFF(day, CURRENT_DATE, DATEADD(day, CASE WHEN ta.active_tier = 'Tier 2' THEN 3 WHEN ta.active_tier = 'Tier 3' THEN 5 WHEN ta.active_tier IN ('Basic','Managed Basic') THEN 7 WHEN ta.active_tier IN ('Premium Services','Tier 1') THEN 2 ELSE NULL END, DATE(rs.created_datetime))) + 1
        - 2 * FLOOR((DATEDIFF(day, CURRENT_DATE, DATEADD(day, CASE WHEN ta.active_tier = 'Tier 2' THEN 3 WHEN ta.active_tier = 'Tier 3' THEN 5 WHEN ta.active_tier IN ('Basic','Managed Basic') THEN 7 WHEN ta.active_tier IN ('Premium Services','Tier 1') THEN 2 ELSE NULL END, DATE(rs.created_datetime)))
        + DAYOFWEEKISO(CURRENT_DATE) - 1) / 7)
        - CASE WHEN DAYOFWEEKISO(CURRENT_DATE) = 7 THEN 1 ELSE 0 END
        - CASE WHEN DAYOFWEEKISO(DATEADD(day, CASE WHEN ta.active_tier = 'Tier 2' THEN 3 WHEN ta.active_tier = 'Tier 3' THEN 5 WHEN ta.active_tier IN ('Basic','Managed Basic') THEN 7 WHEN ta.active_tier IN ('Premium Services','Tier 1') THEN 2 ELSE NULL END, DATE(rs.created_datetime))) = 6 THEN 1 ELSE 0 END) - 1
    )
    ELSE NULL
END AS business_days_left,

ROUND(CASE
    WHEN ta.active_tier = 'Tier 2' THEN 3 - bd.bd_fa
    WHEN ta.active_tier = 'Tier 3' THEN 5 - bd.bd_fa
    WHEN ta.active_tier IN ('Basic','Managed Basic') THEN 7 - bd.bd_fa
    WHEN ta.active_tier IN ('Premium Services','Tier 1') THEN 2 - bd.bd_fa
    ELSE NULL
END, 0) days_past_sla,


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,
BS.BULK_SESSION_IDS as bulk_session_ids,
case when it.upc is not null then true else false end as itunes_ticket

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)
left join bulk_session_ids bs
  on to_varchar(bs.product_code) = to_varchar(rs.product_code)
left join tier_at_submission ta
    on ta.review_queue_id = rs.review_queue_id
    and (e.move_id is null or e.move_id = ta.move_id)
left join itunes_tickets it
    on to_varchar(it.upc) = to_varchar(rs.upc)
;;
```