SELECT
 test_orders.delivered_on_ts_cast AS "Calculation_3323797312816680970",
 AVG(test_orders.average_amount) AS "avg:avg_first_mile_payment:ok",
 AVG(test_orders.average_amount) AS "avg:avg_last_mile_payment:ok",
 AVG(test_orders.average_amount_calculated) AS "avg:avg_middle_mile_payment:ok",
 AVG(test_orders.average_amount_calculated) AS "avg:avg_pitstop_payment:ok",
 AVG(test_orders.average_amount) AS "avg:avg_sortation_payment:ok",
 AVG(test_orders.average_amount_calculated) AS "avg:avg_total_payment:ok",
 COUNT(test_orders.order_count) AS "cnt:num_parcels:ok",
 SUM(test_orders.order_count) AS "sum:num_parcels:ok"
 FROM
 test_orders
 WHERE
 (
 (
 CAST(test_orders.service_category AS TEXT) = 'outbound'
 )
 AND (
 test_orders.delivered_on_ts_cast IS NOT NULL
 )
 AND (
 (
 (
 (
 CAST(test_orders.delivered_on_ts_cast AS DATE) = DATE('2024年01月08日')
 ) IS NULL
 )
 OR (
 CAST(test_orders.delivered_on_ts_cast AS DATE) <> DATE('2024年01月08日')
 )
 )
 OR (
 CAST(test_orders.delivered_on_ts_cast AS DATE) = DATE('2024年01月08日')
 )
 )
 )
 GROUP BY
 1
 ORDER BY
 1

Arrow error: Compute error: Error: No matching signature for operator = for argument types: TIMESTAMP, DATE

cubes:
 - name: test_orders
 sql: |
 SELECT * FROM (
 -- Current day data (2024年01月15日)
 SELECT 
 1 as id,
 'ORD-001' as order_id,
 'CUST-001' as customer_id,
 120.50 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-15 10:00:00') as created_at,
 TIMESTAMP('2024-01-15 15:30:00') as delivered_at,
 DATE('2024-01-15') as delivered_on
 UNION ALL
 SELECT 
 2 as id,
 'ORD-002' as order_id,
 'CUST-002' as customer_id,
 250.75 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-15 11:00:00') as created_at,
 TIMESTAMP('2024-01-15 16:00:00') as delivered_at,
 DATE('2024-01-15') as delivered_on
 UNION ALL
 SELECT 
 3 as id,
 'ORD-003' as order_id,
 'CUST-001' as customer_id,
 75.25 as amount,
 'pending' as status,
 TIMESTAMP('2024-01-15 12:00:00') as created_at,
 CAST(NULL AS TIMESTAMP) as delivered_at,
 CAST(NULL AS DATE) as delivered_on
 UNION ALL
 SELECT 
 4 as id,
 'ORD-004' as order_id,
 'CUST-003' as customer_id,
 320.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-15 13:00:00') as created_at,
 TIMESTAMP('2024-01-15 18:00:00') as delivered_at,
 DATE('2024-01-15') as delivered_on

 -- Previous day data (2024年01月14日)
 UNION ALL
 SELECT 
 5 as id,
 'ORD-005' as order_id,
 'CUST-001' as customer_id,
 150.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-14 10:00:00') as created_at,
 TIMESTAMP('2024-01-14 15:00:00') as delivered_at,
 DATE('2024-01-14') as delivered_on
 UNION ALL
 SELECT 
 6 as id,
 'ORD-006' as order_id,
 'CUST-002' as customer_id,
 200.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-14 11:00:00') as created_at,
 TIMESTAMP('2024-01-14 16:00:00') as delivered_at,
 DATE('2024-01-14') as delivered_on

 -- Week ago data (2024年01月08日)
 UNION ALL
 SELECT 
 7 as id,
 'ORD-007' as order_id,
 'CUST-001' as customer_id,
 100.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-08 10:00:00') as created_at,
 TIMESTAMP('2024-01-08 15:00:00') as delivered_at,
 DATE('2024-01-08') as delivered_on
 UNION ALL
 SELECT 
 8 as id,
 'ORD-008' as order_id,
 'CUST-002' as customer_id,
 180.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-08 11:00:00') as created_at,
 TIMESTAMP('2024-01-08 16:00:00') as delivered_at,
 DATE('2024-01-08') as delivered_on
 UNION ALL
 SELECT 
 9 as id,
 'ORD-009' as order_id,
 'CUST-003' as customer_id,
 90.00 as amount,
 'completed' as status,
 TIMESTAMP('2024-01-08 12:00:00') as created_at,
 TIMESTAMP('2024-01-08 17:00:00') as delivered_at,
 DATE('2024-01-08') as delivered_on
 ) as mock_orders

 measures:
 # Basic measures (for backward compatibility)
 - name: count
 type: count
 title: "Order Count"
 description: "Total number of orders"
 
 - name: total_amount
 type: sum
 sql: "{amount}"
 title: "Total Revenue"
 description: "Sum of all order amounts"
 
 - name: average_amount
 type: avg
 sql: "{amount}"
 format: currency
 title: "Average Order Value"
 description: "Average revenue per order"
 
 - name: completed_count
 type: count
 title: "Completed Orders"
 description: "Count of completed orders"
 filters:
 - sql: "{CUBE}.status = 'completed'"
 
 - name: order_count
 type: count
 title: "Order Count"
 description: "Count of orders (for num_parcels simulation)"
 
 - name: average_amount_calculated
 type: number
 title: "Average Amount Calculated"
 description: "Total amount divided by order count"
 sql: "{total_amount} / nullif({order_count}, 0)"
 
 # Rolling window measures - Previous day
 - name: count_prev_1_day
 type: count
 title: "Order Count - Previous 1 Day"
 description: "Count of orders from the previous day"
 sql: "{id}"
 rolling_window:
 trailing: 1 day
 offset: start
 
 - name: total_amount_prev_1_day
 type: sum
 sql: "{amount}"
 title: "Total Revenue - Previous 1 Day"
 description: "Total revenue from the previous day"
 rolling_window:
 trailing: 1 day
 offset: start
 
 # Rolling window measures - 7 days
 - name: count_7_day
 type: count
 title: "Order Count - Rolling 7 Days"
 description: "Count of orders over the past 7 days"
 sql: "{id}"
 rolling_window:
 trailing: 7 day
 
 - name: total_amount_7_day
 type: sum
 sql: "{amount}"
 title: "Total Revenue - Rolling 7 Days"
 description: "Total revenue over the past 7 days"
 rolling_window:
 trailing: 7 day
 
 # Day-over-Day percentage change (like num_parcels_pct_change_dod)
 - name: count_pct_change_dod
 type: number
 title: "Day-over-Day % Change in Orders"
 description: >
 Percentage change in order count compared to the previous day.
 Calculation: ((Current day orders - Previous day orders) / Previous day orders) * 100
 sql: |
 (
 ({count} - {count_prev_1_day}) /
 nullif({count_prev_1_day}, 0)
 ) * 100

 - name: revenue_pct_change_dod
 type: number
 title: "Day-over-Day % Change in Revenue"
 description: >
 Percentage change in revenue compared to the previous day.
 Calculation: ((Current day revenue - Previous day revenue) / Previous day revenue) * 100
 sql: |
 (
 ({total_amount} - {total_amount_prev_1_day}) /
 nullif({total_amount_prev_1_day}, 0)
 ) * 100
 
 # Average order value with day-over-day change
 - name: avg_order_value_pct_change_dod
 type: number
 title: "Day-over-Day % Change in Average Order Value"
 description: >
 Percentage change in average order value compared to the previous day.
 Calculation: ((Current AOV - Previous AOV) / Previous AOV) * 100
 sql: |
 ((
 ({total_amount} / nullif({count}, 0)) -
 ({total_amount_prev_1_day} / nullif({count_prev_1_day}, 0))
 ) /
 nullif({total_amount_prev_1_day} / nullif({count_prev_1_day}, 0), 0)) * 100

 dimensions:
 - name: id
 sql: id
 type: number
 primary_key: true
 
 - name: order_id
 sql: order_id
 type: string
 
 - name: customer_id
 sql: customer_id
 type: string
 
 - name: amount
 sql: amount
 type: number
 
 - name: status
 sql: status
 type: string
 
 - name: created_at
 sql: created_at
 type: time
 
 - name: delivered_at
 sql: delivered_at
 type: time
 
 - name: service_category
 sql: |
 CASE 
 WHEN customer_id = 'CUST-001' THEN 'outbound'
 WHEN customer_id = 'CUST-002' THEN 'outbound'
 ELSE 'inbound'
 END
 type: string
 title: "Service Category"
 description: "Service category for the order (outbound/inbound)"
 
 - name: delivered_on
 sql: delivered_on
 type: string
 title: "Delivered On Date"
 description: "Date when the order was delivered"
 
 - name: delivered_on_ts_cast
 sql: timestamp(delivered_on)
 type: time
 title: "Delivered On Timestamp"
 description: "Delivered date wrapped in timestamp()"