view: dt_global_certifications_by_country {
    derived_table: {
      sql:
          WITH content_filter AS (
              SELECT
                  di.labelid,
                  di.isrcid,
                  di.isrc,
                  di.isrcname,
                  dr.releaseid,
                  dr.releasename,
                  dr.releasedate
              FROM facts.prod.dim_isrc di
                  INNER JOIN facts.prod.dim_release dr ON di.upc = dr.releaseid
              {% if certification_type._parameter_value == "'releaseid'" %} WHERE {% condition content_filter %} releaseid {% endcondition %}
              {% elsif certification_type._parameter_value == "'isrcid'" %} WHERE {% condition content_filter %} isrc {% endcondition %}
              {% endif %}
          ),

          fact_sales AS (
              SELECT
                  'Accounting' AS data_source,
                  fs.releaseid,
                  fs.isrcid,
                  cf.isrcname as track_name,
                  fs.activityyear,
                  fs.activitymonth,
                  fs.storeid,
                  fs.transactiontypeid,
                  fs.countryid,
                  SUM(fs.sales) AS sum_units
              FROM facts.prod.fact_sales fs
                  INNER JOIN content_filter cf ON fs.labelid = cf.labelid
                      AND fs.releaseid = cf.releaseid
                      AND fs.isrcid = cf.isrcid
                  AND {% condition label_id %} fs.labelid {% endcondition %}
                  AND fs.transactiontypeid in (1,10,19,23,48,49,17,9,38,32,37,31) --17 (Subscription Video Streams), 9 (Ad-Supported Video Streams), 38 (Ad-Enabled Video Streams), 32 (Ad-Disabled Video Streams), 37 (Ad-Enabled Audio Streams), 31 (Ad-Disabled Audio Streams)
                  AND fs.storeid not in (1202, 1294) --excludes TikTok
              GROUP BY 1,2,3,4,5,6,7,8,9
          ),

          fact_analytics AS (
              SELECT
                  'Analytics' AS data_source,
                  fa.releaseid,
                  fa.isrcid,
                  cf.isrcname as track_name,
                  YEAR(fa.download_activity_date) AS activityyear,
                  MONTH(fa.download_activity_date) AS activitymonth,
                  fa.storeid,
                  fa.transactiontypeid,
                  fa.countryid,
                  SUM(fa.units) AS sum_units
              FROM facts.prod.fact_analytics fa
                  INNER JOIN content_filter cf ON fa.labelid = cf.labelid
                      AND fa.releaseid = cf.releaseid
                      AND fa.isrcid = cf.isrcid
                  AND {% condition label_id %} fa.labelid {% endcondition %}
                  AND fa.transactiontypeid in (1,10,19,23,48,49,17,9,38,32,37,31) --17 (Subscription Video Streams), 9 (Ad-Supported Video Streams), 38 (Ad-Enabled Video Streams), 32 (Ad-Disabled Video Streams), 37 (Ad-Enabled Audio Streams), 31 (Ad-Disabled Audio Streams)
                  AND fa.storeid not in (1202, 1294) --exclude TikTok
              GROUP BY 1,2,3,4,5,6,7,8,9
          ),

          unit_detail AS (
              SELECT
                  COALESCE(fs.data_source,fa.data_source) AS data_source,
                  COALESCE(fs.releaseid,fa.releaseid) AS releaseid,
                  COALESCE(fs.isrcid,fa.isrcid) AS isrcid,
                  COALESCE(fs.track_name,fa.track_name) AS track_name,
                  COALESCE(fs.activityyear,fa.activityyear) AS activityyear,
                  COALESCE(fs.activitymonth,fa.activitymonth) AS activitymonth,
                  COALESCE(fs.storeid,fa.storeid) AS storeid,
                  COALESCE(fs.transactiontypeid,fa.transactiontypeid) AS transactiontypeid,
                  COALESCE(fs.sum_units,fa.sum_units) AS units,
                  COALESCE(fs.countryid,fa.countryid) AS countryid
              FROM fact_sales fs FULL OUTER JOIN fact_analytics fa
                  ON fs.releaseid = fa.releaseid
                  AND fs.isrcid = fa.isrcid
                  AND fs.activityyear = fa.activityyear
                  AND fs.activitymonth = fa.activitymonth
                  AND fs.storeid = fa.storeid
                  AND fs.transactiontypeid = fa.transactiontypeid
                  AND fs.countryid = fa.countryid
          ),

          -- Calulations for United States and Canada streaming and downloads
 us_ca_streaming_download_sums as (
              SELECT
                ud.track_name,
                case
                  when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
                  when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
                  when ud.transactiontypeid in (48) then 'Mid-Tier'
                  when ud.transactiontypeid in (19) then 'Track Downloads'
                  when ud.transactiontypeid in (23) then 'Album Downloads'
                end as transaction_type,
                dc.countryname,
                SUM(ud.units) AS units
            FROM unit_detail ud
                INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
                INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
                INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
                INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
            where dc.countryname in ('USA','Canada')
            and ud.storeid IN (187,716,1,286,1433,526,348,548,535,502,1213,173,531,1512,120,4,2,708,384,743,214,365,553,1505,677,286,399,1290,453,312,592,1327,569,12,213,1093,339,650,616,568,446,3,496,184) --stored identified from https://whymusicmatters.com/ which was recommended by RIAA as a list.
                  and not (ud.storeid = 708 AND ud.transactiontypeid = 10) --excludes pandora ad-supported
                  and not (ud.storeid = 708 AND ud.transactiontypeid = 48) --excludes pandora mid-tier
                  and ud.transactiontypeid != 49 --no physical
            group by 1,2,3
          ),

us_ca_streaming_download_calc as(

select
    track_name,
    transaction_type,
    countryname,
    units,
    case
        when transaction_type in ('Subscription','Ad-Supported','Mid-Tier') then (units/1500)
        when transaction_type in ('Track Downloads') then (units/10)
        when transaction_type in ('Album Downloads') then units
    end as certification_units
from us_ca_streaming_download_sums
),

 us_ca_physical_calc as (
              SELECT
                ud.track_name,
                'Physical' as transaction_type,
                dc.countryname,
                SUM(ud.units) AS certification_units
            FROM unit_detail ud
                INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
                INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
                INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
                INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
            where dc.countryname in ('USA','Canada')
              and ud.transactiontypeid = 49 --physical only
            group by 1,2,3
),

          -- Calulations for Denmark
dk_calc as (
  SELECT
    ud.track_name,
    case
        when ud.transactiontypeid in (1) then 'Subscription'
        when ud.transactiontypeid in (10) then 'Ad-Supported'
        when ud.transactiontypeid in (48) then 'Mid-Tier'
        when ud.transactiontypeid in (19) then 'Track Downloads'
        when ud.transactiontypeid in (23) then 'Album Downloads'
        when ud.transactiontypeid in (49) then 'Physical'
    end as transaction_type,
    dc.countryname,
    SUM(CASE ud.transactiontypeid
        WHEN 1 THEN (ud.units/1000) --subscription audio streams
        WHEN 10 THEN (ud.units/1000) --ad-supported audio streams
        WHEN 48 THEN (ud.units/1000) --mid-tier audio streams
        WHEN 19 THEN (ud.units/10) --track downloads
        WHEN 23 THEN (ud.units) --album downloads
        WHEN 49 THEN (ud.units) --physical sales
        END) AS certification_units
FROM unit_detail ud
    INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
    INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
    INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
    INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
where dc.countryname in ('Denmark')
and ud.transactiontypeid in (1,10,48,19,23,49)
--and ds.storeid in () --add specific stores
GROUP BY 1,2,3
),

no_calc as (
  SELECT
    ud.track_name,
    case
        when ud.transactiontypeid in (1) then 'Subscription'
        when ud.transactiontypeid in (10) then 'Ad-Supported'
        when ud.transactiontypeid in (48) then 'Mid-Tier'
        when ud.transactiontypeid in (19) then 'Track Downloads'
        when ud.transactiontypeid in (23) then 'Album Downloads'
        when ud.transactiontypeid in (49) then 'Physical'
    end as transaction_type,
    dc.countryname,
    SUM(CASE ud.transactiontypeid
        WHEN 1 THEN (ud.units/1000) --subscription audio streams
        WHEN 10 THEN (ud.units/1000) --ad-supported audio streams
        WHEN 48 THEN (ud.units/1000) --mid-tier audio streams
        WHEN 19 THEN (ud.units/10) --track downloads
        WHEN 23 THEN (ud.units) --album downloads
        WHEN 49 THEN (ud.units) --physical sales
        END) AS certification_units
FROM unit_detail ud
    INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
    INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
    INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
    INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
where dc.countryname in ('Norway')
and (ds.storename in ('Spotify', 'TIDAL', 'iTunes/Apple') and ud.transactiontypeid in (1,10,48,19,23)) --YouTube streams are not included at this time (8-13-2023)
  or ud.transactiontypeid = 49
GROUP BY 1,2,3
),

          -- Australia calculations
aus_streaming_sums AS (
    SELECT
      ud.track_name as track_name,
      case
          when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
          when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
        end as transaction_type,
      SUM(ud.units) AS units
    FROM unit_detail ud
      INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
      INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
      INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
      INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
    where dc.countryname in ('Australia')
        and ud.transactiontypeid in (1,10,31,17,32,37,9,38) --subscription and ad-supported (includes YT and Video transactions types)
    and ds.storename in ('Spotify','iTunes/Apple','YouTube','YouTube Subscription','Deezer') --specific stores
    group by 1,2
  ),

aus_ranks as (
            select
              s.*,
              row_number() OVER (partition by s.transaction_type order by s.units desc) as rank --removed s.upc from partition by
            from aus_streaming_sums s
            group by 1,2,3
          ),

aus_weighted AS (
    SELECT
        transaction_type,
        COUNT(*),
        SUM(units),
        SUM(units) / COUNT(*) AS weight --calculate the replacement for the top 2 track
    FROM aus_ranks r
    WHERE rank BETWEEN 3 AND 10
    GROUP BY 1
),

aus_weighted_units AS (
    SELECT
        r.track_name,
        r.transaction_type,
        r.units,
        IFF(r.rank IN (1,2), w.weight, r.units) AS weighted_units
    FROM aus_ranks r
    INNER JOIN aus_weighted w ON w.transaction_type = r.transaction_type
    WHERE r.rank BETWEEN 1 AND 10 --only include the top 10 tracks
),

aus_streaming_equivalents as (
    select
        'Australia' as countryname,
        track_name,
        transaction_type,
        case
            when transaction_type = 'Subscription' then round((weighted_units / 170),0) --subscription audio streaming
            when transaction_type = 'Ad-Supported' then round((weighted_units / 420),0) --ad-supported audio streaming
        end as certification_units
    from aus_weighted_units
),

aus_physical_download_equivalents as (
    SELECT
              dc.countryname,
              ud.track_name,
              case
                  when ud.transactiontypeid = 19 then 'Track Downloads'
                  when ud.transactiontypeid = 23 then 'Album Downloads'
                  when ud.transactiontypeid = 49 then 'Physical'
              end as transaction_type,
              sum(
                  case
                      when ud.transactiontypeid = 19 then round((ud.units/10),0) --track downloads
                      when ud.transactiontypeid = 23 then (ud.units)
                      when ud.transactiontypeid = 49 then (ud.units)
                  end) as certification_units
            FROM unit_detail ud
              INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
              INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
              INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
              INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
            where dc.countryname in ('Australia') --data for australia only
                and ud.transactiontypeid in (19,23,49) --track downloads, album downloads, and physical
            group by 1,2,3
),

aus_calc as (
    select
        countryname,
        track_name,
        transaction_type,
        round(certification_units,0) as certification_units
    from aus_streaming_equivalents

    union all

    select
        countryname,
        track_name,
        transaction_type,
        round(certification_units,0) as certification_units
    from aus_physical_download_equivalents
),

uk_streaming_sums AS (
            SELECT
            upper(ud.track_name) as track_name,
            case
                when ud.transactiontypeid in (1,31,17,32) then 'Subscription'
                when ud.transactiontypeid in (10,37,9,38) then 'Ad-Supported'
            end as transaction_type,
            SUM(ud.units) AS units
            FROM unit_detail ud
              INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
              INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
              INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
              INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
            where dc.countryname in ('United Kingdom')
                and ud.transactiontypeid in (1,10,31,17,32,37,9,38) --subscription and ad-supported only based on tricia's doc
            --and ds.storename in ('Spotify','iTunes/Apple','YouTube','YouTube Subscription','Deezer') --removed until specific stores are identified
            group by 1,2
          ),

uk_ranks as (
            select
              s.*,
              row_number() OVER (partition by s.transaction_type order by s.units desc) as rank
            from uk_streaming_sums s
            group by 1,2,3
          ),

uk_weighted AS (
    SELECT
        transaction_type,
        COUNT(*),
        SUM(units),
        SUM(units) / COUNT(*) AS weight
    FROM uk_ranks r
    WHERE rank between 3 and 16 --streaming replacements for top two tracks is the average of tracks 3-16
    GROUP BY 1
),

uk_weighted_units AS (
    SELECT
        --r.upc,
        r.track_name,
        r.transaction_type,
        r.units,
        IFF(r.rank IN (1,2), w.weight, r.units) AS weighted_units, --replaces top 2 tracks with the average of tracks ranked 3 - 16
        r.rank
    FROM uk_ranks r
    INNER JOIN uk_weighted w ON w.transaction_type = r.transaction_type --join wieghted units (i.e average units for tracks 3-16) on transaction type to replace top two tracks with average units for 3-16
    where r.rank between 1 and 16 --only the top 16 tracks are included
),

uk_streaming_equivalents as (
    select
        'United Kingdom' as countryname,
        --upc,
        track_name,
        transaction_type,
        -- rank,
        round((weighted_units / 1000),0) as certification_units --streaming equivalent units calculation
    from uk_weighted_units
),

uk_physical_download_equivalents as (
            SELECT DISTINCT
              dc.countryname,
              ud.track_name,
              case
                  when ud.transactiontypeid = 19 then 'Track Downloads'
                  when ud.transactiontypeid = 23 then 'Album Downloads'
                  when ud.transactiontypeid = 49 then 'Physical'
              end as transaction_type,
              sum(case
                when transaction_type = 'Track Downloads' then (units/10)
                when transaction_type = 'Album Downloads' then units
                when transaction_type = 'Physical' then units
             end) as certification_units
            FROM unit_detail ud
              INNER JOIN facts.prod.dim_store ds ON ud.storeid = ds.storeid
              INNER JOIN facts.prod.dim_transactiontype dtt ON ud.transactiontypeid = dtt.transactiontypeid
              INNER JOIN facts.prod.dim_country dc on dc.countryid = ud.countryid
              INNER JOIN content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
            where dc.countryname in ('United Kingdom') --data for UK only
                and ud.transactiontypeid in (19,23,49) --track downloads, album downloads, and physical
            group by 1,2,3
),

uk_calc as (
    select
        countryname,
        track_name,
        transaction_type,
        round(certification_units,0) as certification_units
    from uk_streaming_equivalents

    union all

    select
        countryname,
        track_name,
        transaction_type,
        round(certification_units,0) as certification_units
    from uk_physical_download_equivalents
)



          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from us_ca_streaming_download_calc

          union all

          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from us_ca_physical_calc

          union all

          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from dk_calc

          union all

          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from no_calc

          union all

          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from aus_calc

          union all

          select
            countryname,
            track_name,
            transaction_type,
            round(certification_units,0) as certification_units
          from uk_calc

            ;;
    }

    parameter: certification_type {
      type: string
      allowed_value: { label: "Album" value: "releaseid" }
      # allowed_value: { label: "Track" value: "isrcid" }
    }

    filter: content_filter {
      label: "Content Filter"
      type: string
    }

    # dimension: data_source {
    #   type: string
    #   sql: ${TABLE}.data_source ;;
    # }

    # dimension: upc {
    #   type: string
    #   sql: ${TABLE}.upc ;;
    #   label: "UPC"
    # }

    # dimension: release_name {
    #   type: string
    #   sql: ${TABLE}.releasename ;;
    # }

    # dimension: release_date {
    #   type: date
    #   sql: ${TABLE}.releasedate ;;
    # }

    # dimension: isrcid {
    #   type: string
    #   sql: ${TABLE}.isrcid ;;
    #   hidden: yes
    # }

    dimension: track_name{
      type: string
      sql: ${TABLE}.track_name ;;
      label: "Track Name"
    }

    # dimension: track_name {
    #   type: string
    #   sql: ${TABLE}.isrcname ;;
    # }

    # dimension: activity_period {
    #   type: string
    #   sql: ${TABLE}.activity_period ;;
    # }

    # dimension: store {
    #   type: string
    #   sql: ${TABLE}.storename ;;
    # }

    dimension: transaction_type {
      type: string
      sql: ${TABLE}.transaction_type ;;
    }

    dimension: country_name {
      type: string
      sql: ${TABLE}.countryname ;;
    }

    # measure: units {
    #   type: sum
    #   sql: ${TABLE}.units ;;
    # }

    measure: certification_units {
      type: sum
      sql: ${TABLE}.certification_units ;;
      value_format: "#,##0"
    }

    filter: label_id {
      type: number
    }

  }