view: dt_rank_tool {
  derived_table: {
    sql:
      WITH dim_values AS (
        SELECT 'artist' AS source, artistid AS id, artistname AS name FROM facts.prod.dim_artist
          UNION ALL SELECT 'country', countryid, countryname FROM facts.prod.dim_country
          UNION ALL SELECT 'genre', genreid, genrename FROM facts.prod.dim_genre
          UNION ALL SELECT 'label', labelid, labelname FROM facts.prod.dim_label
          UNION ALL SELECT 'store', storeid, storename FROM facts.prod.dim_store
          UNION ALL SELECT 'transactiontype', transactiontypeid, transactiontypedesc FROM facts.prod.dim_transactiontype
          UNION ALL SELECT 'release', releaseid, (releasename||'||'||TO_VARCHAR(releaseid)) FROM facts.prod.dim_release
          UNION ALL SELECT 'isrc', isrcid, (isrcname||'||'||artistname||'||'||isrc||'||'||dr.labelid) FROM facts.prod.dim_isrc di LEFT JOIN facts.prod.dim_release dr on dr.releaseid = di.upc LEFT JOIN facts.prod.dim_artist da ON da.artistid = dr.artistid WHERE track_id != 0
      ),
      rank1 AS (
        SELECT
          RANK() OVER (ORDER BY rank1_metric {{ order_results._parameter_value }}) AS rank1,
          rank1_id,
          rank1_metric
        FROM (
          SELECT
            fs.{{ rank1_by._parameter_value | append: 'id' }} AS rank1_id,
            SUM(gross) AS rank1_metric
          FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
            INNER JOIN facts.prod.dim_release dr ON dr.releaseid = fs.releaseid
          WHERE fs.accountingperiodid = (
            SELECT MAX(accountingperiodid)
            FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt
            )
            AND dr.product_type_id = 1
          GROUP BY 1
          ORDER BY 2 {{ order_results._parameter_value }}
          LIMIT {{ rank1_limit._parameter_value }}
        )
      ),
      rank2 AS (
        SELECT
          RANK() OVER (ORDER BY rank2_metric {{ order_results._parameter_value }}) AS rank,
          rank1_id,
          rank2_id,
          rank2_metric
        FROM (
          SELECT
            fs.{{ rank1_by._parameter_value | append: 'id' }} AS rank1_id,
            fs.{{ rank2_by._parameter_value | append: 'id' }} AS rank2_id,
            SUM(fs.gross) AS rank2_metric
          FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
            INNER JOIN facts.prod.dim_release dr ON dr.releaseid = fs.releaseid
          WHERE fs.accountingperiodid = (
            SELECT MAX(accountingperiodid)
            FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt
            )
            AND dr.product_type_id = 1
          GROUP BY 1,2
        )
      )
      SELECT
        rank1,
        dv1.name AS rank1_value,
        rank1_metric,
        IFF(rank2_local > {{  rank2_limit._parameter_value | minus: 1 }}, {{ rank2_limit._parameter_value }}, rank2_local) AS rank2_local,
        IFF(rank2_local > {{  rank2_limit._parameter_value | minus: 1 }}, NULL, rank2_global) AS rank2_global,
        IFF(rank2_local > {{  rank2_limit._parameter_value | minus: 1 }}, NULL, dv2.name) AS rank2_value,
        SUM(rank2_metric) AS rank2_metric
      FROM (
        SELECT
          r1.rank1,
          r1.rank1_id,
          r1.rank1_metric,
          RANK() OVER (PARTITION BY r1.rank1_id ORDER BY r2.rank) AS rank2_local,
          r2.rank2_id,
          r2.rank2_metric,
          r2.rank AS rank2_global
        FROM rank1 r1
          LEFT JOIN rank2 r2 ON r1.rank1_id = r2.rank1_id
      ) base
        LEFT JOIN dim_values dv1 ON base.rank1_id = dv1.id AND dv1.source = '{{ rank1_by._parameter_value }}'
        LEFT JOIN dim_values dv2 ON base.rank2_id = dv2.id AND dv2.source = '{{ rank2_by._parameter_value }}'
      GROUP BY 1,2,3,4,5,6
      ;;
  }

  parameter: order_results {
    type: unquoted
    allowed_value: { label: "Ascending" value: "ASC" }
    allowed_value: { label: "Descending" value: "DESC" }
  }

  parameter: rank1_by {
    type: unquoted
    allowed_value: { label: "Label" value: "label" }
    allowed_value: { label: "Artist" value: "artist" }
    allowed_value: { label: "Release" value: "release" }
    allowed_value: { label: "Track" value: "isrc" }
    allowed_value: { label: "Genre" value: "genre" }
    allowed_value: { label: "Store" value: "store" }
    allowed_value: { label: "Country" value: "country" }
    allowed_value: { label: "Transaction Type" value: "transactiontype" }
  }

  parameter: rank1_limit {
    type: number
  }

  parameter: rank2_by {
    type: unquoted
    allowed_value: { label: "Label" value: "label" }
    allowed_value: { label: "Artist" value: "artist" }
    allowed_value: { label: "Release" value: "release" }
    allowed_value: { label: "Track" value: "isrc" }
    allowed_value: { label: "Genre" value: "genre" }
    allowed_value: { label: "Store" value: "store" }
    allowed_value: { label: "Country" value: "country" }
    allowed_value: { label: "Transaction Type" value: "transactiontype" }
    }

  parameter: rank2_limit {
    type: number
  }

  dimension: rank1 {
    type: number
    sql: ${TABLE}.rank1 ;;
  }

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

  dimension: rank1_metric {
    type: number
    sql: ${TABLE}.rank1_metric ;;
    value_format_name:  usd
  }

  dimension: rank2_local {
    type: number
    sql: ${TABLE}.rank2_local ;;
  }

  dimension: rank2_global {
    type: number
    sql: ${TABLE}.rank2_global ;;
  }

  dimension: rank2_value {
    type: string
    sql: IFNULL(${TABLE}.rank2_value, 'Other') ;;
    html:
    {% assign list_vals = value | split: "||" %}
    {% if list_vals.size > 1 %}
    <details>
    <summary>{{list_vals[0]}}</summary>
    {{list_vals[1]}}
    </details>
    {{list_vals[2]}}
    </details>
    {{list_vals[3]}}
    </details>
    {% else %}
    {{list_vals}}
    {% endif %}
    ;;
  }

  dimension: rank2_metric {
    type: number
    sql: ${TABLE}.rank2_metric ;;
    value_format_name: usd
  }

}