-- 11_features: Build cutoff based customer features
-- Read-only reference query. Setup must be loaded first.
WITH eligible_orders AS (
SELECT o.order_id,o.customer_id,o.order_date,
SUM(i.quantity*i.unit_price-i.line_discount) AS sales_before_refunds
FROM aabha_sql.orders AS o JOIN aabha_sql.order_items AS i ON i.order_id=o.order_id
WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
AND o.order_date < DATE '2025-03-01'
GROUP BY o.order_id,o.customer_id,o.order_date
), customer_features AS (
SELECT customer_id,COUNT(*) AS completed_orders,
SUM(sales_before_refunds) AS sales_before_refunds,MAX(order_date) AS last_order_date
FROM eligible_orders GROUP BY customer_id
)
SELECT c.customer_id,COALESCE(f.completed_orders,0) AS completed_orders,
COALESCE(f.sales_before_refunds,0) AS sales_before_refunds,
DATE '2025-03-01'-f.last_order_date AS recency_days
FROM aabha_sql.customers AS c LEFT JOIN customer_features AS f ON f.customer_id=c.customer_id
WHERE c.signup_date < DATE '2025-03-01'
ORDER BY c.customer_id;
