# View: chartmetric_recordings

**View Name:** chartmetric_recordings
**Table Source:** `Derived from: Chartmetric.Raw_Data, Raw_Data.Cm_Album, Raw_Data.Cm_Artist, Raw_Data.Cm_Track, Raw_Data.L_Cm_Track_Cm_Album + 1 more`
**File Path:** `views/chartmetric_recordings.view.lkml`

## Overview

- **File Size:** 11404 bytes
- **Lines of Code:** 345
- **Dimensions:** 33
- **Measures:** 3
- **Dimension Groups:** 4
- **Filters:** 0

## Comments & Notes

- =========================================================================
- 1. PRIMARY KEYS & CORE HIDDEN IDENTIFIERS
- =========================================================================

## Dimensions

| Name | Type |
|------|------|
| `unique_row_id` | string |
| `cm_album_id` | number |
| `cm_artist_id` | number |
| `cm_track_id` | number |
| `track_name` | string |
| `isrc` | string |
| `track_description` | string |
| `track_need_to_drop_flag` | number |
| `album_name` | string |
| `upc` | string |
| `album_type` | string |
| `album_label` | string |
| `album_spotify_popularity` | number |
| `cm_label_id` | number |
| `album_description` | string |
| `track_album_ordering` | number |
| `artist_name` | string |
| `artist_country_code` | string |
| `artist_gender_code` | number |
| `artist_isni` | string |
| `artist_is_band` | yesno |
| `artist_is_non_artist` | yesno |
| `artist_is_duplicate` | yesno |
| `artist_hometown_city_id` | number |
| `artist_current_city_id` | number |
| `artist_label_string` | string |
| `artist_record_label` | string |
| `artist_booking_agent` | string |
| `artist_press_contact` | string |
| `artist_general_manager` | string |
| `artist_description` | string |
| `track_artist_ordering` | number |
| `track_image_url` | string |

## Measures

| Name | Type |
|------|------|
| `unique_track_count` | count_distinct |
| `unique_album_count` | count_distinct |
| `unique_artist_count` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `album_release` | time |
| `artist_birth` | time |
| `artist_death` | time |
| `track_created` | time |

## SQL Comments

- Unique Composite Row Identifier
- CM_TRACK (Core Track Fields)
- Standardized to uppercase for optimal indexing
- L_CM_TRACK_CM_ALBUM & CM_ALBUM (Core Album Fields)
- L_CM_TRACK_CM_ARTIST & CM_ARTIST (Core Artist Fields)

## Derived Table

```sql
sql:
      SELECT
        -- Unique Composite Row Identifier
        (t.ID || '-' || COALESCE(al.ID::VARCHAR, 'NO_ALBUM') || '-' || COALESCE(ar.ID::VARCHAR, 'NO_ARTIST')) AS unique_row_id,

      -- CM_TRACK (Core Track Fields)
      t.ID AS cm_track_id,
      t.NAME AS track_name,
      UPPER(t.ISRC) AS isrc, -- Standardized to uppercase for optimal indexing
      t.DESCRIPTION AS track_description,
      t.IMAGE_URL AS track_image_url,
      t.NEED_TO_DROP AS track_need_to_drop_flag,
      t.CREATED_AT AS track_created_at,
      t.MODIFIED_AT AS track_modified_at,

      -- L_CM_TRACK_CM_ALBUM & CM_ALBUM (Core Album Fields)
      lta.ORDERING AS track_album_ordering,
      al.ID AS cm_album_id,
      al.UPC AS upc,
      al.NAME AS album_name,
      al.RELEASE_DATE AS album_release_date,
      al.ALBUM_TYPE AS album_type,
      al.LABEL AS album_label,
      al.SPOTIFY_POPULARITY AS album_spotify_popularity,
      al.CM_LABEL AS cm_label_id,
      al.DESCRIPTION AS album_description,
      al.IMAGE_URL AS album_image_url,
      al.CREATED_AT AS album_created_at,
      al.MODIFIED_AT AS album_modified_at,

      -- L_CM_TRACK_CM_ARTIST & CM_ARTIST (Core Artist Fields)
      lta_art.ORDERING AS track_artist_ordering,
      ar.ID AS cm_artist_id,
      ar.NAME AS artist_name,
      ar.CODE2 AS artist_country_code,
      ar.GENDER AS artist_gender_code,
      ar.ISNI AS artist_isni,
      ar.BAND AS artist_is_band,
      ar.IS_NON_ARTIST AS artist_is_non_artist,
      ar.IS_DUPLICATE AS artist_is_duplicate,
      ar.HOMETOWN_CITY AS artist_hometown_city_id,
      ar.CURRENT_CITY AS artist_current_city_id,
      ar.DATE_OF_BIRTH AS artist_date_of_birth,
      ar.DATE_OF_DEATH AS artist_date_of_death,
      ar.LABEL AS artist_label_string,
      ar.RECORD_LABEL AS artist_record_label,
      ar.BOOKING_AGENT AS artist_booking_agent,
      ar.PRESS_CONTACT AS artist_press_contact,
      ar.GENERAL_MANAGER AS artist_general_manager,
      ar.DESCRIPTION AS artist_description,
      ar.IMAGE_URL AS artist_image_url,
      ar.COVER_URL AS artist_cover_url,
      ar.CREATED_AT AS artist_created_at,
      ar.MODIFIED_AT AS artist_modified_at
      FROM CHARTMETRIC.RAW_DATA.CM_TRACK t
      LEFT JOIN CHARTMETRIC.RAW_DATA.L_CM_TRACK_CM_ALBUM lta ON t.ID = lta.CM_TRACK
      LEFT JOIN CHARTMETRIC.RAW_DATA.CM_ALBUM al ON lta.CM_ALBUM = al.ID
      LEFT JOIN CHARTMETRIC.RAW_DATA.L_CM_TRACK_CM_ARTIST lta_art ON t.ID = lta_art.CM_TRACK
      LEFT JOIN CHARTMETRIC.RAW_DATA.CM_ARTIST ar ON lta_art.CM_ARTIST = ar.ID
      ;;
```