# View: dt_playlisting_by_track

**View Name:** dt_playlisting_by_track
**Table Source:** `Derived from: Coalesced_Placements, Coalesced_Placements_Amazon_Grouped_By_Playlist_Name, Coalesced_Placements_By_Playlist_Id, Grouped_Placements, Private_Placements + 3 more`
**File Path:** `dt_playlisting_by_track.view.lkml`

## Overview

- **File Size:** 28847 bytes
- **Lines of Code:** 792
- **Dimensions:** 33
- **Measures:** 1
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `isrc` | string |
| `store_id` | number |
| `store_playlist_id` | string |
| `current_position` | number |
| `previous_position` | number |
| `peak_position` | number |
| `position_change` | number |
| `days_on_playlist` | number |
| `storefront_count` | number |
| `first_play` | date |
| `streams_all_time` | number |
| `streams_last_365_days` | number |
| `streams_last_183_days` | number |
| `streams_last_28_days` | number |
| `streams_last_7_days` | number |
| `streams_last_1_day` | number |
| `completion_rate_all_time` | number |
| `completion_rate_last_365_days` | number |
| `completion_rate_last_183_days` | number |
| `completion_rate_last_28_days` | number |
| `completion_rate_last_7_days` | number |
| `completion_rate_last_1_day` | number |
| `playlist_name` | string |
| `playlist_artwork_url` | string |
| `playlist_genres` | string |
| `playlist_track_count` | number |
| `playlist_follower_count` | number |
| `playlist_type` | string |
| `playlist_uri` | string |
| `curator_name` | string |
| `curator_id` | string |
| `curator_country` | string |
| `total_count` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |

## Dimension Groups

| Name | Type |
|------|------|
| `last_added_on_date` | time |
| `removed_on` | time |

## SQL Comments

- selector
- public data
- private data
- metadata
- and isrc = 'ARAT92461158'

## Derived Table

```sql
sql: with public_placements as (
          select
              -- selector
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              current_position,
            
      
          iff(
              previous_position_date > dateadd(day, -14, current_timestamp),
              previous_position,
              null
          )
       as previous_position,
            peak_position,
            
          iff(
              previous_position_date > dateadd(day, -14, current_timestamp),
              previous_position -  current_position,
              null
          )
       as position_change,
              last_added_on_date,
              removed_on,
              storefront_count,
              -- private data
              null as first_play,
              
          null as streams_all_time,
          null as streams_last_365_days,
          null as streams_last_183_days,
          null as streams_last_28_days,
          null as streams_last_7_days,
          null as streams_last_1_day,
          null as completions_streams_all_time,
          null as completions_completions_all_time,
          null as completions_streams_last_365_days,
          null as completions_completions_last_365_days,
          null as completions_streams_last_183_days,
          null as completions_completions_last_183_days,
          null as completions_streams_last_28_days,
          null as completions_completions_last_28_days,
          null as completions_streams_last_7_days,
          null as completions_completions_last_7_days,
          null as completions_streams_last_1_day,
          null as completions_completions_last_1_day,
      
      
              --metadata
              playlist_name as public_playlist_name,
              playlist_artwork_url as public_playlist_artwork_url,
              playlist_genres as public_playlist_genres,
              playlist_track_count as public_playlist_track_count,
              playlist_follower_count as public_playlist_follower_count,
              playlist_type as public_playlist_type,
              playlist_uri as public_playlist_uri,
              curator_name as public_curator_name,
              curator_id as public_curator_id,
              curator_country as public_curator_country,
              null as private_playlist_name,
              null as private_playlist_artwork_url,
              null as private_playlist_genres,
              null as private_playlist_track_count,
              null as private_playlist_follower_count,
              null as private_playlist_type,
              null as private_playlist_uri,
              null as private_curator_name,
              null as private_curator_id,
              null as private_curator_country
          from
              FACTS.PROD.playlists_placements_by_isrc_playlist_public
          where
              true
      
              
          and ((last_added_on_date is not null and removed_on is null) 
              or (streams_last_7_days > 0 
              and playlist_type in ('PERSONALIZED', 'ALGORITHMIC', 'STATION', 'RADIO')))
      
      
      
              --and isrc = 'ARAT92461158'
      
      
              and UPPER(public_playlist_type) in ('ALGORITHMIC','CURATED','EDITORIAL','NEW_MUSIC_FRIDAY','PERSONALIZED')
      
      
              and store_id in ('286')
      ),
      private_placements as (
          select
              -- selector
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              null as current_position,
              null as previous_position,
              null as peak_position,
              null as position_change,
              null as last_added_on_date,
              null as removed_on,
              storefront_count,
              -- private data
              first_play,
              
          streams_all_time as streams_all_time,
          streams_last_365_days as streams_last_365_days,
          streams_last_183_days as streams_last_183_days,
          streams_last_28_days as streams_last_28_days,
          streams_last_7_days as streams_last_7_days,
          streams_last_1_day as streams_last_1_day,
          completions_streams_all_time,
          completions_completions_all_time,
          completions_streams_last_365_days,
          completions_completions_last_365_days,
          completions_streams_last_183_days,
          completions_completions_last_183_days,
          completions_streams_last_28_days,
          completions_completions_last_28_days,
          completions_streams_last_7_days,
          completions_completions_last_7_days,
          completions_streams_last_1_day,
          completions_completions_last_1_day,
      
      
              -- metadata
              null as public_playlist_name,
              null as public_playlist_artwork_url,
              null as public_playlist_genres,
              null as public_playlist_track_count,
              null as public_playlist_follower_count,
              null as public_playlist_type,
              null as public_playlist_uri,
              null as public_curator_name,
              null as public_curator_id,
              null as public_curator_country,
              playlist_name as private_playlist_name,
              playlist_artwork_url as private_playlist_artwork_url,
              playlist_genres as private_playlist_genres,
              playlist_track_count as private_playlist_track_count,
              playlist_follower_count as private_playlist_follower_count,
              playlist_type as private_playlist_type,
              playlist_uri as private_playlist_uri,
              curator_name as private_curator_name,
              curator_id as private_curator_id,
              curator_country as private_curator_country
          from
              FACTS.PROD.playlists_placements_by_isrc_playlist_private
          where
              true
      
              --and isrc = 'ARAT92461158'
      
              
      
      
      
              and UPPER(private_playlist_type) in ('ALGORITHMIC','CURATED','EDITORIAL','NEW_MUSIC_FRIDAY','PERSONALIZED')
      
      
              and store_id in ('286')
      
              
          and distributor in ('theorchard','sme','awal')
      
      
      
              
          and (
              playlist_type <> 'EDITORIAL'
              or STORE_ID <> 286
              or exists(
                  select store_playlist_id
                  from FACTS.PROD.playlists_placements_by_isrc_playlist_public
                  where
                      playlists_placements_by_isrc_playlist_public.store_playlist_id = playlists_placements_by_isrc_playlist_private.store_playlist_id
                      and playlists_placements_by_isrc_playlist_public.isrc = playlists_placements_by_isrc_playlist_private.isrc
              )
          )
      
      
      ),
      placements as (
          select
              *
          from
              public_placements
          union all
          select
              *
          from
              private_placements
      ),
      grouped_placements as (
          select
              --selector
              isrc,
              store_id,
              store_playlist_id,
              --public data
                  min(iff(store_id = 1, null, current_position)) as current_position,
                  min(iff(store_id = 1, null, previous_position)) as previous_position,
                  min(peak_position) as peak_position,
                  min(iff(store_id = 1, null, position_change)) as position_change,
                  max(last_added_on_date) as last_added_on_date,
                  max(removed_on) as removed_on,
                  case
                      when store_id = 1 then null
                      else datediff(
                          'days',
                          min(last_added_on_date),
                          ifnull(min(removed_on), current_timestamp())
                      ) + 1
                  end as days_on_playlist,
                  case
                  when store_id = 1 then count(store_playlist_id)
                  else max(storefront_count)
                  end AS storefront_count,
              --private data
              min(first_play) as first_play,
              
          sum(streams_all_time) as streams_all_time,
          sum(streams_last_365_days) as streams_last_365_days,
          sum(streams_last_183_days) as streams_last_183_days,
          sum(streams_last_28_days) as streams_last_28_days,
          sum(streams_last_7_days) as streams_last_7_days,
          sum(streams_last_1_day) as streams_last_1_day,
          sum(completions_streams_all_time) as completions_streams_all_time,
          sum(completions_completions_all_time) as completions_completions_all_time,
          sum(completions_streams_last_365_days) as completions_streams_last_365_days,
          sum(completions_completions_last_365_days) as completions_completions_last_365_days,
          sum(completions_streams_last_183_days) as completions_streams_last_183_days,
          sum(completions_completions_last_183_days) as completions_completions_last_183_days,
          sum(completions_streams_last_28_days) as completions_streams_last_28_days,
          sum(completions_completions_last_28_days) as completions_completions_last_28_days,
          sum(completions_streams_last_7_days) as completions_streams_last_7_days,
          sum(completions_completions_last_7_days) as completions_completions_last_7_days,
          sum(completions_streams_last_1_day) as completions_streams_last_1_day,
          sum(completions_completions_last_1_day) as completions_completions_last_1_day,
      
      
              --metadata
              max(public_playlist_name) as public_playlist_name,
              max(private_playlist_name) as private_playlist_name,
              max(public_playlist_artwork_url) as public_playlist_artwork_url,
              max(private_playlist_artwork_url) as private_playlist_artwork_url,
              array_agg(public_playlist_genres) as public_playlist_genres,
              array_agg(private_playlist_genres) as private_playlist_genres,
              max(public_playlist_track_count) as public_playlist_track_count,
              max(private_playlist_track_count) as private_playlist_track_count,
              max(public_playlist_follower_count) as public_playlist_follower_count,
              max(private_playlist_follower_count) as private_playlist_follower_count,
              max(public_playlist_type) as public_playlist_type,
              max(private_playlist_type) as private_playlist_type,
              max(public_playlist_uri) as public_playlist_uri,
              max(private_playlist_uri) as private_playlist_uri,
              max(public_curator_name) as public_curator_name,
              max(private_curator_name) as private_curator_name,
              max(public_curator_id) as public_curator_id,
              max(private_curator_id) as private_curator_id,
              max(public_curator_country) as public_curator_country,
              max(private_curator_country) as private_curator_country
          from
              placements
          group by
              isrc,
              store_id,
              store_playlist_id
      ),
      coalesced_placements as (
          select
              --selector
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              current_position,
              previous_position,
              peak_position,
              position_change,
              last_added_on_date,
              removed_on,
              days_on_playlist,
              storefront_count,
              -- private data
              first_play,
              
          streams_all_time as streams_all_time,
          streams_last_365_days as streams_last_365_days,
          streams_last_183_days as streams_last_183_days,
          streams_last_28_days as streams_last_28_days,
          streams_last_7_days as streams_last_7_days,
          streams_last_1_day as streams_last_1_day,
          div0(completions_completions_all_time, completions_streams_all_time) as completion_rate_all_time,
          div0(completions_completions_last_365_days, completions_streams_last_365_days) as completion_rate_last_365_days,
          div0(completions_completions_last_183_days, completions_streams_last_183_days) as completion_rate_last_183_days,
          div0(completions_completions_last_28_days, completions_streams_last_28_days) as completion_rate_last_28_days,
          div0(completions_completions_last_7_days, completions_streams_last_7_days) as completion_rate_last_7_days,
          div0(completions_completions_last_1_day, completions_streams_last_1_day) as completion_rate_last_1_day,
      
      
              --metadata
              coalesce(public_playlist_name, private_playlist_name) as playlist_name,
              coalesce(
                  public_playlist_artwork_url,
                  private_playlist_artwork_url
              ) as playlist_artwork_url,
              iff(
                  array_size(get(public_playlist_genres, 0)) > 0,
                  get(public_playlist_genres, 0),
                  get(private_playlist_genres, 0)
              ) as playlist_genres,
              coalesce(
                  public_playlist_track_count,
                  private_playlist_track_count
              ) as playlist_track_count,
              coalesce(
                  public_playlist_follower_count,
                  private_playlist_follower_count
              ) as playlist_follower_count,
              coalesce(public_playlist_type, private_playlist_type) as playlist_type,
              coalesce(public_playlist_uri, private_playlist_uri, '') as playlist_uri,
              coalesce(public_curator_name, private_curator_name, '') as curator_name,
              coalesce(public_curator_id, private_curator_id) as curator_id,
              coalesce(public_curator_country, private_curator_country) as curator_country
          from
          grouped_placements
      ),
      coalesced_placements_by_playlist_id as (
          select
              --selector
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              current_position,
              previous_position,
              peak_position,
              position_change,
              last_added_on_date,
              removed_on,
              days_on_playlist,
              storefront_count,
              -- private data
              first_play,
              streams_all_time,
              streams_last_365_days,
              streams_last_183_days,
              streams_last_28_days,
              streams_last_7_days,
              streams_last_1_day,
              completion_rate_all_time,
              completion_rate_last_365_days,
              completion_rate_last_183_days,
              completion_rate_last_28_days,
              completion_rate_last_7_days,
              completion_rate_last_1_day,
              --metadata
              playlist_name,
              playlist_artwork_url,
              playlist_genres,
              playlist_track_count,
              playlist_follower_count,
              playlist_type,
              playlist_uri,
              curator_name,
              curator_id,
              curator_country
          from coalesced_placements
          where (store_id != 187) OR (store_id = 187 and days_on_playlist is not null)
      ),
      coalesced_placements_amazon_grouped_by_playlist_name as (
          select
              --selector
              isrc,
              store_id,
              max(store_playlist_id) as store_playlist_id,
              -- public data
              max(current_position) as current_position,
              max(previous_position) as previous_position,
              max(peak_position) as peak_position,
              max(position_change) as position_change,
              max(last_added_on_date) as last_added_on_date,
              max(removed_on)  as removed_on,
              max(days_on_playlist)  as days_on_playlist,
              max(storefront_count) as storefront_count,
              -- private data
              min(first_play) as first_play,
              sum(streams_all_time) as streams_all_time,
              sum(streams_last_365_days) as streams_last_365_days,
              sum(streams_last_183_days) as streams_last_183_days,
              sum(streams_last_28_days) as streams_last_28_days,
              sum(streams_last_7_days) as streams_last_7_days,
              sum(streams_last_1_day) as streams_last_1_day,
              div0(sum(completion_rate_all_time), count(completion_rate_all_time)) as completion_rate_all_time,
              div0(sum(completion_rate_last_365_days), count(completion_rate_last_365_days)) as completion_rate_last_365_days,
              div0(sum(completion_rate_last_183_days), count(completion_rate_last_183_days)) as completion_rate_last_183_days,
              div0(sum(completion_rate_last_28_days), count(completion_rate_last_28_days)) as completion_rate_last_28_days,
              div0(sum(completion_rate_last_7_days), count(completion_rate_last_7_days)) as completion_rate_last_7_days,
              div0(sum(completion_rate_last_1_day), count(completion_rate_last_1_day)) as completion_rate_last_1_day,
              --metadata
              playlist_name,
              max(playlist_artwork_url) as playlist_artwork_url,
              max(playlist_genres) as playlist_genres,
              max(playlist_track_count) as playlist_track_count,
              max(playlist_follower_count) as playlist_follower_count,
              max(playlist_type) as playlist_type,
              max(playlist_uri) as playlist_uri,
              max(curator_name) as curator_name,
              max(curator_id) as curator_id,
              curator_country as curator_country,
              count(*) over (partition by isrc, store_id, playlist_name) as countries_count_including_null_values
          from coalesced_placements
          where store_id = 187 and playlist_name != 'Untitled'
          group by isrc, store_id, playlist_name, curator_country
      ),
      cleaned_coalesced_placements_amazon_grouped_by_playlist_name as (
          select
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              current_position,
              previous_position,
              peak_position,
              position_change,
              last_added_on_date,
              removed_on,
              days_on_playlist,
              storefront_count,
              -- private data
              first_play,
              streams_all_time,
              streams_last_365_days,
              streams_last_183_days,
              streams_last_28_days,
              streams_last_7_days,
              streams_last_1_day,
              completion_rate_all_time,
              completion_rate_last_365_days,
              completion_rate_last_183_days,
              completion_rate_last_28_days,
              completion_rate_last_7_days,
              completion_rate_last_1_day,
              --metadata
              playlist_name,
              playlist_artwork_url,
              playlist_genres,
              playlist_track_count,
              playlist_follower_count,
              playlist_type,
              playlist_uri,
              curator_name,
              curator_id,
              curator_country as curator_country
          from coalesced_placements_amazon_grouped_by_playlist_name
          where (curator_country is not null) or (curator_country is null and countries_count_including_null_values = 1)
      ),
      unioned as (
          select * from coalesced_placements_by_playlist_id
          union
          select * from cleaned_coalesced_placements_amazon_grouped_by_playlist_name
      ),
      grouped_unioned as (
          select
              --selector
              isrc,
              store_id,
              store_playlist_id,
              -- public data
              iff(removed_on is null, current_position, null) as current_position,
              previous_position,
              peak_position,
              position_change,
              last_added_on_date,
              removed_on,
              days_on_playlist,
              storefront_count,
              -- private data
              first_play,
              streams_all_time,
              streams_last_365_days,
              streams_last_183_days,
              streams_last_28_days,
              streams_last_7_days,
              streams_last_1_day,
              completion_rate_all_time,
              completion_rate_last_365_days,
              completion_rate_last_183_days,
              completion_rate_last_28_days,
              completion_rate_last_7_days,
              completion_rate_last_1_day,
              --metadata
              playlist_name,
              playlist_artwork_url,
              playlist_genres,
              playlist_track_count,
              playlist_follower_count,
              playlist_type,
              playlist_uri,
              curator_name,
              curator_id,
              curator_country
          from unioned where store_id != 187
          union all
          select
              --selector
              isrc,
              store_id,
              MAX(store_playlist_id) AS store_playlist_id,
              -- public data
              iff(MAX(removed_on) is null, MAX(current_position), null) as current_position,
              MAX(previous_position) AS previous_position,
              MAX(peak_position) AS peak_position,
              MAX(position_change) AS position_change,
              MAX(last_added_on_date) AS last_added_on_date,
              MAX(removed_on)  AS removed_on,
              MAX(days_on_playlist)  AS days_on_playlist,
              MAX(storefront_count) AS storefront_count,
              -- private data
              MIN(first_play) as first_play,
              SUM(streams_all_time) as streams_all_time,
              SUM(streams_last_365_days) as streams_last_365_days,
              SUM(streams_last_183_days) as streams_last_183_days,
              SUM(streams_last_28_days) as streams_last_28_days,
              SUM(streams_last_7_days) as streams_last_7_days,
              SUM(streams_last_1_day) as streams_last_1_day,
              div0(SUM(completion_rate_all_time), count(completion_rate_all_time)) as completion_rate_all_time,
              div0(SUM(completion_rate_last_365_days), count(completion_rate_last_365_days)) as completion_rate_last_365_days,
              div0(SUM(completion_rate_last_183_days), count(completion_rate_last_183_days)) as completion_rate_last_183_days,
              div0(SUM(completion_rate_last_28_days), count(completion_rate_last_28_days)) as completion_rate_last_28_days,
              div0(SUM(completion_rate_last_7_days), count(completion_rate_last_7_days)) as completion_rate_last_7_days,
              div0(SUM(completion_rate_last_1_day), count(completion_rate_last_1_day)) as completion_rate_last_1_day,
              --metadata
              MAX(playlist_name) AS playlist_name,
              MAX(playlist_artwork_url) AS playlist_artwork_url,
              MAX(playlist_genres) AS playlist_genres,
              MAX(playlist_track_count) AS playlist_track_count,
              MAX(playlist_follower_count) AS playlist_follower_count,
              MAX(playlist_type) AS playlist_type,
              MAX(playlist_uri) AS playlist_uri,
              MAX(curator_name) AS curator_name,
              MAX(curator_id) AS curator_id,
              MAX(curator_country) AS curator_country
          from unioned where store_id = 187
          group by
              isrc,
              store_id,
              store_playlist_id
      )select *, count(distinct store_playlist_id) OVER () as total_count
      from grouped_unioned
      where
          true
      
          
          and ((last_added_on_date is not null and removed_on is null) 
              or (streams_last_7_days > 0 
              and playlist_type in ('PERSONALIZED', 'ALGORITHMIC', 'STATION', 'RADIO')))
              --and isrc = 'ARAT92461158'
      
      
      order by
          last_added_on_date  desc  nulls last,
          store_playlist_id asc nulls last ;;
```