view: dt_rolling_date_windows_analytics {
  derived_table: {
    sql:
{% assign window_offset_in_days = window_offset_in_days._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
        displaydate AS date,
        FLOOR(DATEDIFF('day', displaydate, CURRENT_DATE-2 - {{ window_offset_in_days | default: 0 }}) / {{ window_units | default: 1 }}) + 1 AS units_since
    FROM facts.prod.dim_day
    WHERE units_since >= 1
    ORDER BY 2 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(date) AS min_date, MAX(date) AS max_date
            FROM calculate_buckets
            GROUP BY 1
        )
)
SELECT cb.date, nb.bucket_name
FROM calculate_buckets cb
INNER JOIN name_buckets nb ON cb.bucket_id = nb.bucket_id ;;
  }

  parameter: window_offset_in_days {
    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: "Days" }
    allowed_value: { value: "7" label: "Weeks" }
    allowed_value: { value: "28" label: "Months" }
    allowed_value: { value: "365" 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: date {
    type: number
    sql: ${TABLE}.date ;;
    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"
  }
}