Deposit Analysis

// Create customers
CREATE (c1:Customer {customerId: "CUST_001"})
CREATE (c2:Customer {customerId: "CUST_002"})
CREATE (c3:Customer {customerId: "CUST_003"})

// Create accounts
CREATE (a1:Account {accountId: "ACC_001", accountNumber: "1000001"})
CREATE (a2:Account {accountId: "ACC_002", accountNumber: "1000002"})
CREATE (a3:Account {accountId: "ACC_003", accountNumber: "1000003"})

// Create relationships - some accounts are shared between customers
CREATE (c1)-[:HAS_ACCOUNT]->(a1)
CREATE (c1)-[:HAS_ACCOUNT]->(a2)
CREATE (c2)-[:HAS_ACCOUNT]->(a2)
CREATE (c2)-[:HAS_ACCOUNT]->(a3)
CREATE (c3)-[:HAS_ACCOUNT]->(a3)

// Create transactions (cash deposits)
CREATE (t1:Transaction {transactionId: "TXN_001", amount: 3000.0, timestamp: datetime()-duration('P2M'), type: "CASH_DEPOSIT"})
CREATE (t2:Transaction {transactionId: "TXN_002", amount: 5000.0, timestamp: datetime()-duration('P1M'), type: "CASH_DEPOSIT"})
CREATE (t3:Transaction {transactionId: "TXN_003", amount: 1000.0, timestamp: datetime(), type: "CASH_DEPOSIT"})
CREATE (t4:Transaction {transactionId: "TXN_004", amount: 4000.0, timestamp: datetime(), type: "CASH_DEPOSIT"})
CREATE (t5:Transaction {transactionId: "TXN_005", amount: 2000.0, timestamp: datetime(), type: "CASH_DEPOSIT"})

// Create transaction relationships
CREATE (a1)-[:PERFORMS]->(t1)-[:BENEFITS_TO]->(a1)
CREATE (a1)-[:PERFORMS]->(t2)-[:BENEFITS_TO]->(a1)
CREATE (a1)-[:PERFORMS]->(t3)-[:BENEFITS_TO]->(a1)
CREATE (a2)-[:PERFORMS]->(t4)-[:BENEFITS_TO]->(a2)
CREATE (a3)-[:PERFORMS]->(t5)-[:BENEFITS_TO]->(a3)

// Show neo4j scheme
CALL db.schema.visualization()

// Find all deposits in the last month with customer and account context
MATCH path = (c:Customer)-[:HAS_ACCOUNT]->(a:Account)-[:PERFORMS]->(t:Transaction)-[:BENEFITS_TO]->(a)
WHERE t.timestamp > datetime()-duration('P1M')
AND t.type = "CASH_DEPOSIT"

RETURN path

// Same query returning tabular data for analysis
MATCH (c:Customer)-[:HAS_ACCOUNT]->(a:Account)-[:PERFORMS]->(t:Transaction)-[:BENEFITS_TO]->(a)
WHERE t.timestamp > datetime()-duration('P1M')
AND t.type = "CASH_DEPOSIT"

RETURN
    c.customerId,
    a.accountNumber,
    t.amount,
    t.timestamp
ORDER BY t.timestamp DESC

// Find consistent high-value depositors for retention and premium pricing
MATCH (c:Customer)-[:HAS_ACCOUNT]->(a:Account)-[:PERFORMS]->(t:Transaction)-[:BENEFITS_TO]->(a)
WHERE t.type = "CASH_DEPOSIT"
AND t.timestamp > datetime()-duration('P3M')

WITH c, a, t,
  CASE
    WHEN t.timestamp > datetime()-duration('P1M') THEN 'current'
    ELSE 'prior_two_months'
  END AS period

WITH c, period, SUM(t.amount) AS total_deposits, COUNT(t) AS transaction_count
WHERE total_deposits > 0

WITH c,
  COALESCE(SUM(CASE WHEN period = 'current' THEN total_deposits END), 0) AS current_month,
  COALESCE(SUM(CASE WHEN period = 'prior_two_months' THEN total_deposits END), 0) AS prior_two_months,
  COALESCE(SUM(CASE WHEN period = 'current' THEN transaction_count END), 0) AS current_txn_count,
  COALESCE(SUM(CASE WHEN period = 'prior_two_months' THEN transaction_count END), 0) AS prior_txn_count

// Filter for consistent depositors (maintained at least 50% of deposit level)
WHERE current_month > 0
AND prior_two_months > 0
AND (current_month / prior_two_months) >= 0.5

// Return customer analysis with retention priority scoring
MATCH (c)-[:HAS_ACCOUNT]->(a:Account)
RETURN
    c.customerId,
    current_month,
    prior_two_months,
    current_month + prior_two_months AS total_deposits_3m,
    current_txn_count + prior_txn_count AS total_transactions,
    (current_month + prior_two_months) / (current_txn_count + prior_txn_count) AS avg_deposit_size,
    current_month / prior_two_months AS consistency_ratio,
    count(DISTINCT a) AS account_count,
    // Priority score for retention campaigns
    (current_month + prior_two_months) * (current_month / prior_two_months) AS retention_priority_score
ORDER BY retention_priority_score DESC

// Visualize household banking relationships and shared account networks
MATCH path = (c1:Customer)-[:HAS_ACCOUNT]->(a:Account)<-[:HAS_ACCOUNT]-(c2:Customer)
WHERE c1.customerId < c2.customerId  // Avoid duplicate pairs

// Include transaction paths for shared accounts
OPTIONAL MATCH txPath = (a)-[:PERFORMS]->(t:Transaction)-[:BENEFITS_TO]->(a)
WHERE t.timestamp > datetime()-duration('P3M')
AND t.type = "CASH_DEPOSIT"

// Return all paths showing household connections
WITH path, collect(txPath) as transactionPaths
LIMIT 25

RETURN path, transactionPaths

// Analyze household/relationship banking patterns through shared accounts
MATCH (c1:Customer)-[:HAS_ACCOUNT]->(a:Account)<-[:HAS_ACCOUNT]-(c2:Customer)
WHERE c1.customerId < c2.customerId  // Avoid duplicate pairs
WITH c1, c2, a

// Get deposit activity for shared accounts over last 3 months
OPTIONAL MATCH (a)-[:PERFORMS]->(t:Transaction)-[:BENEFITS_TO]->(a)
WHERE t.timestamp > datetime()-duration('P3M')
AND t.type = "CASH_DEPOSIT"

// Calculate household deposit metrics
WITH c1, c2, a,
     count(t) as depositCount,
     coalesce(sum(t.amount), 0) as totalDeposits,
     coalesce(avg(t.amount), 0) as avgDepositSize

// Aggregate at household level
WITH c1, c2,
     collect(a.accountNumber) as sharedAccounts,
     count(a) as numberOfSharedAccounts,
     sum(depositCount) as totalTransactions,
     sum(totalDeposits) as householdDepositValue,
     avg(avgDepositSize) as avgHouseholdDepositSize

// Calculate household banking potential
RETURN
    c1.customerId as primaryCustomer,
    c2.customerId as relationshipCustomer,
    numberOfSharedAccounts,
    householdDepositValue,
    totalTransactions,
    avgHouseholdDepositSize,
    // Household value score for relationship banking offers
    householdDepositValue * numberOfSharedAccounts as householdValue
ORDER BY householdValue DESC