Payments Pipeline Overview

SELECT
  chain,
  token_symbol,
  DATE_TRUNC('week', block_timestamp) AS week,
  SUM(usd_amount) AS weekly_payment_volume
FROM crosschain.stablecoin.filtered_transfers
GROUP BY 1, 2, 3
ORDER BY 3 DESC;

SELECT
  transaction_type,
  COUNT(*) AS transaction_count,
  SUM(usd_amount) AS total_volume_usd
FROM crosschain.stablecoin.transaction_classification
GROUP BY 1;

SELECT
  core_payment_category,
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS total_usd
FROM crosschain.stablecoin.payment_categorization
GROUP BY 1, 2
ORDER BY 4 DESC;

SELECT
  balance_tier,
  behavioral_segment,
  engagement_tier,
  COUNT(DISTINCT address) AS wallet_count,
  AVG(engagement_score) AS avg_engagement,
  AVG(risk_score) AS avg_risk
FROM crosschain.stablecoin.wallet_classification
GROUP BY 1, 2, 3;