# View: deleted_labels_snowflake_fd

**View Name:** deleted_labels_snowflake_fd
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Full_Deletions, Art_Relations_Prod_Art_Relations.Releases, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations + 3 more`
**File Path:** `deleted_labels_snowflake_fd.view.lkml`

## Overview

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

## Comments & Notes

- this view derives the Label Deletion Date by finding the date that the last release deleted occurred

## Dimensions

| Name | Type |
|------|------|
| `vendor_id` | number |

## Measures

| Name | Type |
|------|------|
| `count_deleted` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `delete_date` | time |

## Derived Table

```sql
sql:select v.vendor_id
, to_date(fd1.date_deleted) as delete_date
from orchard_app_reporting_v2.art_relations_prod_art_relations.full_deletions  fd1
left join orchard_app_reporting_v2.art_relations_prod_art_relations.releases r on fd1.upc=r.upc
left join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on r.artist_id = ai.artist_id
left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on ai.vendor_id = v.vendor_id

inner join

(select fd2.upc, max(fd2.date_deleted) as date_del
from orchard_app_reporting_v2.art_relations_prod_art_relations.full_deletions fd2
where fd2.action = 'delete'
group by 1) fd2 on fd1.date_deleted = fd2.date_del and fd1.upc=fd2.upc

inner join

(select v.vendor_id, max(fd3.date_deleted) as date_deleted
from orchard_app_reporting_v2.art_relations_prod_art_relations.full_deletions  fd3
left join orchard_app_reporting_v2.art_relations_prod_art_relations.releases r on fd3.upc=r.upc
left join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on r.artist_id = ai.artist_id
left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on ai.vendor_id = v.vendor_id
where fd3.action = 'delete'
group by 1)fd3 on v.vendor_id=fd3.vendor_id and fd3.date_deleted= fd1.date_deleted
where v.status='deletion'
or v.vendor_id in (
         select releases_count.vendor_id
          from(
          select all_count.vendor_id
                  , deleted_releases
                  , all_releases
                  , deleted_releases - all_releases as releases_diff
          from  (
                select v.vendor_id
                     , count(distinct r.upc) as all_releases
              from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
              left join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on r.artist_id=ai.artist_id
              left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on ai.vendor_id=v.vendor_id
              where r.release_status='in_content'
              and r.not_for_distribution='N'
              group by 1
              ) all_count

          inner join

          (    select v.vendor_id
                     , count(distinct r.upc) as deleted_releases
              from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
              left join orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai on r.artist_id=ai.artist_id
              left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on ai.vendor_id=v.vendor_id
              where r.deletions='Y'
              and r.release_status='in_content'
              and r.not_for_distribution='N'
              group by 1
              ) deleted_count
           on deleted_count.vendor_id=all_count.vendor_id
          where all_releases>0
        ) releases_count

      left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on v.vendor_id=releases_count.vendor_id
      where releases_diff=0
      and v.status='signed'
      and v.label_identifier!='Test'
      order by 1

       )
group by 1,2
 ;;
```