# View: delivery_history_delivered_status

**View Name:** delivery_history_delivered_status
**Table Source:** `my_schema_name.tester`
**File Path:** `delivery_history_delivered_status.view.lkml`

## Overview

- **File Size:** 4633 bytes
- **Lines of Code:** 172
- **Dimensions:** 10
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- # You can specify the table name if it's different from the view name:
- sql_table_name: my_schema_name.tester ;;
- # # Define your dimensions and measures here, like this:

## Dimensions

| Name | Type |
|------|------|
| `upc` | number |
| `store_id` | string |
| `delivered_deezer` | string |
| `delivered_itunes` | string |
| `delivered_pandora` | string |
| `delivered_spotify` | string |
| `delivered_tidal` | string |
| `delivered_napster` | string |
| `delivered_google` | string |
| `delivered_amazon` | string |

## Derived Table

```sql
sql:
    select fanned.upc
        , all_cmm_id as store_id
        , case WHEN sum_stores>all_cmm_id
           THEN 'Yes'
           ELSE 'No'
           END AS delivered
from
      (select r.upc
           , cmm.customer_master_master_id as all_cmm_id
           , delivery_status.store_id as delivered_cmm_id
           , cmm.customer_master_master_id + ifnull(delivery_status.store_id, 0) as sum_stores
      from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
      cross join orchard_app_reporting_v2.art_relations_prod_art_relations.customer_master_master cmm
      left join
      (select UPC
            , customer_master_master_id as store_id
      from orchard_app_reporting_v2.art_relations_prod_art_relations.delivery_history
      group by 1, 2
      order by 1) delivery_status on delivery_status.upc=r.upc
                  and delivery_status.store_id=cmm.customer_master_master_id
      where cmm.customer_master_master_id in (1
                                           , 4
                                           , 187
                                           , 286
                                           , 348
                                           , 399
                                           , 496
                                           , 708)
      group by 1, 2, 3) fanned
group by 1,2,3
order by 1
;;
```