Example Queries

SELECT
  DATE_TRUNC('day', block_timestamp) AS day,
  SUM(usd_amount) AS adjusted_volume
FROM stablecoins.intelligence.enriched_transfers_last_30d
WHERE is_adjusted_volume
GROUP BY 1
ORDER BY 1;

SELECT
  transaction_type,
  COUNT(*) AS transfers,
  SUM(usd_amount) AS volume_usd,
  ROUND(100 * SUM(usd_amount) / SUM(SUM(usd_amount)) OVER (), 1) AS pct_of_volume
FROM stablecoins.intelligence.organic_activity_classification
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;

SELECT
  CASE
    WHEN core_payment_category IN ('B2B Payment', 'I2B Payment', 'Institutional Payment') THEN 'B2B'
    WHEN core_payment_category = 'B2C Payment' THEN 'B2C'
    WHEN core_payment_category = 'C2B Payment' THEN 'C2B'
    WHEN core_payment_category = 'C2C Payment' THEN 'C2C'
  END AS bucket,
  SUM(usd_amount) AS volume_usd,
  COUNT(DISTINCT transaction_hash) AS payments
FROM stablecoins.intelligence.payment_categorization
WHERE activity_date >= CURRENT_DATE - 30
  AND core_payment_category IN (
    'B2B Payment', 'I2B Payment', 'Institutional Payment',
    'B2C Payment', 'C2B Payment', 'C2C Payment'
  )
GROUP BY 1
ORDER BY 2 DESC;

SELECT
  chain,
  core_payment_category,
  SUM(usd_amount) AS volume_usd
FROM stablecoins.intelligence.payment_categorization
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

SELECT
  core_payment_category,
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS total_usd,
  AVG(usd_amount) AS avg_payment_size
FROM stablecoins.intelligence.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_payments,
  SUM(usd_amount) AS payroll_usd
FROM stablecoins.intelligence.payment_categorization
WHERE payment_purpose = 'Salary/Payroll'
  AND activity_date >= CURRENT_DATE - 180
GROUP BY 1
ORDER BY 1;

SELECT
  chain,
  COUNT(*) AS transfers,
  SUM(usd_amount) AS volume_usd
FROM stablecoins.intelligence.organic_activity_classification
WHERE transaction_type = 'Store as Value'
  AND activity_date >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;

SELECT
  wallet_type_combination,
  COUNT(*) AS transfers,
  SUM(usd_amount) AS volume_usd,
  ROUND(SUM(usd_amount) / NULLIF(COUNT(*), 0), 0) AS avg_ticket_usd
FROM stablecoins.intelligence.organic_activity_classification
WHERE transaction_type = 'Real-World Payment'
  AND activity_date >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;

SELECT
  e.chain,
  e.token_symbol,
  e.usd_amount,
  o.transaction_type,
  o.wallet_type_combination,
  p.core_payment_category,
  p.payment_purpose
FROM stablecoins.intelligence.enriched_transfers_last_30d e
LEFT JOIN stablecoins.intelligence.organic_activity_classification o
  ON o.chain = e.chain AND o.transaction_hash = e.transaction_hash
LEFT JOIN stablecoins.intelligence.payment_categorization p
  ON p.chain = e.chain AND p.transaction_hash = e.transaction_hash
WHERE e.is_adjusted_volume
LIMIT 100;