view: dt_rolling_date_windows_market_share {
    derived_table: {
      sql:
          {% assign window_offset_in_months = window_offset_in_months._parameter_value %}
          {% assign window_units = window_units._parameter_value %}
          {% assign units_per_window = units_per_window._parameter_value %}
          {% assign number_of_windows = number_of_windows._parameter_value %}
             WITH calculate_units_since AS (
        SELECT
            year||'-'||RIGHT('0'||month,2) AS period,
            FLOOR((( SELECT MAX(accountingperiodid) FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt ) - periodid - {{ window_offset_in_months | default: 1 }}) / {{ window_units | default: 1 }}) + 1 AS units_since
        FROM facts.prod.dim_period
        WHERE units_since >= 1
        ORDER BY 1 DESC
    ),
    calculate_buckets AS (
        SELECT *,
            CEIL(units_since / {{ units_per_window | default: 1 }}) AS bucket_id
        FROM calculate_units_since
        WHERE bucket_id BETWEEN 1 AND {{ number_of_windows | default: 2 }}
    ),
    name_buckets AS (
        SELECT
            bucket_id,
            min_date||' to '||max_date AS bucket_name
            FROM (
                SELECT bucket_id, MIN(period) AS min_date, MAX(period) AS max_date
                FROM calculate_buckets
                GROUP BY 1
            )
    )
    SELECT cb.period, nb.bucket_name
    FROM calculate_buckets cb
    INNER JOIN name_buckets nb ON cb.bucket_id = nb.bucket_id
  ;;
    }

    parameter: window_offset_in_months {
      type: number
      # description: "Rolling Date Windows already implements a two-day offset to account for the full availability of analytics data, which can be extended using this filter; the default value is 0"
    }

    parameter: window_units {
      type: number
      allowed_value: { value: "1" label: "Months" }
      allowed_value: { value: "12" label: "Years" }
      # description: "Sets the unit of measurement for each Rolling Date Window; the default value is Days"
    }

    parameter: units_per_window {
      type: number
      # description: "Sets the number of units of measurements that will be included in each Rolling Date Window; the default value is 1"
    }

    parameter: number_of_windows {
      type: number
      # description: "Sets how many Rolling Date Windows will be generated; the default value is 2"
    }

    dimension: period {
      type: number
      sql: ${TABLE}.period ;;
      primary_key: yes
      hidden: yes
    }

    dimension: rolling_date_windows {
      type: string
      sql: ${TABLE}.bucket_name ;;
      # description: "Customizable period-over-period comparison using the four Filter-Only Fields, or their default values; this Dimension is best used as a Pivot on other Measures"
    }
  }