view: release_level_third_party_publisher_snowflake {
  derived_table: {
    sql: select r.release_id
        , case when type_count=2
               THEN 'both'
               ELSE t.third_party_publisher
               END AS release_third_party_publisher
from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
left join orchard_app_reporting_v2.art_relations_prod_art_relations.track t on t.release_id=r.release_id
left join (
    select r.release_id
            ,count(distinct t.third_party_publisher) as type_count
    from orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
    left join orchard_app_reporting_v2.art_relations_prod_art_relations.track t on t.release_id=r.release_id
    group by 1
        ) track_type_count on track_type_count.release_id=r.release_id
where release_third_party_publisher is not null
group by 1, 2
 ;;
  }

  measure: count {
    type: count
    hidden: yes
    drill_fields: [detail*]
  }

  dimension: release_id {
    type: number
    hidden: yes
    sql: ${TABLE}.RELEASE_ID ;;
  }

  dimension: release_third_party_publisher {
    type: string
    label: "Release Level Third Party Publisher"
    description: "This field aggregates the Track Level field at the Release Level, so if all Tracks on a Release are 'Yes' Third Party Publsher, this field will say 'Yes', same thing for No. If Release has both Yes and No Tracks, 'Yes, No' will appear."
    hidden: no
    sql: CASE WHEN ${TABLE}.RELEASE_THIRD_PARTY_PUBLISHER = 'Y'
              THEN 'Yes'
              WHEN ${TABLE}.RELEASE_THIRD_PARTY_PUBLISHER = 'N'
              THEN 'No'
              WHEN ${TABLE}.RELEASE_THIRD_PARTY_PUBLISHER = 'both'
              THEN 'Yes, No'
              ELSE NULL
              END;;
  }

  set: detail {
    fields: [release_id, release_third_party_publisher]
  }
}