view: release_perc_of_revenue {
  derived_table: {
    sql: select
        z.upc,
        z.labelid,
        z.release_total_revenue,
        z.running_total,
        z.label_total_revenue,
        z.running_total/nullif(label_total_revenue,0) as release_perc_of_revenue

        from

          (
           select
           y.labelid,
           y.upc,
           y.release_total_revenue,
           c.label_total_revenue,
           (
              SELECT
              SUM(x.total_gross_revenue_usd)
              FROM
                   (
                  select
                  row_number() over (partition by c.labelid order by c.total_gross_revenue_usd desc) as id,
                  c.labelid,
                  c.upc,
                  c.total_gross_revenue_usd
                  from
                      (SELECT
                      accounting_aggregates.labelid as labelid,
                      accounting_aggregates.releaseid AS upc,
                      COALESCE(SUM(accounting_aggregates.gross_revenue_usd), 0) AS total_gross_revenue_usd
                      FROM prod.bi.accounting_aggregates AS accounting_aggregates
                      WHERE accounting_aggregates.aggregate='Release Level'
                      and {% condition activity_month_filter %} accounting_aggregates.activity_month {% endcondition %}
                      and {% condition labelid_filter %} accounting_aggregates.labelid {% endcondition %}
                      Group by 1,2
                      ) c
                  ) x
                WHERE x.id <= y.id
                and x.labelid = y.labelid

              ) as running_total
            from
                 (select
                  row_number() over (partition by c.labelid order by c.release_total_gross desc) as id,
                  c.labelid,
                  c.upc,
                  c.release_total_gross as release_total_revenue
                  from
                      (SELECT
                      accounting_aggregates.releaseid AS upc,
                      accounting_aggregates.labelid as labelid,
                      COALESCE(SUM(accounting_aggregates.gross_revenue_usd), 0) AS release_total_gross
                      FROM prod.bi.accounting_aggregates AS accounting_aggregates
                      WHERE accounting_aggregates.aggregate='Release Level'
                      and {% condition activity_month_filter %} accounting_aggregates.activity_month {% endcondition %}
                      and {% condition labelid_filter %} accounting_aggregates.labelid {% endcondition %}
                       Group by 1,2
                      ) c
               ) y

          left join

                  (select
                  e.labelid,
                  e.label_total_gross as label_total_revenue
                  from
                      (SELECT
                      accounting_aggregates.labelid as labelid,
                      COALESCE(SUM(accounting_aggregates.gross_revenue_usd), 0) AS label_total_gross
                      FROM prod.bi.accounting_aggregates AS accounting_aggregates
                      WHERE accounting_aggregates.aggregate='Release Level'
                      and {% condition activity_month_filter %} accounting_aggregates.activity_month {% endcondition %}
                      and {% condition labelid_filter %} accounting_aggregates.labelid {% endcondition %}
                      Group by 1) e
                  ) c on y.labelid=c.labelid
          order by y.id
        ) z
        order by 5 desc
       ;;
  }

  filter: activity_month_filter {
    label: "Activity Month Filter for Release % Revenue"
    view_label: "Xtra Advanced Fields"
    description: "Use Release % Revenue Only"
    type: date
  }

  filter: labelid_filter {
    label: "Label ID filter for Release % Revenue"
    view_label: "Xtra Advanced Fields"
    description: "Use Release % Revenue Only"
    type: number
  }

  dimension: upc {
    type: string
    hidden: yes
    sql: ${TABLE}.upc ;;
  }

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

  dimension: release_total_revenue {
    view_label: "Release"
    type: string
    hidden: no
    sql: ${TABLE}.release_total_revenue ;;
  }

  dimension: running_total {
    type: string
    hidden: yes
    sql: ${TABLE}.RUNNING_TOTAL ;;
  }

  dimension: label_total_revenue {
    type: string
    hidden: yes
    sql: ${TABLE}.LABEL_TOTAL_REVENUE ;;
  }

  dimension: release_perc_of_revenue {
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.release_perc_of_revenue ;;
    value_format: "#,##0.00%"
  }

  set: detail {
    fields: [
      upc,
      labelid,
      release_total_revenue,
      running_total,
      label_total_revenue,
      release_perc_of_revenue
    ]
  }
}