> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.

# Example Queries

> Recipes for stablecoin payment analytics on the stablecoins.intelligence schema

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:

```mermaid theme={null}
graph TD
    A["Raw transfers — all activity"] --> B["Adjusted volume — is_adjusted_volume"]
    B --> C["Organic split — Payment / Investment-Trade / Store-of-Value"]
    C --> D["Payment buckets — B2B / B2C / C2B / C2C"]
```

| Stage          | Table                             | Filter                     |
| -------------- | --------------------------------- | -------------------------- |
| Raw            | `enriched_transfers`              | none                       |
| Adjusted       | `enriched_transfers`              | `is_adjusted_volume`       |
| Organic intent | `organic_activity_classification` | `transaction_type`         |
| Payment mix    | `payment_categorization`          | (real-world payments only) |

<Note>
  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.
</Note>

## 1. Adjusted volume trend (last 30 days)

```sql theme={null}
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

```sql theme={null}
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)

```sql theme={null}
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

```sql theme={null}
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, …)

```sql theme={null}
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

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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

<CardGroup cols={2}>
  <Card title="Table Reference" icon="table" href="/historical-data/payments/tables">
    Full schema for every column
  </Card>

  <Card title="Adjusted Volume Methodology" icon="filter" href="/historical-data/payments/adjusted-volume">
    How organic volume is calculated
  </Card>
</CardGroup>