# Robinhood query examples

> Working ClickHouse SQL against the Supanode Robinhood Chain Historical Data Indexer: cross-version swap feeds, active pools, readable pairs, launchpad funnels, bonding-curve flow, and execution cost.

Runnable patterns against the `robinhood` database. Every column used here exists in the [Table reference](https://supanode.xyz/docs/robinhood/tables); for connection details see [Indexer overview](https://supanode.xyz/docs/robinhood/indexer).

<Warning>
**Always filter `block_timestamp` in `PREWHERE` on the trade tables.** It feeds the partition key. Without it, a query against `uniswap_v3_trades` scans all 34 GB.
</Warning>

<Tip>
**Keep big integers as strings.** `amount0`, `amount1`, `sqrt_price_x96` and the fee columns are 128- and 256-bit. Wrap them in `toString()` before they leave the database, or a JSON or float64 consumer will truncate them without telling you.
</Tip>

## Recent swaps across both Uniswap versions

The two versions key their pools differently - v3 by contract address, v4 by `pool_id` - and v4 records no recipient. Normalizing both into one shape is the first thing most pipelines need.

```sql
SELECT
    protocol,
    block_timestamp,
    transaction_hash,
    pool,
    sender,
    recipient,
    amount0_raw,
    amount1_raw,
    tick,
    sqrt_price_x96
FROM
(
    SELECT
        'uniswap_v3' AS protocol,
        block_timestamp,
        transaction_hash,
        pool_address AS pool,
        sender,
        toNullable(recipient) AS recipient,
        toString(amount0) AS amount0_raw,
        toString(amount1) AS amount1_raw,
        tick,
        toString(sqrt_price_x96) AS sqrt_price_x96
    FROM uniswap_v3_trades
    PREWHERE block_timestamp >= now() - INTERVAL 1 HOUR
    ORDER BY block_number DESC, transaction_index DESC, log_index DESC
    LIMIT 50

    UNION ALL

    SELECT
        'uniswap_v4' AS protocol,
        block_timestamp,
        transaction_hash,
        pool_id AS pool,
        sender,
        CAST(NULL, 'Nullable(String)') AS recipient,
        toString(amount0) AS amount0_raw,
        toString(amount1) AS amount1_raw,
        tick,
        toString(sqrt_price_x96) AS sqrt_price_x96
    FROM uniswap_v4_trades
    PREWHERE block_timestamp >= now() - INTERVAL 1 HOUR
    ORDER BY block_number DESC, transaction_index DESC, log_index DESC
    LIMIT 50
)
ORDER BY block_timestamp DESC
LIMIT 100
```

<Note>
**Order by the block triple, not by timestamp.** Many swaps share a `block_timestamp` to the second. `block_number`, then `transaction_index`, then `log_index` is the only ordering that reproduces on-chain sequence exactly.
</Note>

## Most active pools in the last complete hour

Scanning the last *complete* hour instead of a moving window keeps consecutive runs comparable - a partial current hour always looks quieter than it is.

```sql
WITH pool_activity AS
(
    SELECT
        'uniswap_v3' AS protocol,
        pool_address AS pool,
        count() AS trades,
        uniqExact(transaction_hash) AS transactions,
        min(block_timestamp) AS first_trade,
        max(block_timestamp) AS last_trade
    FROM uniswap_v3_trades
    PREWHERE block_timestamp >= toStartOfHour(now()) - INTERVAL 1 HOUR
        AND block_timestamp < toStartOfHour(now())
    GROUP BY pool_address

    UNION ALL

    SELECT
        'uniswap_v4' AS protocol,
        pool_id AS pool,
        count() AS trades,
        uniqExact(transaction_hash) AS transactions,
        min(block_timestamp) AS first_trade,
        max(block_timestamp) AS last_trade
    FROM uniswap_v4_trades
    PREWHERE block_timestamp >= toStartOfHour(now()) - INTERVAL 1 HOUR
        AND block_timestamp < toStartOfHour(now())
    GROUP BY pool_id
)
SELECT
    protocol,
    pool,
    trades,
    transactions,
    first_trade,
    last_trade
FROM pool_activity
ORDER BY trades DESC, protocol, pool
LIMIT 50
```

## Turn a pool into a readable pair

A `pool_address` on its own tells you nothing. Join it through the pool table to `tokens` and it becomes a ticker pair with the decimals you need for every later calculation.

```sql
SELECT
    p.pool_address,
    t0.symbol AS symbol0,
    t1.symbol AS symbol1,
    t0.decimals AS decimals0,
    t1.decimals AS decimals1,
    p.fee / 10000 AS fee_percent,
    p.tick_spacing,
    p.block_timestamp AS created_at
FROM uniswap_v3_pools AS p
LEFT JOIN tokens AS t0 ON t0.token_address = p.token0
LEFT JOIN tokens AS t1 ON t1.token_address = p.token1
ORDER BY p.block_timestamp DESC
LIMIT 100
```

<Note>
**`fee` is in hundredths of a basis point.** A 3000 in that column is 0.30 percent, which is why the query divides by 10,000.
</Note>

## A human price out of sqrt_price_x96

Uniswap stores price as a Q64.96 fixed-point square root. Squaring it and dividing by 2 to the 192nd gives the raw ratio; the decimal difference between the two tokens turns that into a price a person can read.

```sql
SELECT
    s.block_timestamp,
    t0.symbol AS symbol0,
    t1.symbol AS symbol1,
    pow(toFloat64(s.sqrt_price_x96) / pow(2, 96), 2)
        * pow(10, toInt16(t0.decimals) - toInt16(t1.decimals)) AS price_1_per_0,
    s.tick
FROM uniswap_v3_trades AS s
INNER JOIN uniswap_v3_pools AS p ON p.pool_address = s.pool_address
LEFT JOIN tokens AS t0 ON t0.token_address = p.token0
LEFT JOIN tokens AS t1 ON t1.token_address = p.token1
PREWHERE s.block_timestamp >= now() - INTERVAL 1 DAY
WHERE t0.symbol != '' AND t1.symbol != ''
ORDER BY s.block_timestamp DESC
LIMIT 100
```

<Warning>
**Converting to `Float64` loses precision by design.** That is fine for charting and ranking, and wrong for accounting. If a number ends up in a ledger, keep the integers and do the arithmetic in a decimal type downstream.
</Warning>

## Launchpad funnel: launched versus graduated

Pons runs a bonding curve and graduates tokens onto a pool, so it carries both a creations table and a migrations table. The gap between them is the funnel.

```sql
SELECT
    toDate(c.block_timestamp) AS day,
    count() AS launched,
    countIf(m.token != '') AS graduated,
    round(100 * countIf(m.token != '') / count(), 2) AS graduation_rate_pct
FROM pons_creations AS c
LEFT JOIN
(
    SELECT DISTINCT token FROM pons_migrations
) AS m ON m.token = c.token
GROUP BY day
ORDER BY day DESC
LIMIT 60
```

## Bonding-curve flow for one token

Before graduation, trading happens on the curve rather than in a pool. `is_buy` splits the direction, and `quote_amount` is the ETH-side leg.

```sql
SELECT
    toStartOfHour(block_timestamp) AS hour,
    countIf(is_buy) AS buys,
    countIf(NOT is_buy) AS sells,
    uniqExact(sender) AS traders,
    toString(sumIf(quote_amount, is_buy)) AS quote_in_raw,
    toString(sumIf(quote_amount, NOT is_buy)) AS quote_out_raw,
    toString(sum(fee)) AS fees_raw
FROM pons_curve_trades
PREWHERE block_timestamp >= now() - INTERVAL 7 DAY
WHERE token = 'PASTE_TOKEN_ADDRESS_HERE'
GROUP BY hour
ORDER BY hour DESC
```

## What a swap cost to execute

Gas and the EIP-1559 fields sit on every decoded event, so cost analysis needs no second data source.

```sql
SELECT
    toDate(block_timestamp) AS day,
    count() AS swaps,
    round(avg(gas_used)) AS avg_gas_used,
    round(avg(toFloat64(base_fee_per_gas)) / 1e9, 4) AS avg_base_fee_gwei,
    round(avg(toFloat64(max_priority_fee_per_gas)) / 1e9, 4) AS avg_max_priority_gwei,
    round(sum(toFloat64(gas_used) * toFloat64(base_fee_per_gas)) / 1e18, 6) AS base_fee_eth
FROM uniswap_v3_trades
PREWHERE block_timestamp >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day DESC
```

<Note>
**`base_fee_eth` is the burnt portion, not the full bill.** The priority fee a transaction actually paid is bounded by `max_priority_fee_per_gas` but is not stored directly, so treat the max columns as a ceiling rather than the amount charged.
</Note>

## New tokens and who deployed them

```sql
SELECT
    block_timestamp AS created_at,
    token_address,
    symbol,
    name,
    decimals,
    creator,
    transaction_hash
FROM tokens
PREWHERE block_timestamp >= now() - INTERVAL 7 DAY
ORDER BY block_timestamp DESC
LIMIT 200
```

<Tip>
**Creator concentration is one join away.** Group the same table by `creator` to see who is deploying at volume - the pattern that separates a single project from a token factory.
</Tip>

## Next steps

<CardGroup cols={2}>
  <Card title="Table reference" icon="table" href="https://supanode.xyz/docs/robinhood/tables">
    All 25 tables, column by column.
  </Card>
  <Card title="Indexer" icon="database" href="https://supanode.xyz/docs/robinhood/indexer">
    Connection details and how the tables join.
  </Card>
</CardGroup>