bricksmart_metrics

version: 0.1
  source: aibi_demo_catalog.bricksmart.transactions

  joins:
    - name: products
      source: aibi_demo_catalog.bricksmart.products
      on: source.product_id = products.product_id
    - name: users
      source: aibi_demo_catalog.bricksmart.users
      on: source.user_id = users.user_id

  dimensions:
    - name: Transaction Month
      expr: DATE_TRUNC("MONTH", source.transaction_date)
    - name: Transaction Quarter
      expr: DATE_TRUNC("QUARTER", source.transaction_date)
    - name: Category
      expr: products.category
    - name: Subcategory
      expr: products.subcategory
    - name: Store ID
      expr: CAST(source.store_id AS INT)
    - name: Gender
      expr: users.gender
    - name: Age
      expr: users.age
    - name: Region
      expr: users.region

  measures:
    - name: Total Revenue
      expr: SUM(source.transaction_price)
    - name: Order Count
      expr: COUNT(source.transaction_id)
    - name: Total Quantity
      expr: SUM(source.quantity)
    - name: Avg Order Value
      expr: SUM(source.transaction_price) / COUNT(source.transaction_id)
    - name: Unique Customers
      expr: COUNT(DISTINCT source.user_id)
    - name: Gross Profit
      expr: SUM(source.transaction_price - (products.cost_price * source.quantity))
    - name: current_qtr_profit
      expr: SUM(source.transaction_price - (products.cost_price * source.quantity))
      window:
        - order: Transaction Quarter
          range: current
          semiadditive: last
    - name: prev_qtr_profit
      expr: SUM(source.transaction_price - (products.cost_price * source.quantity))
      window:
        - order: Transaction Quarter
          range: trailing 3 month
          semiadditive: last
    - name: qoq_profit_change
      expr: MEASURE(current_qtr_profit) - MEASURE(prev_qtr_profit)