Skip to main content
These recipes run against the stablecoins.intelligence schema. Where a query is meant for quick evaluation, it uses the broadly-shared enriched_transfers_last_30d view; swap in the full enriched_transfers table (gated) for full-history analysis.

​
The Payments Funnel

Stablecoin volume narrows sharply from raw transfers to real-world payments. The pipeline exposes each stage so you can measure the drop-off:
StageTableFilter
Rawenriched_transfersnone
Adjustedenriched_transfersis_adjusted_volume
Organic intentorganic_activity_classificationtransaction_type
Payment mixpayment_categorization(real-world payments only)
Most stablecoin volume is trading / investment, not payments — real-world payments are a minority slice of organic volume. Use is_adjusted_volume and transaction_type = 'Real-World Payment' to isolate genuine payment activity.

​
1. Adjusted volume trend (last 30 days)

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;

​
2. Organic split — payments vs trading vs savings

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;

​
3. Real-world payment mix (B2B / B2C / C2B / C2C)

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;

​
4. Payment mix by chain

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;

​
5. Payment purpose breakdown (P2P, payroll, supplier, …)

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;

​
6. Payroll volume over time

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;

​
7. Store-of-value inflows

Wallets accumulating stablecoins rather than spending them: 
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;

​
8. Count vs volume — the large-ticket effect

Consumer flows dominate by transfer count; business flows dominate by dollar volume: 
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;

​
9. Join across the pipeline

Enriched transfer + intent + payment purpose in one row: 
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;

​
Next Steps

Table Reference

Full schema for every column

Adjusted Volume Methodology

How organic volume is calculated