# View: dt_apple_lyrics_breakdown

**View Name:** dt_apple_lyrics_breakdown
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Customer_Master_Master, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Track + 3 more`
**File Path:** `views/dt_apple_lyrics_breakdown.view.lkml`

## Overview

- **File Size:** 4489 bytes
- **Lines of Code:** 125
- **Dimensions:** 6
- **Measures:** 2
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |
| `isrc` | string |
| `brand` | string |
| `lyrics` | string |
| `label_tier` | string |
| `tier_row_number` | string |

## Measures

| Name | Type |
|------|------|
| `count_upc` | count_distinct |
| `count_isrc` | count |

## Dimension Groups

| Name | Type |
|------|------|
| `insert_date` | time |

## SQL Comments

- HAVING MIN(TO_DATE(encoding_queue_detail_snowflake.delivery_ended)) >= DATEADD(year, -1, CURRENT_DATE))

## Derived Table

```sql
sql:
    with products_apple_ltm as (
    SELECT distinct
        releases_snowflake.upc  AS upc,
        track_snowflake.isrc  AS isrc,
        MIN(TO_DATE(encoding_queue_detail_snowflake.delivery_ended)) AS insert_date
    FROM orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue_detail AS encoding_queue_detail_snowflake
INNER JOIN orchard_app_reporting_v2.reportsdd_direct_delivery.encoding_queue AS encoding_queue_snowflake
    ON encoding_queue_detail_snowflake.encoding_queue_id = encoding_queue_snowflake.encoding_queue_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER AS customer_master_master_snowflake
    ON customer_master_master_snowflake.CUSTOMER_MASTER_MASTER_ID=encoding_queue_detail_snowflake.dms_master_master_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES  AS releases_snowflake
    ON releases_snowflake.upc=encoding_queue_detail_snowflake.upc
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK  AS track_snowflake
    ON releases_snowflake.upc=track_snowflake.upc
WHERE (encoding_queue_snowflake.meta_update ) = 'N' AND (customer_master_master_snowflake.CUSTOMER_MASTER_MASTER_ID ) = 1
GROUP BY 1,2)
--HAVING MIN(TO_DATE(encoding_queue_detail_snowflake.delivery_ended)) >= DATEADD(year, -1, CURRENT_DATE))

select distinct
products_apple_ltm.*,
metadata.label_tier,
metadata.lyrics,
metadata.brand,
CASE metadata.label_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,
from products_apple_ltm
INNER JOIN (
    SELECT
        orch_app_ar_releases.upc  AS upc,
        orch_app_ar_track.isrc  AS isrc,
        int_dbt_prod_awal_tier_groups.service_tier_name  AS label_tier,
        int_dbt_prod_awal_tier_groups.brand_name as brand,
        case when dt_track_lyrics.track_lyrics is null then 'No' else 'Yes' end AS lyrics
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
    INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor
        ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info
        ON orch_app_ar_vendor.vendor_id = orch_app_ar_artist_info.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  AS orch_app_ar_releases
        ON orch_app_ar_artist_info.artist_id = orch_app_ar_releases.artist_id
    INNER JOIN intelligence.dbt_prod.orch_app_ar_track_track_physical_joined  AS orch_app_ar_track
        ON orch_app_ar_track.release_id = orch_app_ar_releases.release_id
    LEFT JOIN INTELLIGENCE.DBT_PROD.DT_TRACK_LYRICS  AS dt_track_lyrics
        ON orch_app_ar_track.id = dt_track_lyrics.tuid
    LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
        ON orch_app_ar_vendor.vendor_id= int_dbt_prod_awal_tier_groups.labelid
    WHERE ((UPPER(( dt_track_lyrics.track_lyrics  )) NOT LIKE UPPER('%Test%') OR ( dt_track_lyrics.track_lyrics  ) IS NULL))
        and int_dbt_prod_awal_tier_groups.service_tier_name is not null
    GROUP BY 1,2,3,4,5
) metadata
    on metadata.upc = products_apple_ltm.upc and metadata.isrc = products_apple_ltm.isrc

    ;;
```