view: dt_aria_streaming_score {
  derived_table: {
    sql:

with total_sea as(

WITH sums AS (SELECT
    labelid,
    artistid,
    releaseid,
    trackid,
    transactiontypeid,
    SUM(units) as units
FROM facts.prod.fact_analytics
WHERE labelid = {% parameter filter_label_id %}
AND releaseid = {% parameter filter_release_id %}
and to_date(download_activity_date) between {% parameter filter_lower_date %} and
{% parameter filter_upper_date %}
AND transactiontypeid IN (1,10)
AND countryid = 8
GROUP BY 1,2,3,4,5),

ranks 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 AS (SELECT
    transactiontypeid,
    COUNT(*),
    SUM(units),
    SUM(units) / COUNT(*) AS weight
FROM ranks r
WHERE rank BETWEEN 3 AND 10
GROUP BY 1),


weighted_units AS (SELECT
    r.releaseid,
    r.trackid,
    r.transactiontypeid,
    r.units,
    IFF(rank IN (1,2), w.weight, r.units) AS weighted_units,
    r.rank
FROM ranks r
INNER JOIN weighted w ON w.transactiontypeid=r.transactiontypeid
WHERE r.rank BETWEEN 1 AND 10),


certification as (SELECT
    releaseid
    trackid,
    transactiontypeid,
    rank,
    CASE
        WHEN transactiontypeid = 1 THEN weighted_units * (1/170)
        WHEN transactiontypeid = 10 THEN weighted_units * (1/420)
    END AS certification_units
FROM weighted_units),


sea as(select rank, trackid, round(sum(certification_units)) as sea_by_track
from certification
group by rank, trackid
order by rank asc)

select round(avg(sea_by_track)) as SEA_Score
from sea),



track_equiv as (SELECT
    releaseid,
    round((SUM(units)/10)) as track_download_equivilant
FROM facts.prod.fact_analytics
WHERE labelid = {% parameter filter_label_id %}
AND releaseid = {% parameter filter_release_id %}
and to_date(download_activity_date) between {% parameter filter_lower_date %} and
{% parameter filter_upper_date %}
AND transactiontypeid IN (19)
AND countryid = 8
GROUP BY 1),

album_download as (SELECT
    releaseid,
    round(SUM(units)) as album_download
FROM facts.prod.fact_analytics
WHERE labelid = {% parameter filter_label_id %}
AND releaseid = {% 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 = 8
GROUP BY 1),


physical_sales as (SELECT
    releaseid,
    round(SUM(units)) as physical_sales
FROM facts.prod.fact_analytics
WHERE labelid = {% parameter filter_label_id %}
AND releaseid = {% parameter filter_release_id %}
and to_date(download_activity_date) between {% parameter filter_lower_date %} and
{% parameter filter_upper_date %}
AND transactiontypeid IN (49)
AND countryid = 8
GROUP BY 1)

select zeroifnull(ts.sea_score) as SEA,
zeroifnull(te.track_download_equivilant) as TDE,
zeroifnull(ad.album_download) as AD,
zeroifnull(ps.physical_sales) as PS
from total_sea ts
full join track_equiv te on true
full join album_download ad on true
full join physical_sales ps on true;;
  }

  parameter: filter_release_id {
    type: string
    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 Score"
    type: number
    sql: ${TABLE}.sea ;;
    view_label: "Aria"
  }

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

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

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