# View: chartmetric_itunes

**View Name:** chartmetric_itunes
**Table Source:** `Derived from: Chartmetric.Raw_Data, Raw_Data.Itunes, Raw_Data.Itunes_Album, Raw_Data.Itunes_Artist, Raw_Data.L_Itunes_Artist`
**File Path:** `views/chartmetric_itunes.view.lkml`

## Overview

- **File Size:** 10341 bytes
- **Lines of Code:** 357
- **Dimensions:** 29
- **Measures:** 3
- **Dimension Groups:** 7
- **Filters:** 0

## Comments & Notes

- =========================================================================
- 1. PRIMARY KEY (Looker Internal Unique Row ID)
- =========================================================================

## Dimensions

| Name | Type |
|------|------|
| `unique_row_id` | string |
| `track_id` | number |
| `itunes_track_id` | string |
| `track_name` | string |
| `track_artist_name_string` | string |
| `track_price` | string |
| `track_genre` | string |
| `isrc` | string |
| `storefront` | string |
| `composer_name` | string |
| `itunes_album_id` | string |
| `album_name` | string |
| `album_copyright` | string |
| `album_num_tracks` | number |
| `upc` | string |
| `album_description` | string |
| `album_is_single` | yesno |
| `album_label` | string |
| `album_genres` | string |
| `itunes_artist_id` | number |
| `artist_name` | string |
| `cm_artist_id` | number |
| `artist_primary_genre` | string |
| `artist_is_songwriter` | yesno |
| `artist_ordering` | number |
| `track_artwork_url` | string |
| `track_preview_url` | string |
| `album_artwork_url` | string |
| `artist_artwork_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 |
| `track_created` | time |
| `track_modified` | time |
| `album_created` | time |
| `album_modified` | time |
| `artist_created` | time |
| `artist_modified` | time |

## SQL Comments

- Unique Identifier for Looker Symmetric Aggregations / Row Uniqueness
- ITUNES (Track Level Columns)
- ITUNES_ALBUM (Album Level Columns)
- L_ITUNES_ARTIST (Linker Level Columns)
- ITUNES_ARTIST (Artist Level Columns)

## Derived Table

```sql
sql:
      SELECT
        -- Unique Identifier for Looker Symmetric Aggregations / Row Uniqueness
        CONCAT(t.ID, '-', COALESCE(ar.ID::VARCHAR, 'NO_ARTIST')) AS unique_row_id,

      -- ITUNES (Track Level Columns)
      t.ID AS track_id,
      t.ITUNES_TRACK_ID AS itunes_track_id,
      t.ITUNES_ALBUM_ID AS track_itunes_album_id,
      t.TRACK_NAME AS track_name,
      t.ARTIST_NAME AS track_artist_name_string,
      t.ARTWORK_URL AS track_artwork_url,
      t.PREVIEW_URL AS track_preview_url,
      t.TRACK_PRICE AS track_price,
      t.TRACK_GENRE AS track_genre,
      t.ISRC AS isrc,
      t.STOREFRONT AS storefront,
      t.COMPOSER_NAME AS composer_name,
      t.CREATED_AT AS track_created_at,
      t.MODIFIED_AT AS track_modified_at,

      -- ITUNES_ALBUM (Album Level Columns)
      al.ID AS album_pk_id,
      al.ITUNES_ALBUM_ID AS itunes_album_id,
      al.NAME AS album_name,
      al.RELEASE_DATE AS album_release_date,
      al.COPYRIGHT AS album_copyright,
      al.NUM_TRACK AS album_num_tracks,
      al.UPC AS upc,
      al.DESCRIPTION AS album_description,
      al.IS_SINGLE AS album_is_single,
      al.ARTWORK_URL AS album_artwork_url,
      al.LABEL AS album_label,
      al.GENRES AS album_genres,
      al.CREATED_AT AS album_created_at,
      al.MODIFIED_AT AS album_modified_at,

      -- L_ITUNES_ARTIST (Linker Level Columns)
      l.ID AS link_id,
      l.ITUNES AS link_track_id,
      l.ITUNES_ARTIST AS link_artist_id,
      l.ORDERING AS artist_ordering,
      l.CREATED_AT AS link_created_at,
      l.MODIFIED_AT AS link_modified_at,

      -- ITUNES_ARTIST (Artist Level Columns)
      ar.ID AS artist_pk_id,
      ar.ITUNES_ARTIST_ID AS itunes_artist_id,
      ar.NAME AS artist_name,
      ar.CM_ARTIST AS cm_artist_id,
      ar.PRIMARY_GENRE AS artist_primary_genre,
      ar.ARTWORK_URL AS artist_artwork_url,
      ar.IS_SONGWRITER AS artist_is_songwriter,
      ar.CREATED_AT AS artist_created_at,
      ar.MODIFIED_AT AS artist_modified_at
      FROM CHARTMETRIC.RAW_DATA.ITUNES t
      LEFT JOIN CHARTMETRIC.RAW_DATA.ITUNES_ALBUM al ON t.ITUNES_ALBUM_ID = al.ITUNES_ALBUM_ID
      LEFT JOIN CHARTMETRIC.RAW_DATA.L_ITUNES_ARTIST l ON t.ID = l.ITUNES
      LEFT JOIN CHARTMETRIC.RAW_DATA.ITUNES_ARTIST ar ON l.ITUNES_ARTIST = ar.ID
      ;;
```