-- 13_customer: Capstone customer contribution
-- Read-only reference query. Setup must be loaded first.
WITH line_totals AS (
SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
SELECT order_id,SUM(amount) AS refunded
FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
SELECT o.order_id,o.customer_id,o.order_date,o.channel,
l.line_sales,COALESCE(r.refunded,0) AS refunded,
l.line_sales-COALESCE(r.refunded,0) AS net_sales
FROM aabha_sql.orders AS o
JOIN line_totals AS l ON l.order_id=o.order_id
LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
AND o.order_date < DATE '2025-04-01'
), totals AS (
SELECT customer_id,COUNT(*) AS completed_orders,SUM(net_sales) AS net_sales
FROM order_net GROUP BY customer_id
)
SELECT c.customer_id,c.customer_name,COALESCE(t.completed_orders,0) AS completed_orders,
COALESCE(t.net_sales,0) AS net_sales
FROM aabha_sql.customers AS c LEFT JOIN totals AS t ON t.customer_id=c.customer_id
ORDER BY net_sales DESC,c.customer_id;
