# View: ChartMetric_Artist_Lookup

**View Name:** chartmetric_artist_lookup
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Chartmetric.Raw_Data, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations, Raw_Data.Acr_Channel + 3 more`
**File Path:** `views/ChartMetric_Artist_Lookup.view.lkml`

## Overview

- **File Size:** 3719 bytes
- **Lines of Code:** 167
- **Dimensions:** 16
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Play_ID` | string |
| `CM_Track_ID` | string |
| `CM_Track_Title` | string |
| `CM_ISRC` | string |
| `CM_Album_Name` | string |
| `CM_Artist_Name` | string |
| `CM_Artist_Name_Split` | string |
| `CM_Duration` | number |
| `play_year` | string |
| `play_date` | string |
| `Radio_Station` | string |
| `Country_Code` | string |
| `Country_Name` | string |
| `CM_Track_Duration` | string |
| `Label` | string |
| `Release_Date` | string |

## Measures

| Name | Type |
|------|------|
| `Play_Count` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `CM_Timestamp` | time |

## SQL Comments

- IDs & Timestamp
- Track Metadata
- Play Stats
- Date logic for dimensions
- Station Metadata

## Derived Table

```sql
sql:
      SELECT
        -- IDs & Timestamp
        cmts.ID AS Play_ID,
        cmts.ACR_TRACK AS CM_Track_ID,
        cmts.TIMESTP AS CM_Timestamp,

      -- Track Metadata
      cmt.TRACK_NAME AS CM_Track_Title,
      cmt.ISRC AS CM_ISRC,
      cmt.ALBUM_NAME AS CM_Album_Name,
      cmt.ARTIST_NAME AS CM_Artist_Name,
    TRIM(artist.value::string) AS CM_Artist_Name_split,
      cmt.DURATION AS CM_Track_Duration,

      -- Play Stats
      cmts.PLAYED_DURATION AS CM_Duration,

      -- Date logic for dimensions
      TO_VARCHAR(cmts.TIMESTP,'yyyy/mm/dd') AS play_date,
      TO_VARCHAR(cmts.TIMESTP,'yyyy') AS play_year,

      -- Station Metadata
      cmc.NAME AS Radio_Station,
      cmc.CODE2 AS Country_Code,
      cy.name AS Country_Name,

      -- Album Metadata (Restoring Label & Release Date)
      alb.LABEL AS Label,
      TO_VARCHAR(alb.RELEASE_DATE, 'YYYY-MM-DD') AS Release_Date

      FROM CHARTMETRIC.RAW_DATA.ACR_TRACK_STAT cmts
      LEFT JOIN CHARTMETRIC.RAW_DATA.ACR_TRACK cmt
      ON cmts.ACR_TRACK = cmt.ID

      LEFT JOIN CHARTMETRIC.RAW_DATA.ACR_CHANNEL cmc
      ON cmts.ACR_CHANNEL = cmc.ID

      -- Simple lookup for full Country Name
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country cy
      ON cmc.CODE2 = cy.country_code

      -- Join to Album to get Label and Release Date
      -- Note: Joining on Name alone is the best option available in this dataset
      LEFT JOIN CHARTMETRIC.RAW_DATA.CM_ALBUM alb
      ON cmt.ALBUM_NAME = alb.NAME
   ,LATERAL FLATTEN( INPUT => SPLIT(cmt.ARTIST_NAME, ',') ) artist   ;;
```