# View: queue_move_history_escalations

**View Name:** queue_move_history_escalations
**Table Source:** `Derived from: Cr_Completed_Reviews_With_Vendor_Data`
**File Path:** `views/periscope_migration/review_product_history/queue_move_history_escalations.view.lkml`

## Overview

- **File Size:** 5327 bytes
- **Lines of Code:** 187
- **Dimensions:** 10
- **Measures:** 0
- **Dimension Groups:** 3
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |
| `label_id` | string |
| `label_name` | string |
| `completion_type` | string |
| `current_queue` | string |
| `previous_queue` | string |
| `moved_by` | string |
| `escalation_type` | string |
| `escalated_to` | string |
| `move_note` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `submission_date` | time |
| `review_completed_date` | time |
| `date_moved` | time |

## Derived Table

```sql
sql:
    with cr_completed_reviews_with_vendor_data as (
    SELECT *
      FROM(
      SELECT
        r.UPC,
        rq.ID,
        rq.PRODUCT_ID,
        rq.CREATED_DATETIME AS REVIEW_CREATED_DATETIME,
        rq.STATUS,
        rq.QUEUE_NAME,
        cr.COMPLETION_TYPE,
        cr.REVIEW_COMPLETED_DATETIME,
        cr.USER_ID,
        v.VENDOR_ID,
        v.name as label_name,
        s.SUBACCOUNT_ID,
        v.OWNER,
        st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER,
        rq.locked_at_datetime,
        rq.locked_until_datetime,
        rq.locked_by_user_id,
        cr.approval_note,
        rq.submission_type,
        rq.submission_count,
        rq.SUBMITTED_BY_USER_ID
      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REVIEW_QUEUE as rq
      INNER JOIN (
        SELECT USER_ID, REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE, CREATED_DATETIME AS REVIEW_COMPLETED_DATETIME, NOTE as APPROVAL_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, NOTE as REJECTION_NOTE
        FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
        WHERE _fivetran_deleted=false
      ) as cr ON rq.ID=cr.REVIEW_QUEUE_ID
      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 p.PROJECT_ID = r.PROJECT_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v ON v.VENDOR_ID=p.VENDOR_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".subaccount s ON s.SUBACCOUNT_ID=r.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
      WHERE rq._fivetran_deleted=false
      AND v.label_identifier != 'Test' OR v.label_identifier IS NULL
      HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL
    )
    )
    SELECT
      cr.UPC as upc,
      cr.vendor_id as label_id,
      cr.label_name as label_name,
      cr.REVIEW_CREATED_DATETIME as SUBMISSION_DATE,
      cr.REVIEW_COMPLETED_DATETIME as REVIEW_COMPLETED_DATE,
      cr.COMPLETION_TYPE as completion_type,
      qm.QUEUE_NAME as CURRENT_QUEUE,
      qm.PREVIOUS_QUEUE_NAME as PREVIOUS_QUEUE,
      i.NAME as MOVED_BY,
      qm.MOVED_AT_DATETIME as DATE_MOVED,
      mt.DISPLAY_NAME as ESCALATION_TYPE,
      qm.MOVED_TO_TARGET_NAME as ESCALATED_TO,
      qm.MOVE_NOTE as move_note
    FROM cr_completed_reviews_with_vendor_data as cr
    LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".QUEUE_MOVE_HISTORY qm
      ON cr.ID = qm.review_queue_id
    LEFT JOIN "FACTS"."PROD".IDENTITY as i ON i.ID = qm.MOVED_TO_QUEUE_BY_USER_ID
    LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".move_target mt on mt.ID = qm.MOVED_TO_TARGET_ID
    WHERE CURRENT_QUEUE != 'on_hold'
    and ESCALATION_TYPE IS NOT NULL
    and CURRENT_QUEUE = 'escalation';;
```