view: dt_promusicae_certification_album {
  derived_table: {
    sql:

    with total_sea as(

    WITH sums AS (SELECT
        FA.labelid,
        FA.artistid,
        FA.releaseid,
        FA.trackid,
        FA.transactiontypeid,
        SUM(FA.units) as units
    FROM facts.prod.fact_analytics FA
    WHERE FA.labelid = {% parameter filter_label_id %}
    AND array_contains(to_variant(to_varchar(FA.releaseid)), split({% parameter filter_release_id %}, ','))
    and to_date(FA.download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND FA.transactiontypeid IN (1,10)
    AND FA.countryid = 15
    AND (FA.storeid != 453 OR FA.storeid != 569)
    GROUP BY 1,2,3,4,5),

    ranks_full_length AS (SELECT
        s.*,
        rank() OVER (PARTITION BY releaseid, transactiontypeid ORDER BY units DESC) AS rank
    FROM sums s
    GROUP BY 1,2,3,4,5,6
    ),

    weighted_full_length AS (SELECT
        transactiontypeid,
        COUNT(*),
        SUM(units),
        SUM(units) / COUNT(*) AS weight
    FROM ranks_full_length r
    WHERE rank BETWEEN 3 AND 12
    GROUP BY 1),

    weighted_units_full_length AS (SELECT
        r.releaseid,
        r.artistid,
        r.trackid,
        r.labelid,
        r.transactiontypeid,
        r.units,
        IFF(rank IN (1,2), w.weight, r.units) AS weighted_units,
        r.rank
    FROM ranks_full_length r
    INNER JOIN weighted_full_length w ON w.transactiontypeid=r.transactiontypeid
    WHERE r.rank BETWEEN 1 AND 12),

    certification_full_length as (SELECT
        releaseid,
        trackid,
        artistid,
        labelid,
        transactiontypeid,
        rank,
         CASE
            WHEN transactiontypeid = 1 THEN weighted_units * (1/2000)
            END AS subscription_units,
            CASE
            WHEN transactiontypeid = 10 THEN weighted_units * (1/22000)
        END AS ad_supported_units,
        CASE
            WHEN transactiontypeid = 1 THEN weighted_units * (1/2000)
            WHEN transactiontypeid = 10 THEN weighted_units * (1/22000)
        END AS certification_units
    FROM weighted_units_full_length),



    sea as(select rank, labelid, releaseid, artistid, trackid, round(sum(subscription_units)) as subscription_units,
        round(sum(ad_supported_units)) as ad_supported_units, round(sum(certification_units)) as sea_by_track
    from certification_full_length
    group by 1,2,3,4,5
    order by rank asc)

    select releaseid, labelid,artistid,
    round(sum(subscription_units)) as subscription_units,
        round(sum(ad_supported_units)) as ad_supported_units,
        round(sum(sea_by_track)) as SEA_Score
    from sea group by 1,2,3),


    album_download as (SELECT
        FA.releaseid,
        fa.artistid,
        round(SUM(FA.units)) as album_download
    FROM facts.prod.fact_analytics FA
    WHERE labelid = {% parameter filter_label_id %}
    AND array_contains(to_variant(to_varchar(FA.releaseid)), split({% parameter filter_release_id %}, ','))
    and to_date(download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND transactiontypeid IN (23)
    AND countryid = 15
    GROUP BY 1,2),

physical_sales as (SELECT
    fs.releaseid,
    round(SUM(fs.sales)) as physical_sales
FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
LEFT JOIN facts.prod.dim_period dp ON fs.activityperiodid = dp.periodid
WHERE labelid = {% parameter filter_label_id %}
AND array_contains(to_variant(FS.releaseid), split({% parameter filter_release_id %}, ','))
and date_from_parts(dp.year,dp.month,'01') between {% parameter filter_lower_date %} and
{% parameter filter_upper_date %}
AND transactiontypeid IN (49)
AND countryid = 15
GROUP BY 1),

  ad_streaming_units AS (SELECT
  fa.releaseid,
        SUM(FA.units) as units
    FROM facts.prod.fact_analytics FA
    LEFT JOIN facts.prod.dim_track DT ON FA.isrcid = DT.isrcid
    WHERE FA.labelid = {% parameter filter_label_id %}
    AND array_contains(to_variant(to_varchar(FA.releaseid)), split({% parameter filter_release_id %}, ','))
    and to_date(FA.download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND FA.transactiontypeid IN (10)
    AND FA.countryid = 15
    AND (FA.storeid != 453 OR FA.storeid != 569)
    group by 1
    ),

    sub_streaming_units AS (SELECT
     fa.releaseid,
        SUM(FA.units) as units
    FROM facts.prod.fact_analytics FA
    LEFT JOIN facts.prod.dim_track DT ON FA.isrcid = DT.isrcid
    WHERE FA.labelid = {% parameter filter_label_id %}
    AND array_contains(to_variant(to_varchar(FA.releaseid)), split({% parameter filter_release_id %}, ','))
    and to_date(FA.download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND FA.transactiontypeid IN (1)
    AND FA.countryid = 15
    AND (FA.storeid != 453 OR FA.storeid != 569)
    group by 1
    )


    select
    ts.releaseid,--Including additional fields in the explore
      ts.labelid,
      ts.artistid,
    zeroifnull(ts.ad_supported_units) as ad_supported_units,
    zeroifnull(ts.subscription_units) as subscription_units,
    zeroifnull(ts.sea_score) as SEA_Streaming,
    zeroifnull(ad.album_download) as AD,
    zeroifnull(ps.physical_sales) as PS,
    zeroifnull(asu.units) as ad_streams,
    zeroifnull(ssu.units) as sub_streams
    from total_sea ts
    left join album_download ad on ad.releaseid=ts.releaseid
    left join physical_sales ps on ps.releaseid=ts.releaseid
    left join ad_streaming_units asu on asu.releaseid=ts.releaseid
    left join sub_streaming_units ssu on ssu.releaseid=ts.releaseid;;
  }

  dimension: upc {
    label: "UPC"
    type: string
    sql: ${TABLE}.releaseid ;;
    view_label: "Metadata"
  }
  parameter: filter_release_id {
    type: string
    label: "UPC"
    view_label: "Filter Fields"
  }

  dimension: labelid {
    label: "Label ID"
    type: string
    sql: ${TABLE}.labelid ;;
    view_label: "Metadata"
  }

  dimension: artistid {
    label: "Artist ID"
    type: string
    sql: ${TABLE}.artistid ;;
    view_label: "Metadata"
  }


  parameter: filter_label_id {
    type: string
    label: "Label ID"
    view_label: "Filter Fields"
  }

  parameter: filter_lower_date {
    type: date
    view_label: "Filter Fields"
  }

  parameter: filter_upper_date {
    type: date
    view_label: "Filter Fields"
  }

  dimension: SEA {
    label: "SEA Streaming Score"
    type: number
    sql: ${TABLE}.sea_streaming ;;
    view_label: "Promusicae"
  }

#  dimension: TDE {
#    label: "Track Download Equivilant"
#    type: number
#    sql: ${TABLE}.tde ;;
#    view_label: "Promusicae"
#  }

  dimension: AD {
    label: "Album Downloads"
    type: number
    sql: ${TABLE}.ad ;;
    view_label: "Promusicae"
  }

  dimension: PS {
    label: "Physical Album Sales"
    type: number
    sql: ${TABLE}.ps ;;
    view_label: "Promusicae"
  }

  dimension: ad_streams {
    label: "Ad-Supported Streams"
    type: number
    sql: ${TABLE}.ad_streams ;;
    view_label: "Promusicae"
  }

  dimension: sub_streams {
    label: "Subscription Streams"
    type: number
    sql: ${TABLE}.sub_streams ;;
    view_label: "Promusicae"
  }

  dimension: ad_supported_units {
    label: "Ad Supported Units"
    type: number
    sql: ${TABLE}.ad_supported_units ;;
    view_label: "Promusicae"
  }

  dimension: subscription_units {
    label: "Subscription Units"
    type: number
    sql: ${TABLE}.subscription_units ;;
    view_label: "Promusicae"
  }
}