# View: team_volume_speed

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

## Overview

- **File Size:** 27512 bytes
- **Lines of Code:** 719
- **Dimensions:** 28
- **Measures:** 1
- **Dimension Groups:** 7
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `review_id` | string |
| `upc` | string |
| `queue_name` | string |
| `tier` | string |
| `label_name` | string |
| `genre` | string |
| `brand_Name` | string |
| `assigned_reviewer_id` | string |
| `actioned_by` | string |
| `completed_by` | string |
| `escalation_reason` | string |
| `label_id` | 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 |
| `session_progress` | string |

## Measures

| Name | Type |
|------|------|
| `session_duration_min` | average |

## Dimension Groups

| Name | Type |
|------|------|
| `session_open` | time |
| `escalation_session` | time |
| `first_action_session` | time |
| `first_open_session` | time |
| `completion_session` | time |
| `sale_start_date` | time |
| `submitted_time` | time |

## SQL Comments

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

## Derived Table

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

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

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

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

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

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

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

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

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

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

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

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

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

      --   FROM session_durations sd
      -- )

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

    ;;
```