Robinhood query examples

Runnable patterns against the robinhood database. Every column used here exists in the Table reference; for connection details see Indexer overview.

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.

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

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.

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.

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

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.

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

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.

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.

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.

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

New tokens and who deployed them

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

Next steps