# View: bi_riaa_certification

**View Name:** bi_riaa_certification
**Table Source:** `Derived from: Awal_Sax, Awal_Sax.Sax, Sax.Exp_Versions, Sax.Exploitation_Formats, Sax.Expv_Configurations + 3 more`
**File Path:** `DOSC/BI/riaa_certification/views/bi_riaa_certification.view.lkml`

## Overview

- **File Size:** 5605 bytes
- **Lines of Code:** 164
- **Dimensions:** 11
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `territory` | string |
| `source_name` | string |
| `right_type` | string |
| `catalogue_number` | string |
| `expv_configuration_id` | string |
| `track_or_bundle` | string |
| `physical_royalty_type` | string |
| `recording_id` | string |
| `recording_title` | string |
| `recording_isrc` | string |
| `artist` | string |

## Measures

| Name | Type |
|------|------|
| `quantity` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `period` | time |

## Derived Table

```sql
sql:
      SELECT
        ROYALTIES.ROYALTY_PERIOD_START_DATE,
        NVL(ROYALTY_TERR.NAME, SALES_TERR.NAME) AS TERRITORY,
        INTERESTED_PARTIES.NAME SOURCE_NAME,
        RIGHT_TYPES.DESCRIPTION AS RIGHT_TYPE,
        EXPV_CONFIGURATIONS.CATALOGUE_NUMBER,
        EXPV_CONFIGURATIONS.ID AS EXPV_CONFIG_ID,
        SUM(ROYALTIES.QUANTITY) AS QUANTITY,
        ROYALTIES.EXP_VERSION_ROY_TYPE AS TRACK_OR_BUNDLE,
        ROYALTIES.PHYSICAL_ROY_TYPE AS PHYSICAL_ROYALTY_TYPE,
        COALESCE(RECORDING_ID, 'Bundle...') AS RECORDING_ID,
        COALESCE(RECORDINGS.TITLE, 'Bundle...') AS RECORDING_TITLE,
        COALESCE(RECORDINGS.ISRC, 'Bundle...') AS RECORDING_ISRC,
        RECORDING_ARTISTS.NAME AS ARTIST
      FROM
        AWAL_SAX.SAX.RECORDINGS
        INNER JOIN AWAL_SAX.SAX.ROYALTIES ON RECORDINGS.ID = ROYALTIES.RECORDING_ID
        INNER JOIN AWAL_SAX.SAX.EXPV_CONFIGURATIONS ON EXPV_CONFIGURATIONS.ID = ROYALTIES.EXPV_CONFIGURATION_ID
        INNER JOIN AWAL_SAX.SAX.RIGHT_TYPES ON RIGHT_TYPES.ID = ROYALTIES.RIGHT_TYPE_ID
        INNER JOIN AWAL_SAX.SAX.EXP_VERSIONS ON EXP_VERSIONS.ID = EXPV_CONFIGURATIONS.EXP_VERSION_ID
        INNER JOIN AWAL_SAX.SAX.EXPLOITATIONS ON EXPLOITATIONS.ID = EXP_VERSIONS.EXPLOITATION_ID
        INNER JOIN AWAL_SAX.SAX.EXPLOITATION_FORMATS ON EXPLOITATION_FORMATS.ID = EXPLOITATIONS.EXPLOITATION_FORMAT_ID
        INNER JOIN AWAL_SAX.SAX.RECORDING_ARTISTS ON RECORDING_ARTISTS.ID = EXPLOITATIONS.EXPLOITATION_ARTIST_ID
        INNER JOIN AWAL_SAX.SAX.BATCHES ON ROYALTIES.BATCH_ID = BATCHES.ID
        INNER JOIN AWAL_SAX.SAX.INTERESTED_PARTIES ON BATCHES.SOURCE_IP_ID = INTERESTED_PARTIES.ID
        LEFT JOIN AWAL_SAX.SAX.TERRITORIES ROYALTY_TERR ON ROYALTIES.TERRITORY_ID = ROYALTY_TERR.ID
        LEFT JOIN AWAL_SAX.SAX.TERRITORIES SALES_TERR ON ROYALTIES.SALES_TERRITORY_ID = SALES_TERR.ID
      WHERE
        ROYALTIES.BATCH_ID = BATCHES.ID
        AND (
            (ROYALTIES.RIGHT_TYPE_ID IN ('RT46','RT48')
            AND ROYALTIES.TERRITORY_ID IN ('TERR744', 'TERR587','TERR639','TERR643','TERR610','TERR763','TERR761')
            )
            OR
            (ROYALTIES.RIGHT_TYPE_ID IN ('RT38','RT99','RT114','RT115')
            AND ROYALTIES.SALES_TERRITORY_ID IN ('TERR744', 'TERR587','TERR639','TERR643','TERR610','TERR763','TERR761')
            AND ROYALTIES.PHYSICAL_ROY_TYPE IN ('Sale', 'Return'))
          )
        AND current_date() >= ROYALTIES.ROYALTY_PERIOD_END_DATE
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%soundcloud%'
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%tiktok%'
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%aspiro%'
        AND LOWER(INTERESTED_PARTIES.NAME) != 'dubset media holdings inc'
        AND LOWER(INTERESTED_PARTIES.NAME) != 'electric jukebox'
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%peloton%'
        AND LOWER(INTERESTED_PARTIES.NAME) != 'saavn'
        AND INTERESTED_PARTIES.NAME != 'UMA'
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%xbox music%'
        AND LOWER(INTERESTED_PARTIES.NAME) NOT LIKE '%iheartradio%'
        AND INTERESTED_PARTIES.NAME != 'Pandora'
        AND INTERESTED_PARTIES.NAME != 'Juno'
        AND INTERESTED_PARTIES.NAME != 'Snap'
        AND INTERESTED_PARTIES.NAME NOT LIKE 'Qobuz%'
      GROUP BY
        ROYALTIES.ROYALTY_PERIOD_START_DATE,
        NVL(ROYALTY_TERR.NAME, SALES_TERR.NAME),
        INTERESTED_PARTIES.NAME,
        RIGHT_TYPES.DESCRIPTION,
        EXPV_CONFIGURATIONS.CATALOGUE_NUMBER,
        EXPV_CONFIGURATIONS.ID,
        ROYALTIES.EXP_VERSION_ROY_TYPE,
        ROYALTIES.PHYSICAL_ROY_TYPE,
        COALESCE(RECORDING_ID, 'Bundle...'),
        COALESCE(RECORDINGS.TITLE, 'Bundle...'),
        COALESCE(RECORDINGS.ISRC, 'Bundle...'),
        RECORDING_ARTISTS.NAME
      ORDER BY
        RECORDING_ID,
        TERRITORY,
        ROYALTY_PERIOD_START_DATE,
        RIGHT_TYPES.DESCRIPTION,
        ROYALTIES.EXP_VERSION_ROY_TYPE,
        ROYALTIES.PHYSICAL_ROY_TYPE;;
```