# View: dt_spotify_top_200_aggregates

**View Name:** dt_spotify_top_200_aggregates
**Table Source:** `Derived from: Dbt_Prod.Orch_Spotify_Top_200_Chart_Appearances, Intelligence.Dbt_Prod`
**File Path:** `dt_spotify_top_200_aggregates.view.lkml`

## Overview

- **File Size:** 14856 bytes
- **Lines of Code:** 627
- **Dimensions:** 64
- **Measures:** 15
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `hit` | string |
| `chart_country` | string |
| `avg_acousticness` | number |
| `min_acousticness` | number |
| `max_acousticness` | number |
| `median_acousticness` | number |
| `sd_acousticness` | number |
| `avg_danceability` | number |
| `min_danceability` | number |
| `max_danceability` | number |
| `median_danceability` | number |
| `sd_danceability` | number |
| `avg_duration_secs` | number |
| `min_duration_secs` | number |
| `max_duration_secs` | number |
| `median_duration_secs` | number |
| `sd_duration_secs` | number |
| `avg_energy` | number |
| `min_energy` | number |
| `max_energy` | number |
| `median_energy` | number |
| `sd_energy` | number |
| `avg_instrumentalness` | number |
| `min_instrumentalness` | number |
| `max_instrumentalness` | number |
| `median_instrumentalness` | number |
| `sd_instrumentalness` | number |
| `avg_liveness` | number |
| `min_liveness` | number |
| `max_liveness` | number |
| `median_liveness` | number |
| `sd_liveness` | number |
| `avg_loudness` | number |
| `min_loudness` | number |
| `max_loudness` | number |
| `median_loudness` | number |
| `sd_loudness` | number |
| `avg_speechiness` | number |
| `min_speechiness` | number |
| `max_speechiness` | number |
| `median_speechiness` | number |
| `sd_speechiness` | number |
| `avg_tempo` | number |
| `min_tempo` | number |
| `max_tempo` | number |
| `median_tempo` | number |
| `sd_tempo` | number |
| `avg_valence` | number |
| `min_valence` | number |
| `max_valence` | number |
| `median_valence` | number |
| `sd_valence` | number |
| `min_key` | number |
| `max_key` | number |
| `median_key` | number |
| `mode_key` | number |
| `min_time_signature` | number |
| `max_time_signature` | number |
| `median_time_signature` | number |
| `mode_time_signature` | number |
| `min_mode` | number |
| `max_mode` | number |
| `median_mode` | number |
| `mode_mode` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |
| `avg_acousticness_measure` | average |
| `avg_danceability_measure` | average |
| `avg_duration_measure` | average |
| `avg_energy_measure` | average |
| `min_energy_measure` | average |
| `avg_instrumentalness_measure` | average |
| `avg_liveness_measure` | average |
| `avg_loudness_measure` | average |
| `avg_speechiness_measure` | average |
| `avg_tempo_measure` | average |
| `avg_valence_measure` | average |
| `median_key_measure` | average |
| `mode_key_measure` | average |
| `median_time_signature_measure` | average |

## Derived Table

```sql
sql: select
              to_varchar(hit) as hit,
              case
                  when chart_country = null then 'uncharted'
                  else chart_country
              end as chart_country,
              avg(acousticness) as avg_acousticness,
              min(acousticness) as min_acousticness,
              max(acousticness) as max_acousticness,
              median(acousticness) as median_acousticness,
              stddev(acousticness) as sd_acousticness,

              avg(DANCEABILITY) as avg_DANCEABILITY,
              min(DANCEABILITY) as min_DANCEABILITY,
              max(DANCEABILITY) as max_DANCEABILITY,
              median(DANCEABILITY) as median_DANCEABILITY,
              stddev(DANCEABILITY) as sd_DANCEABILITY,

              avg(DURATION_MS)/1000 as avg_DURATION_SECS,
              min(DURATION_MS)/1000 as min_DURATION_SECS,
              max(DURATION_MS)/1000 as max_DURATION_SECS,
              median(DURATION_MS)/1000 as median_DURATION_SECS,
              stddev(DURATION_MS)/1000 as sd_DURATION_SECS,

              avg(ENERGY) as avg_ENERGY,
              min(ENERGY) as min_ENERGY,
              max(ENERGY) as max_ENERGY,
              median(ENERGY) as median_ENERGY,
              stddev(ENERGY) as sd_ENERGY,


              avg(INSTRUMENTALNESS) as avg_INSTRUMENTALNESS,
              min(INSTRUMENTALNESS) as min_INSTRUMENTALNESS,
              max(INSTRUMENTALNESS) as max_INSTRUMENTALNESS,
              median(INSTRUMENTALNESS) as median_INSTRUMENTALNESS,
              stddev(INSTRUMENTALNESS) as sd_INSTRUMENTALNESS,

              avg(LIVENESS) as avg_LIVENESS,
              min(LIVENESS) as min_LIVENESS,
              max(LIVENESS) as max_LIVENESS,
              median(LIVENESS) as median_LIVENESS,
              stddev(LIVENESS) as sd_LIVENESS,


              avg(LOUDNESS) as avg_LOUDNESS,
              min(LOUDNESS) as min_LOUDNESS,
              max(LOUDNESS) as max_LOUDNESS,
              median(LOUDNESS) as median_LOUDNESS,
              stddev(LOUDNESS) as sd_LOUDNESS,

              avg(SPEECHINESS) as avg_SPEECHINESS,
              min(SPEECHINESS) as min_SPEECHINESS,
              max(SPEECHINESS) as max_SPEECHINESS,
              median(SPEECHINESS) as median_SPEECHINESS,
              stddev(SPEECHINESS) as sd_SPEECHINESS,

              avg(TEMPO) as avg_TEMPO,
              min(TEMPO) as min_TEMPO,
              max(TEMPO) as max_TEMPO,
              median(TEMPO) as median_TEMPO,
              stddev(TEMPO) as sd_TEMPO,

              avg(VALENCE) as avg_VALENCE,
              min(VALENCE) as min_VALENCE,
              max(VALENCE) as max_VALENCE,
              median(VALENCE) as median_VALENCE,
              stddev(VALENCE) as sd_VALENCE,

              min(KEY) as min_KEY,
              max(KEY) as max_KEY,
              median(KEY) as median_KEY,
              mode(KEY) as mode_KEY,

              min(TIME_SIGNATURE) as min_TIME_SIGNATURE,
              max(TIME_SIGNATURE) as max_TIME_SIGNATURE,
              median(TIME_SIGNATURE) as median_TIME_SIGNATURE,
              mode(TIME_SIGNATURE) as mode_TIME_SIGNATURE,

              min(MODE) as min_MODE,
              max(MODE) as max_MODE,
              median(MODE) as median_MODE,
              mode(MODE) as mode_MODE

              from intelligence.dbt_prod.orch_spotify_top_200_chart_appearances
              group by hit, chart_country ;;
```