view: dt_top_5_countries {
  derived_table: {
    sql: WITH top_5_countries AS (
      SELECT
          dc.countryname AS country_name,
          SUM(fs.net_receipt) AS net_revenue
      FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
      LEFT JOIN facts.prod.dim_country AS dc ON fs.countryid = dc.countryid
      WHERE fs.labelid = {% parameter filter_label_id %}
      AND {% condition filter_accountingyear %} DATE_FROM_PARTS(fs.accountingyear, fs.accountingmonth, 1) {% endcondition %}
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 5),
      other AS (SELECT
          dc.countryname AS country_name,
          SUM(fs.net_receipt) AS net_revenue
      FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
      LEFT JOIN facts.prod.dim_country AS dc ON fs.countryid = dc.countryid
      WHERE fs.labelid = {% parameter filter_label_id %}
      AND {% condition filter_accountingyear %} DATE_FROM_PARTS(fs.accountingyear, fs.accountingmonth, 1) {% endcondition %}
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 500
      OFFSET 5)
      SELECT 'Other Countries' AS "Country Name", SUM(net_revenue) AS "Net Revenue"
      FROM other
      UNION ALL
      SELECT *
      FROM top_5_countries

       ;;
  }


  dimension: country_name {
    type: string
    label: "Country Name"
    sql: ${TABLE}."Country Name" ;;
  }

 measure: net_revenue {
    type: max
    label: "Net Revenue"
    sql: ${TABLE}."Net Revenue" ;;
    value_format: "$0.00"
  }


  parameter: filter_label_id {
    type: number
    view_label: "Filter Fields"
  }

  filter: filter_accountingyear {
    type: date
    view_label: "Filter Fields"
  }
}