# View: content_review **View Name:** content_review **Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Project, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Subaccount, Art_Relations_Prod_Art_Relations.Vendor, Prod.Vendor_In_Service_Tier_Service_Tier + 3 more` **File Path:** `content_review.view.lkml` ## Overview - **File Size:** 15406 bytes - **Lines of Code:** 568 - **Dimensions:** 39 - **Measures:** 0 - **Dimension Groups:** 7 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `review_queue_id` | number | | `product_id` | number | | `product_link_completed` | string | | `product_link_inprogress` | string | | `display_upc` | number | | `product_artist_name` | string | | `project_id` | number | | `project_name` | string | | `not_for_distribution` | string | | `product_Name` | string | | `product_format` | string | | `meta_language` | string | | `special_instructions` | string | | `deletions` | string | | `release_status` | string | | `submission_type` | string | | `review_status` | string | | `queue_name` | string | | `completion_type` | string | | `rejection_note` | string | | `approval_note` | string | | `move_note` | string | | `escalated_to` | string | | `actual_reviewer` | string | | `assigned_reviewer` | string | | `primary_relationship_manager` | string | | `secondary_relationship_manager` | string | | `account_id` | number | | `account_name` | string | | `sub_account_id` | number | | `sub_account_name` | string | | `owner` | string | | `account_service_tier` | string | | `brand` | string | | `locked_by` | string | | `mktg_blurb` | string | | `global_mktg_highlights` | string | | `local_mktg_highlights` | string | | `product_manager` | string | ## Dimension Groups | Name | Type | |------|------| | `sale_start_date` | time | | `release_date` | time | | `preorder_date` | time | | `review_submitted_on` | time | | `review_completed_on` | time | | `locked_at_datetime` | time | | `locked_until_datetime` | time | ## SQL Comments - LISTAGG(DISTINCT int_prod_vw_release_artists.release_artists, '|RECORD|') WITHIN GROUP (ORDER BY int_prod_vw_release_artists.release_artists) AS product_artist_list, - LEFT JOIN INTELLIGENCE.DBT_PROD.RELEASE_ARTISTS AS int_prod_vw_release_artists ON int_prod_vw_release_artists.release_id= dt_release_schedule_dashboard.release_id - AND [REVIEW_SUBMITTED_ON=daterange] ## Derived Table ```sql sql: SELECT rq.ID as Review_Queue_ID, rq.PRODUCT_ID as product_id, r.display_upc as display_upc, dt_release_schedule_dashboard.artist_name as product_artist_name, --LISTAGG(DISTINCT int_prod_vw_release_artists.release_artists, '|RECORD|') WITHIN GROUP (ORDER BY int_prod_vw_release_artists.release_artists) AS product_artist_list, r.project_id, p.project_name, r.not_for_distribution, iff(r.release_id is not NULL, r.release_name, 'DELETED PRODUCT') as Product_Name, r.format, r.sale_start_date, r.release_date, r.preorder_date, r.meta_language, r.special_instructions, r.deletions, r.release_status, INITCAP(rq.submission_type) AS SUBMISSION_TYPE, rq.CREATED_DATETIME AS REVIEW_SUBMITTED_ON, IFF(rq.STATUS = 'new','Ready For Review','Completed')as REVIEW_STATUS, IFF(rq.QUEUE_NAME = 'initial','For Review',IFF(rq.QUEUE_NAME = 'escalation','Escalation','Under Investigation')) as QUEUE_NAME, cr.REVIEW_COMPLETED_DATETIME AS REVIEW_COMPLETED_ON, INITCAP(cr.COMPLETION_TYPE) AS COMPLETION_TYPE, rej.note as REJECTION_NOTE, app.note as APPROVAL_NOTE, rq.move_note, concat(oa4.f_name, ' ', oa4.l_name) as Escalated_To, i.name as Actual_Reviewer, concat(oa3.f_name, ' ', oa3.l_name) as Assigned_Reviewer, concat(oa.f_name, ' ', oa.l_name) as Primary_Relationship_Manager, concat(oa2.f_name, ' ', oa2.l_name) as Secondary_Relationship_Manager, v.VENDOR_ID as ACCOUNT_ID, IFF(v.company is NULL OR v.company = '', v.name, v.company) as ACCOUNT_NAME, s.SUBACCOUNT_ID as subaccount_id, s.subaccount_name as SubAccount_Name, v.OWNER as owner, st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER, cb.display_name as brand, rq.locked_at_datetime as locked_at_datetime, rq.locked_until_datetime as locked_until_datetime, i2.name as Locked_by, dt_release_schedule_dashboard.mktg_blurb, dt_release_schedule_dashboard.global_mktg_highlights, dt_release_schedule_dashboard.local_mktg_highlights, pm.product_manager FROM ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.REVIEW_QUEUE as rq LEFT JOIN ( SELECT USER_ID, REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE, CREATED_DATETIME AS REVIEW_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 REVIEW_COMPLETED_DATETIME 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 ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.rejection as rej ON rq.id = rej.review_queue_id AND rej._fivetran_deleted = false LEFT JOIN ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.approval as app ON rq.id = app.review_queue_id AND app._fivetran_deleted = false 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 facts.prod.identity as i ON cr.user_id = i.id LEFT JOIN facts.prod.identity as i2 ON rq.locked_by_user_id = i2.id LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa ON v.assigned_to = oa.id LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa2 on oa2.id = v.quarterback_label_manager left JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa3 on oa3.id = v.assigned_reviewer LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa4 ON rq.moved_to_orchadmin_user_id = oa4.id left outer join FACTS.prod.COMPANY_BRAND_HAS_LABEL_VENDOR cbv on cbv.vendor_id = v.vendor_id left outer join FACTS.prod.COMPANY_BRAND cb on cbv.company_brand_uuid = cb.uuid LEFT JOIN INTELLIGENCE.DBT_PROD.RELEASE_SCHEDULE_DASHBOARD AS dt_release_schedule_dashboard ON r.release_id = dt_release_schedule_dashboard.release_id LEFT JOIN intelligence.dbt_prod.artist_services_pm_union AS pm ON NULLIF(CAST(pm.upc AS VARCHAR), '') = NULLIF(CAST(r.display_upc AS VARCHAR), '') --LEFT JOIN INTELLIGENCE.DBT_PROD.RELEASE_ARTISTS AS int_prod_vw_release_artists ON int_prod_vw_release_artists.release_id= dt_release_schedule_dashboard.release_id WHERE rq._fivetran_deleted=false AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160, 25824) --AND [REVIEW_SUBMITTED_ON=daterange] HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL ORDER BY REVIEW_SUBMITTED_ON, REVIEW_STATUS ASC ;; ```