# View: dt_custom_napalm_upc

**View Name:** dt_custom_napalm_upc
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Releases, Dbt_Prod_Seed_Data.Transaction_Type_Group, Intelligence.Dbt_Prod_Seed_Data, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations + 3 more`
**File Path:** `dt_custom_napalm_upc.view.lkml`

## Overview

- **File Size:** 3408 bytes
- **Lines of Code:** 85
- **Dimensions:** 1
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `upc` | string |

## Derived Table

```sql
sql: With napalm_top_100 as (

SELECT
  a.releaseid  UPC,
  COALESCE(SUM(a.units ), 0) AS units
FROM facts.prod.fact_analytics a
LEFT JOIN facts.prod.dim_transactiontype tt ON a.transactiontypeid = tt.transactiontypeid
LEFT JOIN intelligence.dbt_prod_seed_data.transaction_type_group  tg ON tt.transactiontypeid = tg.transactiontypeid
LEFT JOIN facts.prod.dim_licensor l ON a.licensorid = l.licensorid

WHERE ((((a.download_activity_date ) >= ((DATEADD('day', -6, CURRENT_DATE())))
         AND (a.download_activity_date ) < ((DATEADD('day', 7, DATEADD('day', -6, CURRENT_DATE())))))))
         AND (a.labelid  = 25954)
AND ((UPPER(tg.transaction_group ) = UPPER('Streaming')))
AND (a.labelid != 32551 AND a.feedid NOT IN (8)
  AND l.distributor != 'sme')
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100),

napalm_2020 as (SELECT
  a.releaseid  AS UPC
FROM facts.prod.fact_analytics a
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON a.releaseid = r.upc
LEFT JOIN facts.prod.dim_licensor l ON a.licensorid = l.licensorid

WHERE (a.labelid  = 25954)
                AND ((r.release_date  >= TO_TIMESTAMP('2020-01-01')))
                AND (a.labelid != 32551 AND a.feedid NOT IN (8)
  AND l.distributor != 'sme')
GROUP BY 1
ORDER BY 1),

napalm_custom as (SELECT
  a.releaseid  AS UPC
FROM facts.prod.fact_analytics a
LEFT JOIN facts.prod.dim_licensor l ON a.licensorid = l.licensorid
WHERE (a.labelid  = 25954) AND (a.releaseid  IN (840588108862,840588108855,819224018971,819224016274,819224014737,
                                                 840588116683,840588125203,840588118564,840588110025,840588111718,
                                                 840588121151,840588112142,819224019633,840588100200,840588124688,
                                                 840588117765,840588102624,840588106936,819224018391,819224016137,
                                                 840588116140,840588117499,840588105373,819224012986,819224012795,
                                                 819224015772,840588122271,840588122288,840588103546,840588101863,
                                                 840588123476,840588107346,840588123971,840588124534,840588120581,
                                                 840588106769,840588115808,840588116218,840588116690,840588116720,
                                                 840588101566,840588101665,840588101672,840588124237,840588108640,
                                                 840588114894,819224016755,819224016748,819224015871,881226767720,
                                                 881226767621,840588115907,840588116959,840588117574,840588105199,
                                                 840588105229,840588101375,840588101719,840588127726,840588110698,
                                                 840588105298,840588119721,840588123001,840588116904,840588122387,
                                                 840588124275,840588110483,840588103331,840588119165))
AND (a.labelid != 32551 AND a.feedid NOT IN (8)
AND l.distributor != 'sme')
GROUP BY 1
ORDER BY 1),

final as (

select UPC
from napalm_top_100

union all

select UPC
from napalm_2020

union all

select UPC
from napalm_custom)

select distinct(UPC)
from final ;;
```