view: dt_riaa_certification {
  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,
        fs.activityyear,
        fs.activitymonth,
        fs.storeid,
        fs.transactiontypeid,
        SUM(fs.sales) AS sum_units
    FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
        INNER JOIN content_filter cf ON fs.labelid = cf.labelid
            AND fs.releaseid = cf.releaseid
            AND fs.isrcid = cf.isrcid
    WHERE countryid = 1
        AND {% condition label_id %} fs.labelid {% endcondition %}
        AND transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48,100){% else %}(1,10,19,48,100){% endif %}
        AND storeid IN (1, 4, 36, 120, 136, 173, 187, 194, 213, 217, 286, 339, 348, 365, 399, 426, 446, 496, 497, 504, 526, 531, 548, 553, 580, 595, 616, 677, 708, 719, 1089, 1093, 1290,716)
        AND NOT (storeid = 708 AND transactiontypeid = 10)
        AND NOT (storeid = 708 AND transactiontypeid = 48)
    GROUP BY 1,2,3,4,5,6,7
),
fact_analytics AS (
    SELECT
        'Analytics' AS data_source,
        fa.releaseid,
        fa.isrcid,
        YEAR(fa.download_activity_date) AS activityyear,
        MONTH(fa.download_activity_date) AS activitymonth,
        fa.storeid,
        fa.transactiontypeid,
        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
    WHERE countryid = 1
        AND {% condition label_id %} fa.labelid {% endcondition %}
        AND transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48,100){% else %}(1,10,19,48,100){% endif %}
        AND storeid IN (1, 4, 36, 120, 136, 173, 187, 194, 213, 217, 286, 339, 348, 365, 399, 426, 446, 496, 497, 504, 526, 531, 548, 553, 580, 595, 616, 677, 708, 719, 1089, 1093, 1290,716)
        AND NOT (storeid = 708 AND transactiontypeid = 10)
        AND NOT (storeid = 708 AND transactiontypeid = 48)
    GROUP BY 1,2,3,4,5,6,7
),
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.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
    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
)
SELECT
    ud.data_source,
    ud.releaseid AS upc,
    cf.releasename,
    cf.releasedate,
    ud.isrcid,
    cf.isrc,
    cf.isrcname,
    cf.labelid,
    ud.activityyear||'-'||LPAD(ud.activitymonth,2,'0') AS activity_period,
    ds.storename,
    dtt.transactiontypedesc,
    SUM(ud.units) AS units,
  {% if certification_type._parameter_value == "'releaseid'" %}
    SUM(CASE ud.transactiontypeid
        WHEN 1 THEN (ud.units/1500)
        WHEN 10 THEN (ud.units/1500)
        WHEN 48 THEN (ud.units/1500)
        WHEN 19 THEN (ud.units/10)
        WHEN 100 THEN (ud.units/1500)
        ELSE ud.units END) AS certification_units
  {% elsif certification_type._parameter_value == "'isrcid'" %}
    SUM(CASE ud.transactiontypeid
        WHEN 1 THEN (ud.units/150)
        WHEN 10 THEN (ud.units/150)
        WHEN 48 THEN (ud.units/150)
        WHEN 100 THEN (ud.units/150)
        ELSE ud.units END) AS certification_units
  {% endif %}
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 content_filter cf ON ud.releaseid = cf.releaseid AND ud.isrcid = cf.isrcid
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
ORDER BY activity_period DESC, certification_units DESC  ;;
  }

  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: isrc {
    type: string
    sql: ${TABLE}.isrc ;;
    label: "ISRC"
  }

  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}.transactiontypedesc ;;
  }

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

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

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

  filter: label_id {
    type: number
  }

}