# View: dt_incomplete_reviews

**View Name:** dt_incomplete_reviews
**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.Track_Instant_Grat + 3 more`
**File Path:** `views/dt_incomplete_reviews.view.lkml`

## Overview

- **File Size:** 9599 bytes
- **Lines of Code:** 337
- **Dimensions:** 22
- **Measures:** 2
- **Dimension Groups:** 3
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `review_id` | string |
| `product_id` | string |
| `queue_name` | string |
| `escalated_by` | string |
| `account_service_tier` | string |
| `relationship_manager` | string |
| `secondary_relationship_manager` | string |
| `move_note` | string |
| `escalation_reason` | string |
| `instant_grat` | string |
| `upc` | string |
| `release_name` | string |
| `special_instructions` | string |
| `label_id` | string |
| `label_name` | string |
| `assigned_reviewer` | string |
| `owner` | string |
| `product_name` | string |
| `product_primary_artist` | string |
| `genre` | string |
| `subaccount_name` | string |
| `subaccount_id` | string |

## Measures

| Name | Type |
|------|------|
| `BDs_to_escalate` | sum |
| `BDs_since_escalation` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `created_datetime` | time |
| `preorder_date` | time |
| `moved_at_datetime` | 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
    CASE WHEN DAYOFWEEKISO(current_date) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', current_date)))  -- Sat → Fri 12pm
             WHEN DAYOFWEEKISO(current_date) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', current_date)))  -- Sun → Fri 12pm
             ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', current_date))   -- Weekday → 12pm same day
        END AS adj_today,
      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.product_id,
      rq.status,
      rq.queue_name,
      rq.moved_to_queue_by_user_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.special_instructions,
      r.preorder_date,
      v.vendor_id as label_id,
      v.name 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,
      IFF(ig.unique_track_id IS NULL, FALSE, TRUE) as has_instant_grat,
      st.display_name as account_service_tier,
      lm.full_name as relationship_manager,
      slm.full_name as secondary_relationship_manager

      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 intelligence.dbt_prod.orch_app_ar_track_track_physical_joined t
        ON t.release_id = r.release_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_instant_grat ig
        on t.id = ig.unique_track_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,3574)
      -- AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160)
      -- AND rq.queue_name = 'escalation'
      AND rq.status = 'new'
      AND rq.submission_type = 'new'
)

select created_datetime,
GREATEST(
                (DATEDIFF(day,created_datetime,adj_moved)+ 1
                - 2 * FLOOR((DATEDIFF(day,created_datetime,adj_moved)
                + DAYOFWEEKISO(created_datetime) - 1) / 7)
                - CASE WHEN DAYOFWEEKISO(created_datetime) = 7 THEN 1 ELSE 0 END
                - CASE WHEN DAYOFWEEKISO(adj_moved) = 6 THEN 1 ELSE 0 END) - 1, 0)
        as BDs_to_escalate,
moved_at_datetime,
    GREATEST(
        (DATEDIFF(day,adj_moved,adj_today)+ 1
        - 2 * FLOOR((DATEDIFF(day,adj_moved,adj_today)
        + DAYOFWEEKISO(adj_moved) - 1) / 7)
        - CASE WHEN DAYOFWEEKISO(adj_moved) = 7 THEN 1 ELSE 0 END
        - CASE WHEN DAYOFWEEKISO(adj_today) = 6 THEN 1 ELSE 0 END) - 1, 0)
        as BDs_since_escalation,
    review_id,
    status,
    queue_name,
    moved_to_queue_by_user_id,
    i.name as escalated_by,
    account_service_tier,
    assigned_to as relationship_manager_id,
    relationship_manager,
    secondary_relationship_manager,
    move_note,
    escalation_reason,
    submission_type,
    submission_count,
    upc,
    release_name,
    preorder_date,
    special_instructions,
    label_id,
    label_name,
    assigned_reviewer_id,
    assigned_reviewer,
    owner,
    product_name,
    product_id,
    has_instant_grat,
    product_primary_artist,
    genre,
    subaccount_name,
    subaccount_id,

from reviews_submitted rs
LEFT JOIN facts.PROD.identity i
        ON rs.moved_to_queue_by_user_id = i.id

    ;;
```