Payment Pipeline Tables

SELECT
  chain,
  SUM(usd_amount) AS adjusted_volume_30d
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

SELECT
  DATE_TRUNC('day', block_timestamp) AS day,
  COUNT(*) AS transfer_count,
  SUM(usd_amount) AS daily_volume
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1;

SELECT
  token_symbol,
  COUNT(*) AS transfer_count,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;

SELECT
  balance_tier,
  behavioral_segment,
  COUNT(*) AS wallet_count,
  AVG(engagement_score) AS avg_engagement,
  AVG(risk_score) AS avg_risk
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
GROUP BY 1, 2
ORDER BY 3 DESC;

SELECT
  chain,
  address,
  balance_tier,
  current_balance_usd,
  engagement_score,
  risk_flag
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
  AND balance_tier IN ('whale', 'large')
  AND behavioral_segment = 'active'
ORDER BY current_balance_usd DESC
LIMIT 100;

SELECT
  risk_flag,
  COUNT(*) AS wallet_count,
  AVG(volume_30d_usd) AS avg_volume,
  AVG(volatility_risk_score) AS avg_volatility,
  AVG(anomaly_risk_score) AS avg_anomaly
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
GROUP BY 1;

SELECT
  transaction_type,
  COUNT(*) AS tx_count,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.transaction_classification
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;

SELECT
  wallet_type_combination,
  COUNT(*) AS tx_count,
  AVG(usd_amount) AS avg_amount,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.transaction_classification
WHERE activity_date >= CURRENT_DATE - 30
  AND transaction_type = 'Real-World Payment'
GROUP BY 1
ORDER BY 4 DESC;

SELECT
  DATE_TRUNC('week', activity_date) AS week,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS weekly_volume
FROM crosschain.stablecoin.transaction_classification
WHERE wallet_type_combination = 'C2B'
  AND transaction_type = 'Real-World Payment'
  AND activity_date >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1;

SELECT
  core_payment_category,
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS total_usd,
  AVG(usd_amount) AS avg_payment_size
FROM crosschain.stablecoin.payment_categorization
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 4 DESC;

SELECT
  DATE_TRUNC('month', activity_date) AS month,
  COUNT(*) AS payroll_count,
  SUM(usd_amount) AS total_payroll_usd
FROM crosschain.stablecoin.payment_categorization
WHERE payment_purpose = 'Salary/Payroll'
  AND activity_date >= CURRENT_DATE - 180
GROUP BY 1
ORDER BY 1;

SELECT
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS volume
FROM crosschain.stablecoin.payment_categorization
WHERE payment_purpose IN ('Retail Purchase', 'Service Payment')
  AND activity_date >= CURRENT_DATE - 30
GROUP BY 1;

SELECT
  ft.transaction_hash,
  ft.usd_amount,
  wc_from.balance_tier AS from_balance_tier,
  wc_from.engagement_score AS from_engagement,
  wc_to.balance_tier AS to_balance_tier,
  wc_to.engagement_score AS to_engagement
FROM crosschain.stablecoin.filtered_transfers ft
LEFT JOIN crosschain.stablecoin.wallet_classification wc_from
  ON ft.from_address = wc_from.address
  AND DATE(ft.block_timestamp) = wc_from.snapshot_date
LEFT JOIN crosschain.stablecoin.wallet_classification wc_to
  ON ft.to_address = wc_to.address
  AND DATE(ft.block_timestamp) = wc_to.snapshot_date
WHERE ft.block_timestamp >= CURRENT_DATE - 7;

SELECT
  tc.transaction_type,
  pc.payment_purpose,
  COUNT(*) AS tx_count
FROM crosschain.stablecoin.transaction_classification tc
LEFT JOIN crosschain.stablecoin.payment_categorization pc
  ON tc.transaction_hash = pc.transaction_hash
WHERE tc.activity_date >= CURRENT_DATE - 30
GROUP BY 1, 2;