# View: dt_album_cover_art

**View Name:** dt_album_cover_art
**Table Source:** `Derived Table (Custom SQL)`
**File Path:** `views/dt_album_cover_art.view.lkml`

## Overview

- **File Size:** 1419 bytes
- **Lines of Code:** 48
- **Dimensions:** 3
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `product_id` | string |
| `cover_url` | string |
| `album_cover_art` | string |

## Derived Table

```sql
sql:
  with cte as (
      SELECT
      au.PRODUCT_ID,
      af.ASSET_SUBTYPE,
      MAX(CASE WHEN af.ASSET_SUBTYPE = 'cover' THEN 'https://images.theorchard.io/v2/product/cover'||RIGHT(af.FILENAME, len(af.FILENAME) - 23) END) AS cover_url,
      MAX(CASE WHEN af.ASSET_SUBTYPE = 'xlarge_cover' THEN 'https://images.theorchard.io/v2/product/xlarge_cover'||RIGHT(af.FILENAME, len(af.FILENAME) - 30) END) AS xlarge_cover_url
    FROM
      "ORCHARD_APP_REPORTING_V2"."PROD_OWS_ASSETS_OWS_ASSETS"."ASSET_FINAL" af
    INNER JOIN
      "ORCHARD_APP_REPORTING_V2"."PROD_OWS_ASSETS_OWS_ASSETS"."ASSET_UPLOAD" au ON au.id = af.asset_upload_id
    WHERE
      au.api_version = 2 AND
      au.deleted = 0 AND
      (af.asset_subtype = 'cover' OR af.asset_subtype = 'xlarge_cover')
    GROUP BY
      au.PRODUCT_ID,af.ASSET_SUBTYPE
)
select distinct
product_id,
coalesce(max(xlarge_cover_url),max(cover_url)) as cover_url
from cte
group by product_id
  ;;
```