cubes:
    - name: mdd_events
      sql: "SELECT * FROM mdd_events"
      dimensions:
          - name: id
            type: number
            sql: id
            primary_key: true
          - name: happened_at
            type: time
            sql: happened_at
          - name: recorded_at
            type: time
            sql: recorded_at
          - name: batch_at
            type: time
            sql: batch_at

          # Time dimension built from two owned time dimensions. The shift
          # below covers only one of them, so the stored rollup column
          # cannot represent the shifted values.
          # Plain reference to the shifted dimension. Rendered through to what
          # it points at, so a stored copy of it is never offset.
          - name: happened_at_ref
            type: time
            sql: "{CUBE.happened_at}"

          - name: effective_at
            type: time
            sql: "COALESCE({CUBE.happened_at}, {CUBE.recorded_at})"
      segments:
          # Built from the partially shifted dimension, so a stored copy of it
          # is computed from unshifted values just like a stored dimension.
          - name: recent_effective
            sql: "{CUBE.effective_at} >= '2024-02-01'"

      measures:
          - name: total
            type: sum
            sql: val

          # Reads the shifted time dimension inside its own SQL, so a stored
          # copy is aggregated from unshifted values.
          - name: late_total
            type: sum
            sql: "CASE WHEN {CUBE.happened_at} >= '2024-02-01' THEN val END"

          - name: total_prev_month
            type: number
            sql: "{CUBE.total}"
            multi_stage: true
            time_shift:
                - interval: "1 month"
                  type: prior
                  timeDimension: mdd_events.happened_at

          - name: late_prev_month
            type: number
            sql: "{CUBE.late_total}"
            multi_stage: true
            time_shift:
                - interval: "1 month"
                  type: prior
                  timeDimension: mdd_events.happened_at

      pre_aggregations:
          # Stores the partially shifted dimension under `dimensions:` rather
          # than as its time dimension, so it is substituted by column just the
          # same while the time dimension itself is unshifted.
          - name: total_by_batch_month_with_effective
            type: rollup
            measures:
                - total
            dimensions:
                - effective_at
            time_dimension: mdd_events.batch_at
            granularity: month

          - name: total_by_batch_month_with_ref
            type: rollup
            measures:
                - total
            dimensions:
                - happened_at_ref
            time_dimension: mdd_events.batch_at
            granularity: month

          - name: late_total_by_batch_month
            type: rollup
            measures:
                - late_total
            time_dimension: mdd_events.batch_at
            granularity: month

          - name: total_by_batch_month_with_segment
            type: rollup
            measures:
                - total
            segments:
                - recent_effective
            time_dimension: mdd_events.batch_at
            granularity: month

          - name: total_by_effective_month
            type: rollup
            measures:
                - total
            time_dimension: mdd_events.effective_at
            granularity: month
