# View: dt_first_approver

**View Name:** dt_first_approver
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Completed_Reviews, Not_Unsubmitted, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations + 3 more`
**File Path:** `dt_first_approver.view.lkml`

## Overview

- **File Size:** 5124 bytes
- **Lines of Code:** 132
- **Dimensions:** 2
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |
| `first_approver` | string |

## Derived Table

```sql
sql:
  with first_approver 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,
    r.upc,

    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

    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,
    case when moved_at_datetime = max(moved_at_datetime) over (partition by review_queue_id) then TRUE else FALSE end as is_last_escalation,
    i.name as queue_moved_by,
    move_note,

    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,
    cr.completion_type as completion_type,
    i.name as completed_by,

    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')
)

select distinct rs.upc,
rs.created_datetime,
cr.completion_type,
CASE WHEN (e.queue_moved_by is null) then cr.completed_by when (e.queue_moved_by is not null and e.move_note like '%Metadata has been reviewed. The product is ready for approval once the Special Instructions have been actioned.%') then e.queue_moved_by else cr.completed_by end as first_approver

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)
where CASE WHEN coalesce(e.queue_moved_by,cr.completed_by) is null then FALSE else TRUE end = TRUE
and cr.completion_type = 'approval'
)

select distinct
upc,
case when created_datetime = min(created_datetime) over (partition by upc) then first_approver end as first_approver
from first_approver
qualify case when created_datetime = min(created_datetime) over (partition by upc) then first_approver end is not null;;
```