Migrate from Databricks Metric Views

version: 1.1
source: samples.sales.orders
comment: "Order metrics with customer attributes"
filter: o_orderdate >= '2024-01-01'
joins:
  - name: customer
    source: samples.sales.customer
    on: source.customer_id = customer.customer_id
    cardinality: many_to_one
dimensions:
  - name: Order Status
    expr: order_status
  - name: Order Month
    expr: DATE_TRUNC('MONTH', o_orderdate)
measures:
  - name: Total Revenue
    expr: SUM(amount)
    format: { type: currency }
  - name: Completed Revenue
    expr: SUM(CASE WHEN order_status = 'completed' THEN amount END)
  - name: Avg Order Value
    expr: SUM(amount) / NULLIF(COUNT(1), 0)

source: orders is conn.table('samples.sales.orders') extend {
  primary_key: order_id
  join_one: customer is conn.table('samples.sales.customer') on customer_id

  where: o_orderdate >= @2024-01-01

  dimension:
    #(doc) Order status
    #(index)
    status is order_status

    #(doc) Customer segment
    #(index)
    customer_segment is customer.segment

  measure:
    #(doc) Total revenue in USD
    # currency
    total_revenue is sum(amount)

    #(doc) Revenue from completed orders
    # currency
    completed_revenue is sum(amount) { where: status = 'completed' }

    #(doc) Number of orders
    order_count is count()

    #(doc) Average order value
    # currency
    avg_order_value is total_revenue / order_count

  view:
    #(doc) Revenue by month
    revenue_by_month is {
      group_by: o_orderdate.month
      aggregate: total_revenue
    }
}