# View: cr_team_members

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

## Overview

- **File Size:** 35427 bytes
- **Lines of Code:** 881
- **Dimensions:** 11
- **Measures:** 2
- **Dimension Groups:** 3
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `actioned_by` | string |
| `action_type` | string |
| `assigned_reviewer` | string |
| `upc` | string |
| `review_queue_id` | string |
| `account_service_tier` | string |
| `label_id` | string |
| `brand` | string |
| `genre` | string |
| `bulk_session_ids` | string |
| `outside_sla` | yesno |

## Measures

| Name | Type |
|------|------|
| `bds_to_action` | average |
| `action_count` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `action_date` | time |
| `created_datetime` | time |
| `sale_start_date` | time |

## SQL Comments

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

## Derived Table

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

      select
      concat(review_queue_id,'_',ifnull(queue_move_count,0),'_Escalation') as instance_id,
      queue_moved_by as actioned_by,
      case when queue_moved_by is not null then TRUE else FALSE end as was_escalated,
      'Escalation' as action_type,
      assigned_reviewer,
      upc,
      moved_at_datetime as action_date,
      review_queue_id,
      created_datetime,
      sale_start_date,
      account_service_tier,
      label_id,
      brand,
      genre,
      bulk_session_ids,
      bd_es as bds_to_action,
      outside_sla
      from content_review_sla
      where queue_moved_by is not null
      and queue_name in ('escalation','under_investigation')

      union all

      select
      concat(review_queue_id,'_',ifnull(queue_move_count,0),'_Approval') as instance_id,
      completed_by as actioned_by,
      case when queue_moved_by is not null then TRUE else FALSE end as was_escalated,
      'Approval' as action_type,
      assigned_reviewer,
      upc,
      completed_datetime as action_date,
      review_queue_id,
      created_datetime,
      sale_start_date,
      account_service_tier,
      label_id,
      brand,
      genre,
      bulk_session_ids,
      bd_ap as bds_to_action,
      outside_sla
      from content_review_sla
      where completed_by is not null
      and completion_type = 'approval'
      and queue_moved_by is null

      union all

      select
      concat(review_queue_id,'_',ifnull(queue_move_count,0),'_Rejection') as instance_id,
      completed_by as actioned_by,
      case when queue_moved_by is not null then TRUE else FALSE end as was_escalated,
      'Rejection' as action_type,
      assigned_reviewer,
      upc,
      completed_datetime as action_date,
      review_queue_id,
      created_datetime,
      sale_start_date,
      account_service_tier,
      label_id,
      brand,
      genre,
      bulk_session_ids,
      bd_rj as bds_to_action,
      outside_sla
      from content_review_sla
      where completed_by is not null
      and completion_type = 'rejection'
      and queue_moved_by is null

      union all

      select
      concat(review_queue_id,'_',ifnull(queue_move_count,0),'_Approval') as instance_id,
      completed_by as actioned_by,
      case when queue_moved_by is not null then TRUE else FALSE end as was_escalated,
      'Approval' as action_type,
      assigned_reviewer,
      upc,
      completed_datetime as action_date,
      review_queue_id,
      created_datetime,
      sale_start_date,
      account_service_tier,
      label_id,
      brand,
      genre,
      bulk_session_ids,
      bd_ed as bds_to_action,
      outside_sla
      from content_review_sla
      where completed_by is not null
      and completion_type = 'approval'
      and queue_moved_by is not null

      union all

      select
      concat(review_queue_id,'_',ifnull(queue_move_count,0),'_Rejection') as instance_id,
      completed_by as actioned_by,
      case when queue_moved_by is not null then TRUE else FALSE end as was_escalated,
      'Rejection' as action_type,
      assigned_reviewer,
      upc,
      completed_datetime as action_date,
      review_queue_id,
      created_datetime,
      sale_start_date,
      account_service_tier,
      label_id,
      brand,
      genre,
      bulk_session_ids,
      bd_ed as bds_to_action,
      outside_sla
      from content_review_sla
      where completed_by is not null
      and completion_type = 'rejection'
      and queue_moved_by is not null
      ;;
```