cubes:
    - name: rates
      sql: "SELECT * FROM rates"
      dimensions:
          - name: currency
            type: string
            sql: currency
            primary_key: true
          - name: fx_rate
            type: number
            sql: fx_rate

    # Declares the join towards `payments`, so a tree that needs this cube
    # roots here rather than at `payments`.
    - name: customers
      sql: "SELECT * FROM customers"
      joins:
          - name: payments
            relationship: one_to_many
            sql: "{customers}.id = {payments.customer_id}"
      dimensions:
          - name: id
            type: string
            sql: id
            primary_key: true
          - name: tier
            type: string
            sql: tier

    - name: merchants
      sql: "SELECT * FROM merchants"
      dimensions:
          - name: id
            type: string
            sql: id
            primary_key: true
          - name: name
            type: string
            sql: name
          - name: commission
            type: number
            sql: commission

    - name: payment_meta
      sql: "SELECT * FROM payment_meta"
      dimensions:
          - name: id
            type: string
            sql: id
            primary_key: true
          - name: payment_id
            type: string
            sql: payment_id
          - name: value
            type: string
            sql: value
      measures:
          - name: count
            type: count

    # Second one-to-many branch off `payments`, so a query can pull two
    # fan-out siblings into one join tree.
    - name: payment_tags
      sql: "SELECT * FROM payment_tags"
      dimensions:
          - name: id
            type: string
            sql: id
            primary_key: true
          - name: payment_id
            type: string
            sql: payment_id
          - name: tag
            type: string
            sql: tag
      measures:
          - name: count
            type: count

    - name: payments
      sql: "SELECT * FROM payments"
      # Stores a measure the planner cannot compute through the measure
      # subquery, at the grain that would force one.
      pre_aggregations:
          - name: max_fx_by_meta_value
            type: rollup
            measures:
                - max_fx_rate
            dimensions:
                - payment_meta.value
      joins:
          - name: payment_meta
            relationship: one_to_many
            sql: "{payments}.id = {payment_meta.payment_id}"
          - name: payment_tags
            relationship: one_to_many
            sql: "{payments}.id = {payment_tags.payment_id}"
          - name: rates
            relationship: many_to_one
            sql: "{payments}.currency = {rates.currency}"
          - name: merchants
            relationship: many_to_one
            sql: "{payments}.merchant_id = {merchants.id}"
      dimensions:
          - name: id
            type: string
            sql: id
            primary_key: true
          - name: status
            type: string
            sql: status
          - name: customer_id
            type: string
            sql: customer_id
      measures:
          - name: count
            type: count_distinct
            sql: id
          - name: success_count
            type: count_distinct
            sql: id
            filters:
              - sql: "{CUBE}.status = 'SUCCESS'"
          - name: total_amount
            type: sum
            sql: amount
          - name: success_amount
            type: sum
            sql: amount
            filters:
              - sql: "{CUBE}.status = 'SUCCESS'"
          # Needs a join to `rates`, and `sum` is not immune to the
          # `payment_meta` fan-out, so it takes the keys-subquery path.
          - name: converted_value
            type: sum
            sql: "{CUBE}.amount / NULLIF({rates.fx_rate}, 0)"
          # Calculated measures over components of the same cube. The
          # components differ in whether they survive row multiplication on
          # their own: count_distinct does, sum does not.
          - name: success_rate
            type: number
            sql: "100.0 * {success_count} / NULLIF({count}, 0)"
          - name: success_amount_rate
            type: number
            sql: "100.0 * {success_amount} / NULLIF({total_amount}, 0)"
          # Needs `merchants` - a different single-cube extension than
          # `converted_value`, so the two land on different join trees.
          - name: commissioned_value
            type: sum
            sql: "{CUBE}.amount * {merchants.commission}"
          # Reads three cubes at once.
          - name: net_value
            type: sum
            sql: "{CUBE}.amount / NULLIF({rates.fx_rate}, 0) * {merchants.commission}"
          # Reaches the second fan-out branch.
          - name: tagged_amount
            type: sum
            sql: amount
            filters:
              - sql: "{payment_tags.tag} = 'vip'"
          # Calculated measure whose components need *different* extra cubes,
          # so its own footprint is their union.
          - name: rate_vs_commission
            type: number
            sql: "100.0 * {converted_value} / NULLIF({commissioned_value}, 0)"
          # Every dependency is a measure, but the expression also reads the
          # cube's own table directly - that read has nowhere to go if the
          # component leaves.
          - name: converted_per_max_amount
            type: number
            sql: "{converted_value} / NULLIF(MAX({CUBE}.amount), 0)"
          # Reaches `rates` past its component measure, so the component cannot
          # be computed on its own: the leftover expression would have nothing
          # to read `fx_rate` from.
          - name: amount_over_fx
            type: number
            sql: "{total_amount} / NULLIF(MAX({rates.fx_rate}), 0)"
          # Reaches `rates` with no component measure at all.
          - name: max_fx_rate
            type: number
            sql: "MAX({rates.fx_rate})"
          # Reaches a cube that owns the join towards `payments`, so this
          # measure's tree roots away from the key cube.
          - name: gold_amount
            type: sum
            sql: amount
            filters:
              - sql: "{customers.tier} = 'gold'"
          # Components root at different cubes - `gold_amount` at `customers`,
          # `total_amount` at `payments` - so splitting them would divide by a
          # different set of rows than evaluating the ratio in one place.
          - name: gold_share
            type: number
            sql: "100.0 * {gold_amount} / NULLIF({total_amount}, 0)"
