view: dt_amprofon_certification_v2 {
  # Or, you could make this view a derived table, like this:
  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 fs.countryid = 7
              AND {% condition label_id %} fs.labelid {% endcondition %}
              AND fs.transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48){% else %}(1,10,17,19,38,48){% endif %}
              AND fs.storeid IN (1, 4, 117, 286, 348, 399, 496, 719, 1290)
              AND NOT (fs.storeid = 708 AND fs.transactiontypeid = 10)
              AND NOT (fs.storeid = 708 AND fs.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 fa.countryid = 7
              AND {% condition label_id %} fa.labelid {% endcondition %}
              AND fa.transactiontypeid IN {% if certification_type._parameter_value == "'releaseid'" %}(1,10,19,23,48){% else %}(1,10,17,19,38,48){% endif %}
              AND fa.storeid IN (1, 4, 117, 286, 348, 399, 496, 719, 1290)
              AND NOT (fa.storeid = 708 AND fa.transactiontypeid = 10)
              AND NOT (fa.storeid = 708 AND fa.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,
          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/3000)
              WHEN 10 THEN (ud.units/3000)
              WHEN 48 THEN (ud.units/3000)
              WHEN 19 THEN (ud.units/10)
              WHEN 17 THEN (ud.units / 15000)
              WHEN 38 THEN (ud.units / 15000)
              ELSE ud.units END) AS certification_units
        {% elsif certification_type._parameter_value == "'isrcid'" %}
          SUM(CASE ud.transactiontypeid
              WHEN 1 THEN (ud.units)
              WHEN 10 THEN (ud.units)
              WHEN 48 THEN (ud.units)
              WHEN 19 THEN (ud.units * 300)
              WHEN 17 THEN (ud.units / 5)
              WHEN 38 THEN (ud.units / 5)
              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
      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 ;;
  }

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

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

  filter: label_id {
    type: number
  }
}