Views

name: orders
description: "Order transactions and related data"
datasource: "local"
table: "orders.csv"

entities:
  - name: order
    type: primary
    description: "Individual order transaction"
    key: order_id

  - name: customer
    type: foreign
    description: "Customer who placed the order"
    key: customer_id

dimensions:
  - name: order_id
    type: number
    description: "Unique order identifier"
    expr: id

  - name: order_date
    type: date
    description: "Date when order was placed"
    expr: order_date

  - name: order_status
    type: string
    description: "Current status of the order"
    expr: status
    samples: ["pending", "shipped", "delivered", "cancelled"]

  - name: total_amount
    type: number
    description: "Total order amount"
    expr: total_amount

measures:
  - name: total_orders
    type: count
    description: "Total number of orders"

  - name: total_revenue
    type: sum
    description: "Total revenue from all orders"
    expr: total_amount

  - name: avg_order_value
    type: average
    description: "Average order value"
    expr: total_amount

name: customer_metrics
description: "Aggregated customer metrics and segmentation"
datasource: "analytics"
sql: |
  SELECT 
    customer_id,
    COUNT(DISTINCT order_id) as total_orders,
    SUM(order_amount) as lifetime_value,
    MAX(order_date) as last_order_date,
    MIN(order_date) as first_order_date,
    CASE 
      WHEN SUM(order_amount) >= 1000 THEN 'High Value'
      WHEN SUM(order_amount) >= 500 THEN 'Medium Value'
      ELSE 'Low Value'
    END as value_segment
  FROM orders
  GROUP BY customer_id

entities:
  - name: customer
    type: primary
    description: "Customer entity with aggregated metrics"
    key: customer_id

dimensions:
  - name: customer_id
    type: number
    description: "Customer identifier"
    expr: customer_id

  - name: total_orders
    type: number
    description: "Number of orders placed by customer"
    expr: total_orders

  - name: value_segment
    type: string
    description: "Customer value segmentation"
    expr: value_segment
    samples: ["High Value", "Medium Value", "Low Value"]

measures:
  - name: total_customers
    type: count
    description: "Total number of customers"

  - name: avg_lifetime_value
    type: average
    description: "Average customer lifetime value"
    expr: lifetime_value