# View: dt_tier_change_log

**View Name:** dt_tier_change_log
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Dbt_Prod.Awal_Tier_Groups, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations, Prod.Cdc__Music_Graph_V5__Vendor_In_Service_Tier + 3 more`
**File Path:** `views/dt_tier_change_log.view.lkml`

## Overview

- **File Size:** 5770 bytes
- **Lines of Code:** 165
- **Dimensions:** 7
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | string |
| `label_name` | string |
| `assigned_reviewer` | string |
| `tier_change_history` | string |
| `current_label_tier` | string |
| `relationship_manager` | string |
| `secondary_relationship_manager` | string |

## Measures

| Name | Type |
|------|------|
| `count_labels` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `tier_change_timestamp` | time |

## Derived Table

```sql
sql:

    with tier_changes as (
     WITH raw_table AS (
        SELECT DISTINCT
            CASE
                WHEN COUNT(DISTINCT t.display_name) OVER (
                    PARTITION BY COALESCE(
                        RECORD_CONTENT:event:start:keys:Vendor[0]:id:I64::INT,
                        RECORD_CONTENT:event:start:keys:Vendor[1]:id:I64::INT
                    )
                ) > 1 THEN TRUE
                ELSE FALSE
            END AS label_change,
            t.display_name AS label_tier,
            RECORD_CONTENT:event:end:keys:ServiceTier[0]:uuid:S::STRING AS service_tier_uuid,
            COALESCE(
                RECORD_CONTENT:event:start:keys:Vendor[0]:id:I64::INT,
                RECORD_CONTENT:event:start:keys:Vendor[1]:id:I64::INT
            ) AS vendor_id,
            RECORD_CONTENT:metadata:txStartTime:TZDT::TIMESTAMP_TZ AS txStartTime
        FROM FACTS.PROD.CDC__MUSIC_GRAPH_V5__VENDOR_IN_SERVICE_TIER tc
        LEFT JOIN FACTS.PROD.SERVICE_TIER t
            ON tc.RECORD_CONTENT:event:end:keys:ServiceTier[0]:uuid:S::STRING = t.uuid
    ),
    ranked_times AS (
        SELECT *,
            DENSE_RANK() OVER (PARTITION BY vendor_id ORDER BY txStartTime ASC) AS time_rank
        FROM raw_table
        WHERE label_change = TRUE
    ),
    with_prev_status AS (
        SELECT
            curr.vendor_id,
            curr.txStartTime,
            curr.label_tier,
            CASE WHEN prev.label_tier IS NOT NULL THEN TRUE ELSE FALSE END AS existed_in_prev_timestamp
        FROM ranked_times curr
        LEFT JOIN ranked_times prev
            ON curr.vendor_id = prev.vendor_id
            AND curr.time_rank = prev.time_rank + 1
            AND curr.label_tier = prev.label_tier
    ),
    with_history AS (
        SELECT DISTINCT
            vendor_id AS label_id,
            txStartTime,
            LISTAGG(label_tier, ' to ') WITHIN GROUP (
                ORDER BY CASE WHEN existed_in_prev_timestamp THEN 0 ELSE 1 END ASC
            ) OVER (PARTITION BY vendor_id, txStartTime) AS tier_change_history
        FROM with_prev_status
        QUALIFY COUNT(DISTINCT label_tier) OVER (PARTITION BY vendor_id, txStartTime) > 1
    ),
    earliest_tiers AS (
        SELECT
            label_id,
            SPLIT_PART(tier_change_history, ' to ', 1) AS earliest_from_tier
        FROM with_history
        QUALIFY ROW_NUMBER() OVER (PARTITION BY label_id ORDER BY txStartTime ASC) = 1
    )
    SELECT
        wh.label_id,
        wh.txStartTime,
        wh.tier_change_history,
        et.earliest_from_tier
    FROM with_history wh
    LEFT JOIN earliest_tiers et
        ON wh.label_id = et.label_id
    ),

    metadata as (
    SELECT
    orch_app_ar_vendor.vendor_id  AS label_id,
    IFF(orch_app_ar_vendor.company IS NULL OR orch_app_ar_vendor.company  = '', orch_app_ar_vendor.name, orch_app_ar_vendor.company)  AS label_name,
    assigned_reviewer.f_name||' '||assigned_reviewer.l_name  AS assigned_reviewer,
    label_manager.f_name||' '||label_manager.l_name  AS relationship_manager,
    quarterback_label_manager.f_name||' '||quarterback_label_manager.l_name  AS secondary_relationship_manager,
    int_dbt_prod_awal_tier_groups.service_tier_name as current_label_tier
    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.orchadmin_users  AS label_manager ON orch_app_ar_vendor.assigned_to = label_manager.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS quarterback_label_manager ON orch_app_ar_vendor.quarterback_label_manager = quarterback_label_manager.id
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
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS assigned_reviewer ON orch_app_ar_vendor.assigned_reviewer = assigned_reviewer.id
    )

select m.label_id,
m.label_name,
tc.txstarttime as tier_change_timestamp,
tc.tier_change_history,
m.current_label_tier,
m.relationship_manager,
m.secondary_relationship_manager,
m.assigned_reviewer
from metadata m
inner join tier_changes tc
    on m.label_id = tc.label_id
    ;;
```