view: dt_promusicae_certification_single_incl_youtube {
  derived_table: {
    sql:

    with total_sea as(

    WITH sums AS (SELECT
        FA.labelid,
        FA.artistid,
        FA.releaseid,
        FA.trackid,
        FA.transactiontypeid,
        DT.isrc as isrc,
        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(DT.isrc), split({% parameter filter_isrc %}, ',')) --To add multiple values to the user input
    and to_date(FA.download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND FA.transactiontypeid IN (1,10, 38, 37)
    AND FA.countryid = 15
    GROUP BY 1,2,3,4,5,6),


    certification_single as (SELECT
        isrc,
        labelid,
        artistid,
        transactiontypeid,
        --Adding ad supported units and subcri[tion units
        CASE
            WHEN transactiontypeid = 1 THEN units * (1/200)
        END AS subscription_units,
        CASE
            WHEN transactiontypeid IN (10, 38, 37) THEN units * (1/1500)
        END AS ad_supported_units,
        CASE
            WHEN transactiontypeid = 1 THEN units * (1/200)
            WHEN transactiontypeid IN (10, 38, 37) THEN units * (1/1500)
        END AS certification_units
    FROM sums s),


    sea as(select isrc, labelid,
        artistid, round(sum(certification_units)) as sea_by_track,
        round(sum(subscription_units)) as subscription_units,
        round(sum(ad_supported_units)) as ad_supported_units
    from certification_single
    group by 1,2,3)

    select isrc,labelid,
        artistid, round(sum(sea_by_track)) as SEA_Score,
        round(sum(subscription_units)) as subscription_units, --selecting additional fields
        round(sum(ad_supported_units)) as ad_supported_units
    from sea group by 1,2,3),


    single_download as (SELECT
        DT.isrc,
        round(SUM(FA.units)) as single_download
    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(DT.isrc), split({% parameter filter_isrc %}, ','))
    and to_date(download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND transactiontypeid IN (19)
    AND countryid = 15
    GROUP BY 1),

    ad_streaming_units AS (SELECT
    DT.isrc, --Included isrc to have detail on isrc level
        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(DT.isrc), split({% parameter filter_isrc %}, ','))
    and to_date(FA.download_activity_date) between {% parameter filter_lower_date %} and
    {% parameter filter_upper_date %}
    AND FA.transactiontypeid IN (10, 38, 37)
    AND FA.countryid = 15
    group by 1
    ),

    sub_streaming_units AS (SELECT
    DT.isrc,
        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(DT.isrc), split({% parameter filter_isrc %}, ','))
    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
    group by 1
    )


    select
      ts.isrc,--Selecting additional fields to include details 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.single_download) as SD,
      zeroifnull(asu.units) as ad_streams,
      zeroifnull(ssu.units) as sub_streams
    from total_sea ts
    left join single_download ad on ad.isrc=ts.isrc
    left join ad_streaming_units asu on asu.isrc=ts.isrc
    left join sub_streaming_units ssu on ssu.isrc=ts.isrc
    ;;
  }

  dimension: isrc {
    label: "ISRC"
    type: string
    sql: ${TABLE}.isrc ;;
    view_label: "Metadata"
  }

  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_isrc {
    type: string
    view_label: "Filter Fields"
  }

  parameter: filter_release_id {
    type: string
    label: "UPC"
    view_label: "Filter Fields"
  }

  parameter: filter_label_id {
    type: string
    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: SD {
    label: "Single Downloads"
    type: number
    sql: ${TABLE}.sd ;;
    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"
  }

  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: PS {
#    label: "Physical Single Sales"
#    type: number
#    sql: ${TABLE}.ps ;;
#    view_label: "Promusicae"
#  }
}