view: dt_top_5_stores {
  derived_table: {
    sql: WITH top_5_stores AS (
      SELECT
          ds.storename AS store_name,
          SUM(fs.net_receipt) AS net_revenue
      FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
      LEFT JOIN facts.prod.dim_store AS ds ON fs.storeid = ds.storeid
      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
          ds.storename AS store_name,
          SUM(fs.net_receipt) AS net_revenue
      FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
      LEFT JOIN facts.prod.dim_store AS ds ON fs.storeid = ds.storeid
      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 Stores' AS "store_name", SUM(net_revenue) AS "Net Revenue"
      FROM other
      UNION ALL
      SELECT *
      FROM top_5_stores
       ;;
  }

  dimension: store_name {
    type: string
    sql: ${TABLE}."store_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"
  }
}