File size: 1,841 Bytes
12eff8e | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | -- Legacy customer lifetime value calculation
-- 4 CTEs, multiple joins, window functions
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;
|