# View: no_tiktok_carveout_no_monetization

**View Name:** no_tiktok_carveout_no_monetization
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Vendor_Closers, Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Restrictions_Label_Store + 3 more`
**File Path:** `views/tiktok_conflicts/no_tiktok_carveout_no_monetization.view.lkml`

## Overview

- **File Size:** 12177 bytes
- **Lines of Code:** 269
- **Dimensions:** 9
- **Measures:** 3
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | string |
| `label_tier` | string |
| `label_name` | string |
| `owner` | string |
| `brand` | string |
| `closer` | string |
| `relationship_manager` | string |
| `monetization_territories` | string |
| `no_monetization_territories` | string |

## Measures

| Name | Type |
|------|------|
| `monetization_territory_count` | sum |
| `no_monetization_territory_count` | sum |
| `gross_revenue_usd` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `label_created_date` | time |

## Derived Table

```sql
sql:

  with tiktok_carveout as (
    WITH int_prod_vw_restrictions_label_store AS
        (SELECT * FROM intelligence.dbt_prod.restrictions_label_store WHERE 1=1)

    SELECT DISTINCT
    (TO_CHAR(TO_DATE(orch_app_ar_vendor.date_created ), 'YYYY-MM-DD')) AS label_created_date,
    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,
    int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
    int_dbt_prod_awal_tier_groups.owner  AS owner,
    int_dbt_prod_awal_tier_groups.brand_name  AS brand,
    LISTAGG(DISTINCT closer.f_name||' '||closer.l_name, ', ')  AS closer,
    label_manager.f_name||' '||label_manager.l_name  AS relationship_manager
    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 int_prod_vw_restrictions_label_store
        ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_store.vendor_id
    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.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
        ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
        ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.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
    WHERE ((UPPER(( int_prod_vw_restrictions_label_store.list_aggregate_restrictions  )) LIKE UPPER('%TikTok (Audio Library)%')))
    group by all
),

no_tiktok_carveout as (
    WITH int_prod_vw_restrictions_label_store AS
        (SELECT * FROM intelligence.dbt_prod.restrictions_label_store WHERE 1=1)

    SELECT DISTINCT
    (TO_CHAR(TO_DATE(orch_app_ar_vendor.date_created ), 'YYYY-MM-DD')) AS label_created_date,
    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,
    int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
    int_dbt_prod_awal_tier_groups.owner  AS owner,
    int_dbt_prod_awal_tier_groups.brand_name  AS brand,
    LISTAGG(DISTINCT closer.f_name||' '||closer.l_name, ', ')  AS closer,
    label_manager.f_name||' '||label_manager.l_name  AS relationship_manager
    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 int_prod_vw_restrictions_label_store
        ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_store.vendor_id
    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.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
        ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
        ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.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
    WHERE ((UPPER(( int_prod_vw_restrictions_label_store.list_aggregate_restrictions  )) NOT LIKE UPPER('%TikTok (Audio Library)%')))
    group by all
),

territory_carveout 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,
        int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
        int_dbt_prod_awal_tier_groups.owner  AS owner,
        int_dbt_prod_awal_tier_groups.brand_name  AS brand,
        listagg(distinct closer.f_name||' '||closer.l_name)  AS closer,
        label_manager.f_name||' '||label_manager.l_name  AS relationship_manager,
        int_prod_vw_restrictions_label_territory.territory_carveout  AS territory_restrictions,
        country.name as country_name,
        country.country_code as country_code,
    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 (
        SELECT
            r.vendor_id,
            TRIM(f.value) AS territory_carveout
        FROM intelligence.dbt_prod.restrictions_label_territory r,
             LATERAL FLATTEN(
                 INPUT => SPLIT(r.list_aggregate_restrictions, ';'),
                 OUTER => TRUE) f
    ) AS int_prod_vw_restrictions_label_territory
        ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_territory.vendor_id
    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 royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract  AS orch_app_ar_vendor_contract
        ON orch_app_ar_vendor.vendor_id = orch_app_ar_vendor_contract.vendor_id
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
        ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
        ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.id
    LEFT JOIN intelligence.dbt_prod.active_vendor_contracts  AS active_label
        ON orch_app_ar_vendor.vendor_id = active_label.vendor_id and orch_app_ar_vendor_contract.id = active_label.vendor_contract_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.COUNTRY as country
        on country.name = int_prod_vw_restrictions_label_territory.territory_carveout
    WHERE (IFF(active_label.vendor_id IS NULL, FALSE, TRUE) )
        AND (int_prod_vw_restrictions_label_territory.territory_carveout ) IS NOT NULL
    group by all
),

territory_carveout_flattened as (
select label_id,
label_name,
label_tier,
owner,
brand,
closer,
relationship_manager,
listagg(distinct country_code, ',') within group (order by country_code) country_code_list
from territory_carveout
group by all
),

monetization as (
SELECT
    ORCHARD_OBJ_ID AS label_id,
    LISTAGG(TERRITORY, ',') AS monetization_territories,
    count(territory) as monetization_territory_count
FROM FACTS.PROD.HAS_FINGERPRINT_RULE
WHERE SERVICE = 'tiktok'
AND ORCHARD_OBJ_TYPE = 'Vendor'
AND policy = 'monetize'
GROUP BY 1
),

no_monetization as (
    SELECT
        ORCHARD_OBJ_ID AS label_id,
        LISTAGG(TERRITORY, ',') within group (order by territory) AS no_monetization_territories,
        count(territory) as no_monetization_territory_count
    FROM FACTS.PROD.HAS_FINGERPRINT_RULE
    WHERE SERVICE = 'tiktok'
    AND ORCHARD_OBJ_TYPE = 'Vendor'
    AND policy = 'carveout'
    GROUP BY 1
),

ltm_revenue as (
SELECT
    facts_prod_dim_label.labelid  AS label_id,
    COALESCE(SUM(case
      when vw_abacus_fact_sales_distro.sale_currency_code = 'USD' then vw_abacus_fact_sales_distro.gross_revenue_sale_currency
      when vw_abacus_fact_sales_distro.sale_currency_code != 'USD' and  orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code  = 'USD' then vw_abacus_fact_sales_distro.gross_revenue_sale_currency *  orch_app_ar_prod_royalty_accounting_exchange_rate.rate
    else 0
    end), 0) AS gross_revenue_usd
FROM royalty_accounting.prod.abacus_fact_sales_unified_dbt  AS vw_abacus_fact_sales_distro
LEFT JOIN facts.prod.dim_label  AS facts_prod_dim_label ON vw_abacus_fact_sales_distro.label_id = facts_prod_dim_label.labelid
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD  AS orch_app_ar_royalty_statement_period ON vw_abacus_fact_sales_distro.statement_period_id = orch_app_ar_royalty_statement_period.statement_period_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.EXCHANGE_RATE AS orch_app_ar_prod_royalty_accounting_exchange_rate ON vw_abacus_fact_sales_distro.statement_period_id= orch_app_ar_prod_royalty_accounting_exchange_rate.statement_period_id and
          vw_abacus_fact_sales_distro.sale_currency_code=orch_app_ar_prod_royalty_accounting_exchange_rate.from_currency_code
          and orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code = 'USD'
WHERE ((( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) >= ((DATEADD('month', -12, DATE_TRUNC('month', CURRENT_DATE())))) AND ( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) < ((DATEADD('month', 13, DATEADD('month', -12, DATE_TRUNC('month', CURRENT_DATE())))))))
GROUP BY ALL
)


select ntc.*,
m.* exclude (label_id),
nm.* exclude (label_id),
m.monetization_territory_count + nm.no_monetization_territory_count as total_count,
r.gross_revenue_usd,
from no_tiktok_carveout ntc
inner join monetization m
    on ntc.label_id = m.label_id
left join no_monetization nm
    on ntc.label_id = nm.label_id
left join ltm_revenue r
  on ntc.label_id = r.label_id
where nm.no_monetization_territory_count < 249 and m.monetization_territory_count + nm.no_monetization_territory_count != 249

;;
```