| |
| |
|
|
| WITH customer_orders AS ( |
| SELECT |
| customer_id, |
| order_id, |
| order_date, |
| order_amount - ZEROIFNULL(discount_amount) AS net_amount |
| FROM staging.customer_transactions |
| WHERE order_date >= CURRENT_DATE - 365 |
| ), |
| customer_refunds AS ( |
| SELECT |
| customer_id, |
| SUM(refund_amount) AS total_refunds |
| FROM staging.refunds |
| WHERE refund_date >= CURRENT_DATE - 365 |
| GROUP BY customer_id |
| ), |
| customer_summary AS ( |
| SELECT |
| co.customer_id, |
| COUNT(DISTINCT co.order_id) AS order_count, |
| SUM(co.net_amount) AS gross_spend, |
| ZEROIFNULL(cr.total_refunds) AS total_refunds, |
| SUM(co.net_amount) - ZEROIFNULL(cr.total_refunds) AS net_spend, |
| MAX(co.order_date) AS last_order_date, |
| MIN(co.order_date) AS first_order_date |
| FROM customer_orders co |
| LEFT JOIN customer_refunds cr |
| ON co.customer_id = cr.customer_id |
| GROUP BY co.customer_id, cr.total_refunds |
| ), |
| customer_lifetime AS ( |
| SELECT |
| cs.*, |
| DATEDIFF('day', cs.first_order_date, cs.last_order_date) AS customer_lifespan_days, |
| CASE |
| WHEN cs.order_count >= 10 AND cs.net_spend > 1000 THEN 'PLATINUM' |
| WHEN cs.order_count >= 5 AND cs.net_spend > 500 THEN 'GOLD' |
| WHEN cs.order_count >= 2 THEN 'SILVER' |
| ELSE 'BRONZE' |
| END AS loyalty_tier |
| FROM customer_summary cs |
| ) |
| SELECT |
| cl.customer_id, |
| c.customer_name, |
| cl.order_count, |
| cl.gross_spend, |
| cl.total_refunds, |
| cl.net_spend AS customer_lifetime_value, |
| cl.loyalty_tier, |
| cl.last_order_date, |
| cl.customer_lifespan_days |
| FROM customer_lifetime cl |
| JOIN staging.customers c |
| ON cl.customer_id = c.customer_id |
| ORDER BY cl.net_spend DESC; |
|
|
