# View: dt_project_moments_cluster_group_audio_upper_x_lower_bounds

**View Name:** dt_project_moments_cluster_group_audio_upper_x_lower_bounds
**Table Source:** `Derived from: Audio_Feature_Cluster_Profile, Cluster_Groups, Dbt_Prod.Spotify_Track_Audio_Features, Dev_Engineering, Dev_Engineering.Eimpara + 3 more`
**File Path:** `dt_project_moments_cluster_group_audio_upper_x_lower_bounds.view.lkml`

## Overview

- **File Size:** 27095 bytes
- **Lines of Code:** 748
- **Dimensions:** 41
- **Measures:** 39
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- cluster_group Range Dimensions & Measures

## Dimensions

| Name | Type |
|------|------|
| `cluster_group` | string |
| `cluster_group_range_type` | string |
| `cluster_group_avg_acousticness` | number |
| `cluster_group_stddev_acousticness` | number |
| `cluster_group_avg_danceability` | number |
| `cluster_group_stddev_danceability` | number |
| `cluster_group_avg_energy` | number |
| `cluster_group_stddev_energy` | number |
| `cluster_group_avg_instrumentalness` | number |
| `cluster_group_stddev_instrumentalness` | number |
| `cluster_group_avg_liveness` | number |
| `cluster_group_stddev_liveness` | number |
| `cluster_group_avg_loudness` | number |
| `cluster_group_stddev_loudness` | number |
| `cluster_group_avg_speechiness` | number |
| `cluster_group_stddev_speechiness` | number |
| `cluster_group_avg_tempo` | number |
| `cluster_group_stddev_tempo` | number |
| `cluster_group_avg_duration_ms` | number |
| `cluster_group_stddev_duration_ms` | number |
| `cluster_group_avg_key` | number |
| `cluster_group_stddev_key` | number |
| `cluster_group_avg_mode` | number |
| `cluster_group_stddev_mode` | number |
| `cluster_group_avg_time_signature` | number |
| `cluster_group_stddev_time_signature` | number |
| `cluster_group_avg_valence` | number |
| `cluster_group_stddev_valence` | number |
| `cluster_group_stddev_range_acousticenss` | number |
| `cluster_group_stddev_range_danceability` | number |
| `cluster_group_stddev_range_duration_ms` | number |
| `cluster_group_stddev_range_energy` | number |
| `cluster_group_stddev_range_instrumentalness` | number |
| `cluster_group_stddev_range_key` | number |
| `cluster_group_stddev_range_liveness` | number |
| `cluster_group_stddev_range_loudness` | number |
| `cluster_group_stddev_range_mode` | number |
| `cluster_group_stddev_range_speechiness` | number |
| `cluster_group_stddev_range_tempo` | number |
| `cluster_group_stddev_range_time_signature` | number |
| `cluster_group_stddev_range_valence` | number |

## Measures

| Name | Type |
|------|------|
| `cluster_group_avg_acousticness_measure` | average |
| `cluster_group_stddev_acousticness_measure` | average |
| `cluster_group_avg_danceability_measure` | average |
| `cluster_group_stddev_danceability_measure` | average |
| `cluster_group_avg_energy_measure` | average |
| `cluster_group_stddev_energy_measure` | average |
| `cluster_group_avg_instrumentalness_measure` | average |
| `cluster_group_stddev_instrumentalness_measure` | average |
| `cluster_group_avg_liveness_measure` | average |
| `cluster_group_stddev_liveness_measure` | average |
| `cluster_group_avg_loudness_measure` | average |
| `cluster_group_stddev_loudness_measure` | average |
| `cluster_group_avg_speechiness_measure` | average |
| `cluster_group_stddev_speechiness_measure` | average |
| `cluster_group_avg_tempo_measure` | average |
| `cluster_group_stddev_tempo_measure` | average |
| `cluster_group_avg_duration_ms_measure` | average |
| `cluster_group_stddev_duration_ms_measure` | average |
| `cluster_group_avg_key_measure` | average |
| `cluster_group_stddev_key_measure` | average |
| `cluster_group_avg_mode_measure` | average |
| `cluster_group_stddev_mode_measure` | average |
| `cluster_group_avg_time_signature_measure` | average |
| `cluster_group_stddev_time_signature_measure` | average |
| `cluster_group_avg_valence_measure` | average |
| `cluster_group_stddev_valence_measure` | average |
| `cluster_group_stddev_range_acousticenss_measure` | average |
| `cluster_group_stddev_range_danceability_measure` | average |
| `cluster_group_stddev_range_duration_mss_measure` | average |
| `cluster_group_stddev_range_energy_measure` | average |
| `cluster_group_stddev_range_instrumentalness_measure` | average |
| `cluster_group_stddev_range_key_measure` | average |
| `cluster_group_stddev_range_liveness_measure` | average |
| `cluster_group_stddev_range_loudness_measure` | average |
| `cluster_group_stddev_range_mode_measure` | average |
| `cluster_group_stddev_range_speechiness_measure` | average |
| `cluster_group_stddev_range_tempo_measure` | average |
| `cluster_group_stddev_range_time_signature_measure` | average |
| `cluster_group_stddev_range_valence_measure` | average |

## Derived Table

```sql
sql:
    with cluster_groups as (
select
*,
case
    when fuzzy_cluster_label = 0 then 'A'
    when fuzzy_cluster_label = 1 then 'B'
    when fuzzy_cluster_label = 2 then 'C'
    when fuzzy_cluster_label = 3 then 'D'
    when fuzzy_cluster_label = 4 then 'E'
    when fuzzy_cluster_label = 5 then 'F'
    when fuzzy_cluster_label = 6 then 'G'
    when fuzzy_cluster_label = 7 then 'H'
    when fuzzy_cluster_label = 8 then 'I'
    when fuzzy_cluster_label = 9 then 'J'
else null
end as trending_group
from dev_engineering.eimpara.MOMENTS_CLUSTERS_TABLE_APR_SEPT_22_POSITIVE_SIGN

union all

select
*,
case
    when fuzzy_cluster_label = 0 then 'K'
    when fuzzy_cluster_label = 1 then 'L'
    when fuzzy_cluster_label = 2 then 'M'
    when fuzzy_cluster_label = 3 then 'N'
    when fuzzy_cluster_label = 4 then 'O'
    when fuzzy_cluster_label = 5 then 'P'
    when fuzzy_cluster_label = 6 then 'Q'
    when fuzzy_cluster_label = 7 then 'R'
    when fuzzy_cluster_label = 8 then 'S'
    when fuzzy_cluster_label = 9 then 'T'
else null
end as trending_group
from dev_engineering.eimpara.MOMENTS_CLUSTERS_TABLE_APR_SEPT_22_NEGATIVE_SIGN
),

audio_feature_cluster_profile as (
select
    cg.trending_group as trending_group,
    avg(af.ACOUSTICNESS) as avg_acousticness,
    stddev(af.ACOUSTICNESS) as stddev_acousticness,
    avg(af.ENERGY) as avg_energy,
    stddev(af.ENERGY) as stddev_energy,
    avg(af.INSTRUMENTALNESS) as avg_instrumentalness,
    stddev(af.INSTRUMENTALNESS) as stddev_instrumentalness,
    avg(af.LIVENESS) as avg_liveness,
    stddev(af.LIVENESS) as stddev_liveness,
    avg(af.LOUDNESS) as avg_loudness,
    stddev(af.LOUDNESS) as stddev_loudness,
    avg(af.SPEECHINESS) as avg_speechiness,
    stddev(af.SPEECHINESS) as stddev_speechiness,
    avg(af.TEMPO) as avg_tempo,
    stddev(af.TEMPO) as stddev_tempo,
    avg(af.duration_ms) as avg_duration_ms,
    stddev(af.duration_ms) as stddev_duration_ms,
    avg(af.key) as avg_key,
    stddev(af.key) as stddev_key,
    avg(af.mode) as avg_mode,
    stddev(af.mode) as stddev_mode,
    avg(af.time_signature) as avg_time_signature,
    stddev(af.time_signature) as stddev_time_signature,
    avg(af.danceability) as avg_danceability,
    stddev(af.danceability) as stddev_danceability,
    avg(af.valence) as avg_valence,
    stddev(af.valence) as stddev_valence


from cluster_groups cg
Left Join intelligence.dbt_prod.spotify_track_audio_features af on af.isrc = cg.isrc


group by 1
order by 6),


cluster_group_upper as (
    select
    'Cluster Group Upper' as cluster_group_range_type,
    trending_group,
    avg(avg_acousticness) as cluster_group_avg_acousticness,
    avg(stddev_acousticness) as cluster_group_stddev_acousticness,
    avg(avg_danceability) as cluster_group_avg_danceability,
    avg(stddev_danceability) as cluster_group_stddev_danceability,
    avg(avg_energy) as cluster_group_avg_energy,
    avg(stddev_energy) as cluster_group_stddev_energy,
    avg(avg_instrumentalness) as cluster_group_avg_instrumentalness,
    avg(stddev_instrumentalness) as cluster_group_stddev_instrumentalness,
    avg(avg_liveness) as cluster_group_avg_liveness,
    avg(stddev_liveness) as cluster_group_stddev_liveness,
    avg(avg_loudness) as cluster_group_avg_loudness,
    avg(stddev_loudness) as cluster_group_stddev_loudness,
    avg(avg_speechiness) as cluster_group_avg_speechiness,
    avg(stddev_speechiness) as cluster_group_stddev_speechiness,
    avg(avg_tempo) as cluster_group_avg_tempo,
    avg(stddev_tempo) as cluster_group_stddev_tempo,
    avg(avg_duration_ms) as cluster_group_avg_duration_ms,
    avg(stddev_duration_ms) as cluster_group_stddev_duration_ms,
    avg(avg_key) as cluster_group_avg_key,
    avg(stddev_key) as cluster_group_stddev_key,
    avg(avg_mode) as cluster_group_avg_mode,
    avg(stddev_mode) as cluster_group_stddev_mode,
    avg(avg_time_signature) as cluster_group_avg_time_signature,
    avg(stddev_time_signature) as cluster_group_stddev_time_signature,
    avg(avg_valence) as cluster_group_avg_valence,
    avg(stddev_valence) as cluster_group_stddev_valence,
    iff(((cluster_group_avg_acousticness + cluster_group_stddev_acousticness) > 1), 1, (cluster_group_avg_acousticness + cluster_group_stddev_acousticness))as cluster_group_stddev_range_acousticenss,
    iff(((cluster_group_avg_danceability + cluster_group_stddev_danceability) > 1), 1, (cluster_group_avg_danceability + cluster_group_stddev_danceability))as cluster_group_stddev_range_danceability,
    (cluster_group_avg_duration_ms + cluster_group_stddev_duration_ms)as cluster_group_stddev_range_duration_ms,
    iff(((cluster_group_avg_energy + cluster_group_stddev_energy) > 1), 1, (cluster_group_avg_energy + cluster_group_stddev_energy))as cluster_group_stddev_range_energy,
    iff(((cluster_group_avg_instrumentalness + cluster_group_stddev_instrumentalness) > 1), 1, (cluster_group_avg_instrumentalness + cluster_group_stddev_instrumentalness))as cluster_group_stddev_range_instrumentalness,
    (cluster_group_avg_key + cluster_group_stddev_key)as cluster_group_stddev_range_key,
    iff(((cluster_group_avg_liveness + cluster_group_stddev_liveness) > 1), 1, (cluster_group_avg_liveness + cluster_group_stddev_liveness))as cluster_group_stddev_range_liveness,
    iff(((cluster_group_avg_loudness + cluster_group_stddev_loudness) > 1), 1, (cluster_group_avg_loudness + cluster_group_stddev_loudness))as cluster_group_stddev_range_loudness,
    iff(((cluster_group_avg_mode + cluster_group_stddev_mode) > 1), 1, (cluster_group_avg_mode + cluster_group_stddev_mode))as cluster_group_stddev_range_mode,
    iff(((cluster_group_avg_speechiness + cluster_group_stddev_speechiness) > 1), 1, (cluster_group_avg_speechiness + cluster_group_stddev_speechiness))as cluster_group_stddev_range_speechiness,
    (cluster_group_avg_tempo + cluster_group_stddev_tempo)as cluster_group_stddev_range_tempo,
    (cluster_group_avg_time_signature + cluster_group_stddev_time_signature)as cluster_group_stddev_range_time_signature,
    iff(((cluster_group_avg_valence + cluster_group_stddev_valence) > 1), 1, (cluster_group_avg_valence + cluster_group_stddev_valence))as cluster_group_stddev_range_valence
    from audio_feature_cluster_profile
    group by 1,2
),

cluster_group_lower as (
    select
    'Cluster Group Lower' as cluster_group_range_type,
    trending_group,
    avg(avg_acousticness) as cluster_group_avg_acousticness,
    avg(stddev_acousticness) as cluster_group_stddev_acousticness,
    avg(avg_danceability) as cluster_group_avg_danceability,
    avg(stddev_danceability) as cluster_group_stddev_danceability,
    avg(avg_energy) as cluster_group_avg_energy,
    avg(stddev_energy) as cluster_group_stddev_energy,
    avg(avg_instrumentalness) as cluster_group_avg_instrumentalness,
    avg(stddev_instrumentalness) as cluster_group_stddev_instrumentalness,
    avg(avg_liveness) as cluster_group_avg_liveness,
    avg(stddev_liveness) as cluster_group_stddev_liveness,
    avg(avg_loudness) as cluster_group_avg_loudness,
    avg(stddev_loudness) as cluster_group_stddev_loudness,
    avg(avg_speechiness) as cluster_group_avg_speechiness,
    avg(stddev_speechiness) as cluster_group_stddev_speechiness,
    avg(avg_tempo) as cluster_group_avg_tempo,
    avg(stddev_tempo) as cluster_group_stddev_tempo,
    avg(avg_duration_ms) as cluster_group_avg_duration_ms,
    avg(stddev_duration_ms) as cluster_group_stddev_duration_ms,
    avg(avg_key) as cluster_group_avg_key,
    avg(stddev_key) as cluster_group_stddev_key,
    avg(avg_mode) as cluster_group_avg_mode,
    avg(stddev_mode) as cluster_group_stddev_mode,
    avg(avg_time_signature) as cluster_group_avg_time_signature,
    avg(stddev_time_signature) as cluster_group_stddev_time_signature,
    avg(avg_valence) as cluster_group_avg_valence,
    avg(stddev_valence) as cluster_group_stddev_valence,
    iff(((cluster_group_avg_acousticness - cluster_group_stddev_acousticness) < 0), 0, (cluster_group_avg_acousticness - cluster_group_stddev_acousticness))as cluster_group_stddev_range_acousticenss,
    iff(((cluster_group_avg_danceability - cluster_group_stddev_danceability) < 0), 0, (cluster_group_avg_danceability - cluster_group_stddev_danceability))as cluster_group_stddev_range_danceability,
    iff(((cluster_group_avg_duration_ms - cluster_group_stddev_duration_ms) < 0), 0, (cluster_group_avg_duration_ms - cluster_group_stddev_duration_ms))as cluster_group_stddev_range_duration_ms,
    iff(((cluster_group_avg_energy - cluster_group_stddev_energy) < 0), 0, (cluster_group_avg_energy - cluster_group_stddev_energy))as cluster_group_stddev_range_energy,
    iff(((cluster_group_avg_instrumentalness - cluster_group_stddev_instrumentalness) < 0), 0, (cluster_group_avg_instrumentalness - cluster_group_stddev_instrumentalness))as cluster_group_stddev_range_instrumentalness,
    (cluster_group_avg_key - cluster_group_stddev_key)as cluster_group_stddev_range_key,
    iff(((cluster_group_avg_liveness - cluster_group_stddev_liveness) < 0), 0, (cluster_group_avg_liveness - cluster_group_stddev_liveness))as cluster_group_stddev_range_liveness,
    iff(((cluster_group_avg_loudness - cluster_group_stddev_loudness) < 0), 0, (cluster_group_avg_loudness - cluster_group_stddev_loudness))as cluster_group_stddev_range_loudness,
    iff(((cluster_group_avg_mode - cluster_group_stddev_mode) < 0), 0, (cluster_group_avg_mode - cluster_group_stddev_mode))as cluster_group_stddev_range_mode,
    iff(((cluster_group_avg_speechiness - cluster_group_stddev_speechiness) < 0), 0, (cluster_group_avg_speechiness - cluster_group_stddev_speechiness))as cluster_group_stddev_range_speechiness,
    iff(((cluster_group_avg_tempo - cluster_group_stddev_tempo) < 0), 0, (cluster_group_avg_tempo - cluster_group_stddev_tempo))as cluster_group_stddev_range_tempo,
    iff(((cluster_group_avg_time_signature - cluster_group_stddev_time_signature) < 0), 0, (cluster_group_avg_time_signature - cluster_group_stddev_time_signature))as cluster_group_stddev_range_time_signature,
    iff(((cluster_group_avg_valence - cluster_group_stddev_valence) < 0), 0, (cluster_group_avg_valence - cluster_group_stddev_valence))as cluster_group_stddev_range_valence
    from audio_feature_cluster_profile
    group by 1,2
)

select
*
from cluster_group_upper
union
select
*
from cluster_group_lower
    ;;
```