view: dt_releases_ingested_top_labels {
  derived_table: {
    sql:WITH current_year AS (WITH labels AS (SELECT
    labelid,
    SUM(gross) AS gross
FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt
WHERE accountingperiodid >= (SELECT MAX(accountingperiodid) FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt) - 11
AND labelid IN (

   SELECT
       v.vendor_id
    FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON c.id=v.country
    WHERE {% condition label_country %} c.name {% endcondition %}

)
AND labelid IN (SELECT vendor_id FROM intelligence.dbt_prod.active_vendor_contracts)
GROUP BY 1
ORDER BY gross DESC
LIMIT {% parameter top_label_amt %})

SELECT
    'Current Year' AS timeframe,
    v.vendor_id AS labelid,
    IFF(v.company IS NULL OR v.company  = '',  v.name, v.company) AS label_name,
    c.name AS label_country,
    DATE_FROM_PARTS(YEAR({% parameter context %}), MONTH({% parameter context %}), DAY({% parameter context %})) AS date,
    IFF(r.release_date >= r.ingestion_completed, 'Future Release', 'Past Release') AS ingestion_type,
    mkt.priority AS priority,
    COUNT(DISTINCT r.release_id) AS releases_ingested,
    COUNT(DISTINCT t.isrc) AS tracks_ingested
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track t ON t.release_id=r.release_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_priority mkt ON mkt.release_id=r.release_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON ai.artist_id=r.artist_id
INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON v.vendor_id=ai.vendor_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON c.id=v.country
WHERE ai.vendor_id IN (SELECT labelid FROM labels)
AND {% parameter context %} >= '2020-01-01'
AND r.product_type_id = 1
GROUP BY 1,2,3,4,5,6,7),

last_year AS (

WITH labels AS (SELECT
    labelid,
    SUM(gross) AS gross
FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt
WHERE accountingperiodid >= (SELECT MAX(accountingperiodid) FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt) - 11
AND labelid IN (

   SELECT
       v.vendor_id
    FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
    INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON c.id=v.country
    WHERE  {% condition label_country %} c.name {% endcondition %}

)
AND labelid IN (SELECT vendor_id FROM intelligence.dbt_prod.active_vendor_contracts)

GROUP BY 1
ORDER BY gross DESC
LIMIT {% parameter top_label_amt %})

SELECT
    'Last Year' AS timeframe,
    v.vendor_id AS labelid,
    IFF(v.company IS NULL OR v.company  = '',  v.name, v.company) AS label_name,
    c.name AS label_country,
    DATE_FROM_PARTS(YEAR({% parameter context %}), MONTH({% parameter context %}), DAY({% parameter context %})) AS date,
    IFF(r.release_date >= r.ingestion_completed, 'Future Release', 'Past Release') AS ingestion_type,
    mkt.priority AS priority,
    COUNT(DISTINCT r.release_id) AS releases_ingested,
    COUNT(DISTINCT t.isrc) AS tracks_ingested
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.releases r
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track t ON t.release_id=r.release_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.mkt_priority mkt ON mkt.release_id=r.release_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON ai.artist_id=r.artist_id
INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON v.vendor_id=ai.vendor_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON c.id=v.country
WHERE ai.vendor_id IN (SELECT labelid FROM labels)
AND {% parameter context %} BETWEEN '2019-01-01' AND DATEADD(year, -1, CURRENT_DATE())
AND r.product_type_id = 1
GROUP BY 1,2,3,4,5,6,7
)

SELECT * FROM current_year
UNION ALL
SELECT * FROM last_year
 ;;
  }

  dimension: labelid {
    type: string
    sql: ${TABLE}.labelid ;;
  }

  dimension: label_name {
    type: string
    sql: ${TABLE}.label_name ;;
  }

  dimension: country {
    type: string
    sql: ${TABLE}.label_country ;;
  }

  dimension_group: context {
    type: time
    sql: ${TABLE}.date;;
    timeframes: [
      date,
      week,
      month,
      day_of_month,
      month_num,
      month_name,
      quarter,
      quarter_of_year,
      year,
      day_of_year,
      week_of_year,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year
    ]
  }

  dimension_group: current {
    type: time
    sql: CURRENT_DATE();;
    timeframes: [
      date,
      week,
      month,
      day_of_month,
      month_num,
      month_name,
      quarter,
      quarter_of_year,
      year,
      day_of_year,
      week_of_year,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year
    ]
  }

  dimension: timeframe {
    type: string
    sql: ${TABLE}.timeframe ;;
  }

  dimension: ingestion_type {
    type: string
    sql: ${TABLE}.ingestion_type ;;
  }

  dimension: priority {
    type: string
    sql: ${TABLE}.priority ;;
  }

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

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

 parameter: top_label_amt {
   type: number
  default_value: "1000"
 }

parameter: context {
  type: unquoted

  allowed_value: {
    label: "Ingestion Completed"
    value: "r.ingestion_completed"
  }

  allowed_value: {
    label: "Release Date"
    value: "r.release_date"
  }

}

filter: label_country {
  type: string
}

}