view: label_country_level_contribution_to_change {
 derived_table: {
  sql:
    with country_level_change as (
    select distinct
      label_id,
      label_name,
      country,
      period_one,
      period_two,
      sum(period_one_streams) as period_one_streams,
      sum(period_two_streams) as period_two_streams,
      sum(period_two_streams) - sum(period_one_streams) as total_change,
      div0((sum(period_two_streams) - sum(period_one_streams)),sum(period_one_streams)) as percent_change
    from {% if period._parameter_value == "'year'" %} intelligence.dbt_prod.label_streams_yoy_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'quarter'" %}  intelligence.dbt_prod.label_streams_qoq_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'month'" %} intelligence.dbt_prod.label_streams_mom_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'week'" %}  intelligence.dbt_prod.label_streams_wow_by_country_store_artist_source_release_track
        {% endif %}
    where
    {% if artist_exclusions._parameter_value == "'yes'" %} {% condition artist %} artist {% endcondition %} {% endif %}
    {% if release_exclusions._parameter_value == "'yes'" %} and {% condition release_name %} release_name {% endcondition %} {% endif %}
    {% if track_exclusions._parameter_value == "'yes'" %} and {% condition track_name %} track_name {% endcondition %} {% endif %}
    group by 1,2,3,4,5
    ),

    label_level_change as (
    select distinct
      label_id,
      label_name,
      period_one,
      period_two,
      sum(period_one_streams) as period_one_streams,
      sum(period_two_streams) as period_two_streams,
      sum(period_two_streams) - sum(period_one_streams) as total_change,
      div0((sum(period_two_streams) - sum(period_one_streams)),sum(period_one_streams)) as percent_change
    from {% if period._parameter_value == "'year'" %} intelligence.dbt_prod.label_streams_yoy_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'quarter'" %}  intelligence.dbt_prod.label_streams_qoq_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'month'" %} intelligence.dbt_prod.label_streams_mom_by_country_store_artist_source_release_track
        {% elsif period._parameter_value == "'week'" %}  intelligence.dbt_prod.label_streams_wow_by_country_store_artist_source_release_track
        {% endif %}
    where
    {% if artist_exclusions._parameter_value == "'yes'" %} {% condition artist %} artist {% endcondition %} {% endif %}
    {% if release_exclusions._parameter_value == "'yes'" %} and {% condition release_name %} release_name {% endcondition %} {% endif %}
    {% if track_exclusions._parameter_value == "'yes'" %} and {% condition track_name %} track_name {% endcondition %} {% endif %}
    group by 1,2,3,4
    ),

    contribution_calculations as (
    select
      country.label_id,
      country.label_name,
      country.country,
      country.period_one,
      country.period_two,
      country.period_one_streams,
      country.period_two_streams,
      country.total_change,
      country.percent_change,
      div0(country.total_change,abs(label.total_change)) as contribution_to_change
    from country_level_change country
      left join label_level_change label on label.label_id = country.label_id
      )

      select
        label_id,
        label_name,
        country,
        period_one,
        period_two,
        period_one_streams,
        period_two_streams,
        total_change,
        percent_change,
        contribution_to_change,
        row_number() over(partition by label_id order by abs(contribution_to_change) desc) as contribution_rank
      from contribution_calculations
      ;;
}

  dimension: primary_key {
    type: string
    sql: concat(${TABLE}.label_id,${TABLE}.country) ;;
    primary_key: yes
  }


parameter: period {
  type: string
  allowed_value: { label: "Year over Year" value: "year"}
  allowed_value: { label: "Quarter over Quarter" value: "quarter"}
  allowed_value: { label: "Month over Month" value: "month"}
  allowed_value: { label: "Week over Week" value: "week"}
}

  parameter: artist_exclusions {
    type: string
    allowed_value: { label: "Yes" value: "yes"}
    allowed_value: { label: "No" value: "no"}
  }

  filter: artist {
    label: "Artist"
    type: string
    case_sensitive: no
  }

  parameter: release_exclusions {
    type: string
    allowed_value: { label: "Yes" value: "yes"}
    allowed_value: { label: "No" value: "no"}
  }

  filter: release_name {
    label: "Release Name"
    type: string
    case_sensitive: no
  }

  parameter: track_exclusions {
    type: string
    allowed_value: { label: "Yes" value: "yes"}
    allowed_value: { label: "No" value: "no"}
  }

  filter: track_name {
    label: "Track Name"
    type: string
    case_sensitive: no
  }

dimension: label_id {
  description: "Unique ID for each label"
  type: number
  sql: ${TABLE}.label_id ;;
}

dimension: label_name {
  description: "The name of the label."
  type: string
  sql: ${TABLE}.label_name ;;
}

dimension: country {
  description: "The name of the country that streams occurred in."
  type: string
  sql: ${TABLE}.country ;;
}

dimension: period_one {
  description: "The first period in the comparison."
  type: string
  sql: ${TABLE}.period_one ;;
}

dimension: period_two {
  description: "The second period in the comparison."
  type: string
  sql: ${TABLE}.period_two ;;
}

measure: period_one_streams {
  description: "The total streams in the first period of the comparison."
  type: sum
  sql: ${TABLE}.period_one_streams ;;
}

measure: period_two_streams {
  description: "The total streams in the second period of the comparison."
  type: sum
  sql: ${TABLE}.period_two_streams ;;
}

dimension: total_change {
  description: "The total streams in the second period of the comparison."
  type: number
  sql: ${TABLE}.total_change ;;
}

dimension: percent_change {
  description: "The total streams in the second period of the comparison."
  type: number
  sql: ${TABLE}.percent_change ;;
}

  measure: contribution_to_change {
    description: "The country's contribution to the total change in label streams."
    type: sum
    value_format: "0%"
    sql: ${TABLE}.contribution_to_change ;;
  }

  dimension: contribution_rank {
    description: "The rank of the absolute value of the country's contribution to the total change in label streams."
    type: number
    sql: ${TABLE}.contribution_rank ;;
  }

}