🚨 INFOSYS SP/DSE OFF CAMPUS ASSESSMENT 🚨

🚀 Accenture Frontend Engineer Project Successfully Completed ✅

WITH account_summary AS (
    SELECT
        customer_id,
        SUM(CASE WHEN status = 'active' THEN balance ELSE 0 END) AS total_account_balance
    FROM accounts
    GROUP BY customer_id
),
transaction_summary AS (
    SELECT
        a.customer_id,
        SUM(ABS(t.amount)) AS monthly_transaction_volume,
        COUNT(t.transaction_id) AS transaction_count
    FROM accounts a
    LEFT JOIN transactions t
        ON a.account_id = t.account_id
    GROUP BY a.customer_id
),
loan_summary AS (
    SELECT
        customer_id,
        COALESCE(SUM(principal_amount),0) AS total_loan_principal,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_loans
    FROM loans
    GROUP BY customer_id
),
credit_summary AS (
    SELECT
        customer_id,
        COALESCE(SUM(credit_limit),0) AS total_credit_limit,
        COALESCE(SUM(current_balance),0) AS total_cc_balance,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_credit_cards
    FROM credit_cards
    GROUP BY customer_id
),
branch_rank AS (
    SELECT
        a.customer_id,
        b.branch_name,
        ROW_NUMBER() OVER (
            PARTITION BY a.customer_id
            ORDER BY COUNT(*) DESC, b.branch_name ASC
        ) AS rn
    FROM accounts a
    JOIN branches b
        ON a.branch_id = b.branch_id
    WHERE a.status = 'active'
    GROUP BY a.customer_id, b.branch_name
)
SELECT
    c.customer_id,
    CONCAT(c.first_name,' ',c.last_name) AS customer_name,
    CASE
        WHEN c.date_joined >= '2023-06-01' THEN 'New Customer'
        WHEN c.date_joined BETWEEN '2022-01-01' AND '2023-05-31' THEN 'Established Customer'
        ELSE 'Loyal Customer'
    END AS tenure_segment,
    br.branch_name AS primary_branch,
    COALESCE(acc.total_account_balance,0) AS total_account_balance,
    COALESCE(cs.total_credit_limit,0) AS total_credit_limit,
    COALESCE(cs.total_cc_balance,0) AS total_cc_balance,
    COALESCE(ls.total_loan_principal,0) AS total_loan_principal,
    COALESCE(ts.monthly_transaction_volume,0) AS monthly_transaction_volume,
    (
        LEAST(COALESCE(cs.total_cc_balance / NULLIF(cs.total_credit_limit,0),0) * 100,100) * 0.40 +
        LEAST(COALESCE(ls.total_loan_principal / NULLIF(acc.total_account_balance,0),0) * 100,200) * 0.30 +
        CASE
            WHEN COALESCE(ts.transaction_count,0) = 0 THEN 20
            ELSE 0
        END +
        CASE
            WHEN COALESCE(ls.active_loans,0) = 0
             AND COALESCE(cs.active_credit_cards,0) = 0 THEN 5
            ELSE 0
        END
    ) AS risk_score,
    (
        COALESCE(acc.total_account_balance,0) * 0.02 +
        COALESCE(cs.total_credit_limit,0) * 0.15 +
        COALESCE(ls.total_loan_principal,0) * 0.05 +
        COALESCE(ts.monthly_transaction_volume,0) * 12 * 0.01
    ) AS customer_lifetime_value
FROM customers c
LEFT JOIN account_summary acc
    ON c.customer_id = acc.customer_id
LEFT JOIN transaction_summary ts
    ON c.customer_id = ts.customer_id
LEFT JOIN loan_summary ls
    ON c.customer_id = ls.customer_id
LEFT JOIN credit_summary cs
    ON c.customer_id = cs.customer_id
LEFT JOIN branch_rank br
    ON c.customer_id = br.customer_id
   AND br.rn = 1
ORDER BY
    customer_lifetime_value DESC,
    risk_score ASC,
    c.customer_id ASC;

Comprehensive Customer Value. // Phone pe

🎉 Codingal Python Tutor Interview Completed Successfully! ✅