cubes:
  - name: users
    sql: "SELECT 1"
    joins:
      - name: orders
        relationship: one_to_many
        sql: "{users}.id = {orders.user_id}"
    dimensions:
      - name: id
        type: number
        sql: id
        primary_key: true

      - name: userName
        type: string
        sql: "{CUBE}.user_name"

      - name: userType
        type: string
        sql: "{CUBE}.user_type"

      - name: userNameProxy
        type: string
        sql: "{CUBE.userName}"

      - name: secondId
        type: number
        sql: "{CUBE}.secondId"

      - name: complexId
        type: number
        sql: "{id} % {users.secondId}"

      - name: ownedCase
        type: string
        case:
          when:
            - sql: "{CUBE}.user_type = 'admin'"
              label: "Admin"
            - sql: "{CUBE}.user_type = 'user'"
              label: "User"
          else:
            label: "Unknown"

      - name: anotherOwnedCase
        type: string
        case:
          when:
            - sql: "{CUBE}.user_type = 'admin'"
              label: "Admin"
            - sql: "{orders.orderType} = 'user'"
              label: "User"
          else:
            label: "Unknown"

      - name: notOwnedCase
        type: string
        case:
          when:
            - sql: "{CUBE.userType} = 'admin'"
              label: "Admin"
            - sql: "{orders.orderType} = 'user'"
              label: "User"
          else:
            label: "Unknown"

      - name: notOwnedCaseOtherCube
        type: string
        case:
          when:
            - sql: "{orders.orderType} = 'user'"
              label: "User"
          else:
            label: "Unknown"

      - name: latitude
        type: number
        sql: "{CUBE}.latitude"

      - name: longitude
        type: number
        sql: "{CUBE}.longitude"

      - name: ownedGeo
        type: geo
        latitude: latitude
        longitude: longitude

      - name: anotherOwnedGeo
        type: geo
        latitude: "{CUBE.latitude}"
        longitude: "{CUBE}.longitude"

      - name: notOwnedGeo
        type: geo
        latitude: "{CUBE.latitude}"
        longitude: "{CUBE.longitude}"

      - name: notOwnedGeoTypeOtherCube
        type: geo
        latitude: "{CUBE.latitude}"
        longitude: "{CUBE.longitude}"

    measures:
      - name: count
        type: count

      - name: amount
        type: sum
        sql: "amount"

      - name: minPayment
        type: min
        sql: "{users}.payment"

      - name: proxyAmount
        type: number
        sql: "{CUBE.amount}"

      - name: complexCalculation
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"

      - name: complexCalculationOtherCube
        type: number
        sql: "{orders.revenue} / {orders.amount}"

      - name: multiStage
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"
        multi_stage: true

      - name: ownedFilter
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"
        filters:
          - sql: "{CUBE}.amount > 100"

      - name: otherOwnedFilter
        type: number
        sql: "{orders.revenue}"
        filters:
          - sql: "{CUBE}.amount > 100"

      - name: notOwnedFilter
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"
        filters:
          - sql: "{CUBE.amount} > 100"

      - name: notOwnedFilterOtherCube
        type: number
        sql: "{orders.revenue}"
        filters:
          - sql: "{orders.count} > 100"

      - name: ownedDrillFilter
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"
        drill_filters:
          - sql: "{CUBE}.amount > 100"

      - name: otherOwnedDrillFilter
        type: number
        sql: "{orders.revenue}"
        drill_filters:
          - sql: "{CUBE}.amount > 100"

      - name: notOwnedDrillFilter
        type: number
        sql: "{CUBE.amount} / {CUBE.minPayment}"
        drill_filters:
          - sql: "{CUBE.amount} > 100"

      - name: notOwnedDrillFilterOtherCube
        type: number
        sql: "{orders.revenue}"
        drill_filters:
          - sql: "{orders.count} > 100"

  - name: orders
    sql: "SELECT 1"
    dimensions:
      - name: id
        type: number
        sql: id
        primary_key: true
      - name: orderUserName
        type: string
        sql: "{users.userName}"
      - name: orderType
        type: string
        sql: "{CUBE}.order_type"
      - name: latitude
        type: number
        sql: "{CUBE}.latitude"
      - name: longitude
        type: number
        sql: "{CUBE}.longitude"
    measures:
      - name: count
        type: count

      - name: revenue
        type: sum
        sql: "{CUBE}.revenue"
views:
  - name: users_to_orders
    cubes:
      - join_path: users
        includes: "*"
      - join_path: users.orders
        includes:
          - orderUserName
          - orderType
