Daily, Weekly, Monthly Active Users (DAU, WAU, MAU)

cubes:
  - name: active_users
    sql: |
      SELECT user_id, created_at FROM public.orders

    measures:
      - name: monthly_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 30 day
          offset: start

      - name: weekly_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 7 day
          offset: start

      - name: daily_active_users
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: 1 day
          offset: start

      - name: wau_to_mau
        title: WAU to MAU
        type: number
        sql:
          "1.0 * {weekly_active_users} / NULLIF({monthly_active_users}, 0)"
        format: percent

    dimensions:
      - name: created_at
        type: time
        sql: created_at

cube(`active_users`, {
  sql: `SELECT user_id, created_at
    FROM public.orders`,

  measures: {
    monthly_active_users: {
      sql: `user_id`,
      type: `count_distinct`,
      rolling_window: {
        trailing: `30 day`,
        offset: `start`
      }
    },

    weekly_active_users: {
      sql: `user_id`,
      type: `count_distinct`,
      rolling_window: {
        trailing: `7 day`,
        offset: `start`
      }
    },

    daily_active_users: {
      sql: `user_id`,
      type: `count_distinct`,
      rolling_window: {
        trailing: `1 day`,
        offset: `start`
      }
    },

    wau_to_mau: {
      title: `WAU to MAU`,
      sql: `1.0 * ${weekly_active_users} / NULLIF(${monthly_active_users}, 0)`,
      type: `number`,
      format: `percent`
    }
  },

  dimensions: {
    created_at: {
      sql: `created_at`,
      type: `time`
    }
  }
})