The trusted enterprise data context for AI agents.

SELECT
  customers.customer_id AS customer_id,
  customers.full_name AS full_name,
  CASE WHEN COALESCE(f0.fixed_order_count,
  0) > 0 THEN COALESCE(f1.fixed_booked_revenue,
  0) - COALESCE(f2.fixed_refund_amount,
  0) - COALESCE(f3.fixed_product_cost,
  0) + COALESCE(f4.fixed_recovered_product_cost,
  0) - COALESCE(f5.fixed_outbound_shipping,
  0) - COALESCE(f3.fixed_payment_fees,
  0) - COALESCE(f4.fixed_return_handling,
  0) END AS customer_margin,
  COALESCE(COALESCE(f0.fixed_order_count,
  0),
  0) AS customer_orders,
  COALESCE(COALESCE(f0.fixed_returned_order_count,
  0),
  0) * 1.0 / NULLIF(COALESCE(f0.fixed_order_count,
  0),
  0) AS customer_return_rate
FROM crm.dim_customer AS customers
LEFT JOIN (SELECT
  orders.customer_id AS customer_id,
  COUNT(DISTINCT orders.order_id) AS fixed_order_count,
  COUNT(DISTINCT CASE WHEN orders.is_returned THEN orders.order_id END) AS fixed_returned_order_count
FROM (SELECT
  o.*
FROM commerce.fct_orders AS o JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND EXISTS(SELECT
  1
FROM commerce.fct_order_lines AS l
WHERE l.order_id = o.order_id AND l.delivered_qty > 0)) AS orders
GROUP BY orders.customer_id) AS f0
ON customers.customer_id = f0.customer_id
LEFT JOIN (SELECT
  customers.customer_id AS customer_id,
  SUM((lines.gross_sales_cents - lines.discount_cents) * 0.01) AS fixed_booked_revenue
FROM (SELECT
  l.*
FROM commerce.fct_order_lines AS l JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0) AS lines
LEFT JOIN (SELECT
  o.*
FROM commerce.fct_orders AS o JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND EXISTS(SELECT
  1
FROM commerce.fct_order_lines AS l
WHERE l.order_id = o.order_id AND l.delivered_qty > 0)) AS orders
ON lines.order_id = orders.order_id
LEFT JOIN crm.dim_customer AS customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id) AS f1
ON customers.customer_id = f1.customer_id
LEFT JOIN (SELECT
  refunds.customer_id AS customer_id,
  SUM(refunds.merchandise_refund_cents * 0.01) AS fixed_refund_amount
FROM (SELECT
  a.*,
  o.customer_id AS customer_id
FROM finance.fct_refund_events AS a JOIN commerce.fct_order_lines AS l
ON a.order_line_id = l.order_line_id JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0 AND a.status = 'posted' AND a.posted_at < CAST('2026-10-20' AS DATE)) AS refunds
GROUP BY refunds.customer_id) AS f2
ON customers.customer_id = f2.customer_id
LEFT JOIN (SELECT
  customers.customer_id AS customer_id,
  SUM(line_costs.cogs_cents * 0.01) AS fixed_product_cost,
  SUM(line_costs.payment_fee_cents * 0.01) AS fixed_payment_fees
FROM (SELECT
  a.*
FROM finance.fct_order_line_costs AS a JOIN commerce.fct_order_lines AS l
ON a.order_line_id = l.order_line_id JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0) AS line_costs
LEFT JOIN (SELECT
  l.*
FROM commerce.fct_order_lines AS l JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0) AS lines
ON line_costs.order_line_id = lines.order_line_id
LEFT JOIN (SELECT
  o.*
FROM commerce.fct_orders AS o JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND EXISTS(SELECT
  1
FROM commerce.fct_order_lines AS l
WHERE l.order_id = o.order_id AND l.delivered_qty > 0)) AS orders
ON lines.order_id = orders.order_id
LEFT JOIN crm.dim_customer AS customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id) AS f3
ON customers.customer_id = f3.customer_id
LEFT JOIN (SELECT
  customers.customer_id AS customer_id,
  SUM(returns.recoverable_cogs_credit_cents * 0.01) AS fixed_recovered_product_cost,
  SUM(returns.processing_cost_cents * 0.01) AS fixed_return_handling
FROM (SELECT
  a.*
FROM returns.fct_return_events AS a JOIN commerce.fct_order_lines AS l
ON a.order_line_id = l.order_line_id JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0 AND a.status = 'received' AND a.received_at < CAST('2026-10-20' AS DATE)) AS returns
LEFT JOIN (SELECT
  l.*
FROM commerce.fct_order_lines AS l JOIN commerce.fct_orders AS o
ON l.order_id = o.order_id JOIN crm.dim_customer AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND l.delivered_qty > 0) AS lines
ON returns.order_line_id = lines.order_line_id
LEFT JOIN (SELECT
  o.*
FROM commerce.fct_orders AS o JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND EXISTS(SELECT
  1
FROM commerce.fct_order_lines AS l
WHERE l.order_id = o.order_id AND l.delivered_qty > 0)) AS orders
ON lines.order_id = orders.order_id
LEFT JOIN crm.dim_customer AS customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id) AS f4
ON customers.customer_id = f4.customer_id
LEFT JOIN (SELECT
  customers.customer_id AS customer_id,
  SUM(deliveries.shipping_cost_cents * 0.01) AS fixed_outbound_shipping
FROM (SELECT
  d.*
FROM logistics.fct_order_logistics AS d JOIN commerce.fct_orders AS o USING (order_id) JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved') AS deliveries
LEFT JOIN (SELECT
  o.*
FROM commerce.fct_orders AS o JOIN crm.dim_customer AS c USING (customer_id)
WHERE o.status = 'delivered' AND NOT o.is_test AND o.delivered_at >= CAST('2025-09-20' AS DATE) AND o.delivered_at < CAST('2026-09-20' AS DATE) AND NOT c.is_employee AND c.identity_status = 'resolved' AND EXISTS(SELECT
  1
FROM commerce.fct_order_lines AS l
WHERE l.order_id = o.order_id AND l.delivered_qty > 0)) AS orders
ON deliveries.order_id = orders.order_id
LEFT JOIN crm.dim_customer AS customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id) AS f5
ON customers.customer_id = f5.customer_id
WHERE (COALESCE((CASE WHEN COALESCE(f0.fixed_order_count,
  0) > 0 THEN COALESCE(f1.fixed_booked_revenue,
  0) - COALESCE(f2.fixed_refund_amount,
  0) - COALESCE(f3.fixed_product_cost,
  0) + COALESCE(f4.fixed_recovered_product_cost,
  0) - COALESCE(f5.fixed_outbound_shipping,
  0) - COALESCE(f3.fixed_payment_fees,
  0) - COALESCE(f4.fixed_return_handling,
  0) END) >= 3000 AND COALESCE(f0.fixed_order_count,
  0) >= 6 AND (COALESCE(COALESCE(f0.fixed_returned_order_count,
  0),
  0) * 1.0 / NULLIF(COALESCE(f0.fixed_order_count,
  0),
  0)) <= 0.20,
  FALSE)) = TRUE
LIMIT 10000