Customer 360 View

1-- 1. Customer 360 — Single Customer View
2WITH customer_loans AS (
3  SELECT 
4      customer_id,
5      COUNT(*) AS total_applications,
6      SUM(CASE WHEN status = 'Approved' THEN 1 ELSE 0 END) AS approved_loans,
7      SUM(CASE WHEN default_flag = 1 THEN 1 ELSE 0 END) AS default_count,
8      SUM(loan_amount) AS total_loan_amount,
9      AVG(credit_score) AS avg_credit_score,
10      AVG(dti_ratio) AS avg_dti_ratio,
11      COUNT(DISTINCT product_id) AS num_products,
12      MIN(application_date) AS first_loan_date,
13      MAX(application_date) AS last_loan_date
14  FROM fact_loan_application
15  GROUP BY customer_id
16),
17customer_txn AS (
18  SELECT 
19      customer_id,
20      COUNT(*) AS total_transactions,
21      SUM(amount) AS total_txn_value,
22      AVG(amount) AS avg_txn_value,
23      AVG(balance_after) AS avg_balance,
24      MAX(transaction_date) AS last_txn_date,
25      DATEDIFF(DAY, MAX(transaction_date), '2024-12-31') AS days_since_last_txn,
26      -- Preferred channel
27      MAX(channel) AS main_channel,
28      -- Monthly frequency
29      COUNT(*) * 1.0 / DATEDIFF(MONTH, MIN(transaction_date), MAX(transaction_date)) AS monthly_txn_freq
30  FROM fact_transaction
31  GROUP BY customer_id
32),
33customer_payments AS (
34  SELECT 
35      l.customer_id,
36      COUNT(p.payment_id) AS total_payments,
37      ROUND(AVG(CASE WHEN p.payment_status = 'On-time' THEN 1.0 ELSE 0 END) * 100, 1) AS on_time_pct,
38      ROUND(AVG(p.days_past_due), 1) AS avg_dpd,
39      SUM(p.amount_due - p.amount_paid) AS total_shortfall
40  FROM fact_loan_payment p
41  JOIN fact_loan_application l ON p.application_id = l.application_id
42  GROUP BY l.customer_id
43)
44SELECT 
45  -- Demographics
46  c.customer_id,
47  c.gender,
48  c.age,
49  c.marital_status,
50  c.education,
51  c.employment_type,
52  c.monthly_income,
53  c.city,
54  c.customer_since,
55  DATEDIFF(YEAR, c.customer_since, '2024-12-31') AS tenure_years,
56  -- Segment
57  CASE 
58      WHEN c.monthly_income >= 50000000 THEN 'Priority'
59      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
60      ELSE 'Mass'
61  END AS segment,
62  -- Credit Profile
63  cl.total_applications,
64  cl.approved_loans,
65  cl.default_count,
66  cl.total_loan_amount,
67  ROUND(cl.avg_credit_score, 0) AS avg_credit_score,
68  ROUND(cl.avg_dti_ratio, 1) AS avg_dti_ratio,
69  cl.num_products AS product_count,
70  -- Transaction Behavior
71  ct.total_transactions,
72  ROUND(ct.avg_balance, 0) AS avg_balance,
73  ROUND(ct.monthly_txn_freq, 1) AS monthly_txn_freq,
74  ct.days_since_last_txn,
75  -- Payment Behavior
76  cp.on_time_pct,
77  cp.avg_dpd,
78  -- Derived: Churn Risk
79  CASE 
80      WHEN ct.days_since_last_txn > 90 THEN 'High Risk'
81      WHEN ct.days_since_last_txn > 60 THEN 'Medium Risk'
82      WHEN ct.days_since_last_txn > 30 THEN 'Low Risk'
83      ELSE 'Active'
84  END AS churn_risk
85FROM dim_customer c
86LEFT JOIN customer_loans cl ON c.customer_id = cl.customer_id
87LEFT JOIN customer_txn ct ON c.customer_id = ct.customer_id
88LEFT JOIN customer_payments cp ON c.customer_id = cp.customer_id;

1-- 2. CLV estimation (simplified)
2WITH customer_revenue AS (
3  SELECT 
4      c.customer_id,
5      c.monthly_income,
6      -- Revenue: interest from loans
7      COALESCE(SUM(l.loan_amount * l.interest_rate / 100), 0) AS estimated_annual_interest,
8      -- Tenure
9      DATEDIFF(YEAR, c.customer_since, '2024-12-31') AS tenure_years,
10      -- Risk
11      COALESCE(AVG(l.default_flag), 0) AS default_probability,
12      -- Products
13      COUNT(DISTINCT l.product_id) AS num_products
14  FROM dim_customer c
15  LEFT JOIN fact_loan_application l 
16      ON c.customer_id = l.customer_id AND l.status = 'Approved'
17  GROUP BY c.customer_id, c.monthly_income, c.customer_since
18)
19SELECT 
20  customer_id,
21  monthly_income,
22  estimated_annual_interest,
23  tenure_years,
24  num_products,
25  ROUND(default_probability, 3) AS default_prob,
26  -- Simple CLV = Annual Revenue × Expected Remaining Years × (1 - Default Prob)
27  ROUND(
28      estimated_annual_interest * 
29      CASE WHEN tenure_years < 5 THEN 5 ELSE 3 END *
30      (1 - default_probability),
31      0
32  ) AS estimated_clv,
33  -- CLV Tier
34  CASE 
35      WHEN estimated_annual_interest * 5 * (1 - default_probability) > 50000000 THEN 'Platinum'
36      WHEN estimated_annual_interest * 5 * (1 - default_probability) > 20000000 THEN 'Gold'
37      WHEN estimated_annual_interest * 5 * (1 - default_probability) > 5000000 THEN 'Silver'
38      ELSE 'Bronze'
39  END AS clv_tier
40FROM customer_revenue
41ORDER BY estimated_clv DESC;

1-- 3. CLV summary theo segment
2WITH clv_data AS (
3  SELECT 
4      c.customer_id,
5      CASE 
6          WHEN c.monthly_income >= 50000000 THEN 'Priority'
7          WHEN c.monthly_income >= 20000000 THEN 'Affluent'
8          ELSE 'Mass'
9      END AS segment,
10      COALESCE(SUM(l.loan_amount * l.interest_rate / 100) * 5 * 
11          (1 - AVG(l.default_flag)), 0) AS clv
12  FROM dim_customer c
13  LEFT JOIN fact_loan_application l 
14      ON c.customer_id = l.customer_id AND l.status = 'Approved'
15  GROUP BY c.customer_id, c.monthly_income
16)
17SELECT 
18  segment,
19  COUNT(*) AS customers,
20  ROUND(AVG(clv), 0) AS avg_clv,
21  ROUND(SUM(clv), 0) AS total_clv,
22  ROUND(SUM(clv) * 100.0 / SUM(SUM(clv)) OVER(), 1) AS pct_total_clv
23FROM clv_data
24GROUP BY segment
25ORDER BY avg_clv DESC;