# Query examples

> Working ClickHouse SQL against the Supanode Solana Indexer: launch cohorts, creator migration rates, PumpSwap fee reconciliation, aggregator routing splits, and sub-slot timing.

Queries you can paste, each written against the live column names in the [Table reference](https://supanode.xyz/docs/solana/indexer/tables).

<Note>
**Every example is bounded.** Each one filters on a partition column or a recent time window before it touches anything else. Copy that habit - the swap tables are large enough that an unbounded scan is expensive for you and slow for everyone.
</Note>

## Transfer and funding activity, hour by hour

`token_transfers` and `sol_top_ups` are deliberately separate tables. To see both rhythms side by side, union their hourly counts rather than joining individual rows.

```sql
WITH
    toStartOfHour(now()) AS end_hour,
    end_hour - INTERVAL 24 HOUR AS start_hour
SELECT
    hour,
    sum(token_transfers) AS token_transfers,
    sum(sol_top_ups)     AS sol_top_ups
FROM
(
    SELECT toStartOfHour(block_time) AS hour, count() AS token_transfers, 0 AS sol_top_ups
    FROM token_transfers
    PREWHERE block_time >= start_hour AND block_time < end_hour
    GROUP BY hour

    UNION ALL

    SELECT toStartOfHour(block_time) AS hour, 0 AS token_transfers, count() AS sol_top_ups
    FROM sol_top_ups
    PREWHERE block_time >= start_hour AND block_time < end_hour
    GROUP BY hour
)
GROUP BY hour
ORDER BY hour
```

Both tables keep 31 days, so anchor any longer study to an export.

## Most active Pump.fun creators in the last 24 hours

`pumpfun_token_creation` stores `creator` and `mint` as `FixedString(48)`, so strip the null padding before grouping.

```sql
SELECT
    replaceAll(toString(creator), '\0', '') AS creator,
    count()                                 AS launches,
    sum(bundled_buys) / 1e9                 AS bundled_buys_sol,
    max(bundle_size)                        AS biggest_bundle
FROM pumpfun_token_creation
WHERE block_time >= now() - INTERVAL 1 DAY
GROUP BY creator
HAVING launches >= 5
ORDER BY launches DESC
LIMIT 25
```

`bundled_buys` is in lamports, hence the `/ 1e9`.

## Creator migration rate

How often does a creator's launch actually graduate to the PumpSwap AMM? Anchor the window to the newest data in the table rather than to the wall clock, so the result stays reproducible.

```sql
WITH
    (SELECT max(block_time) FROM pumpfun_token_creation) AS data_end,
    data_end - INTERVAL 30 DAY                           AS data_start
SELECT
    c.creator                                        AS creator,
    count()                                          AS tokens,
    countIf(m.mint != '')                            AS migrated,
    round(100 * countIf(m.mint != '') / count(), 2)  AS migrated_pct,
    min(c.block_time)                                AS first_launch,
    max(c.block_time)                                AS last_launch
FROM
(
    SELECT replaceAll(toString(creator), '\0', '') AS creator,
           replaceAll(toString(mint), '\0', '')    AS mint,
           block_time
    FROM pumpfun_token_creation
    WHERE block_time >= data_start AND block_time <= data_end
) AS c
LEFT JOIN
(
    SELECT DISTINCT mint FROM pfamm_migrations
    WHERE block_date_utc >= toDate(data_start)
) AS m
ON c.mint = m.mint
GROUP BY creator
HAVING tokens >= 5
ORDER BY migrated_pct DESC, tokens DESC
LIMIT 50
```

## PumpSwap fee and reserve reconciliation

`pumpswap_all_swaps` exposes pool reserves before and after the swap alongside every fee component, so the accounting can be checked rather than assumed.

```sql
SELECT
    signature,
    direction,
    base_token_amount,
    quote_token_amount,
    -- reserve movement should equal the traded amounts
    abs(toInt128(pool_base_token_reserves_after)  - toInt128(pool_base_token_reserves_before))
        = base_token_amount  AS base_reserves_ok,
    abs(toInt128(pool_quote_token_reserves_after) - toInt128(pool_quote_token_reserves_before))
        = quote_token_amount AS quote_reserves_ok,
    lp_fee,
    protocol_fee,
    coin_creator_fees,
    cash_back_fees,
    buy_back_fees,
    quote_token_amount_without_lp_fee,
    is_exact_quote
FROM pumpswap_all_swaps
PREWHERE block_date_utc = today()
ORDER BY slot DESC, tx_idx DESC
LIMIT 25
```

The creator, cashback, buyback and virtual-reserve fields are populated from 23 July 2026 onward; before that date they hold zeros.

## Top wallets by PumpSwap SOL volume today

```sql
SELECT
    signing_wallet,
    count()                        AS swaps,
    sum(quote_token_amount) / 1e9  AS volume_sol,
    countIf(direction = 'buy')     AS buys,
    countIf(direction = 'sell')    AS sells
FROM pumpswap_all_swaps
PREWHERE block_date_utc = today()
WHERE quote_token = 'So11111111111111111111111111111111111111112'
GROUP BY signing_wallet
ORDER BY volume_sol DESC
LIMIT 20
```

## Organic flow versus routed flow

`parent_program` records the program that invoked the swap through CPI. An empty value means the DEX was called directly; anything else is an aggregator, bot, or router.

```sql
SELECT
    if(parent_program = '', 'direct', parent_program) AS router,
    count()                                          AS swaps,
    sum(quote_token_amount) / 1e9                    AS volume_sol,
    round(100 * count() / sum(count()) OVER (), 2)   AS share_pct
FROM pumpswap_all_swaps
PREWHERE block_date_utc = today()
WHERE quote_token = 'So11111111111111111111111111111111111111112'
GROUP BY router
ORDER BY swaps DESC
LIMIT 20
```

## Jito tip spend by wallet

```sql
SELECT
    sender,
    count()          AS tips,
    sum(amount)/1e9  AS tipped_sol,
    max(amount)/1e9  AS biggest_tip_sol
FROM jito_tips
PREWHERE block_date_utc >= today() - 6
GROUP BY sender
ORDER BY tipped_sol DESC
LIMIT 25
```

`jito_tips` keeps 14 days, so a week is a safe window.

## Sub-slot timing

`tx_timestamps` holds a high-precision entry timestamp per transaction. Join it on `(slot, tx_idx)` to place swaps inside a slot instead of at block granularity.

```sql
SELECT
    s.slot                                          AS slot,
    count()                                         AS swaps,
    min(t.entry_timestamp)                          AS first_entry,
    max(t.entry_timestamp)                          AS last_entry,
    round((max(t.entry_timestamp) - min(t.entry_timestamp)) * 1000, 3) AS spread_ms
FROM pumpfun_v2_swaps AS s
INNER JOIN tx_timestamps AS t
    ON s.slot = t.slot AND s.tx_idx = t.tx_idx
PREWHERE s.block_date_utc = today()
GROUP BY slot
HAVING swaps > 10
ORDER BY spread_ms DESC
LIMIT 20
```

<Warning>
**`tx_timestamps` keeps 14 days.** Any latency study reaching further back needs an export arranged in advance - see [Nanosecond timestamps](https://supanode.xyz/docs/solana/indexer/nanosecond-timestamp).
</Warning>

## Failed transactions on Pump.fun v2

`pumpfun_v2_swaps` is the only swap table that retains failed transactions, and `failed` is part of its partition key - so filtering on it is free.

```sql
SELECT
    toStartOfHour(block_time)     AS hour,
    countIf(failed = 1)           AS failed_swaps,
    countIf(failed = 0)           AS landed_swaps,
    round(100 * countIf(failed = 1) / count(), 2) AS fail_pct
FROM pumpfun_v2_swaps
PREWHERE block_date_utc >= today() - 1
GROUP BY hour
ORDER BY hour
```

## Need a query written for you?

Recurring queries can be deployed as a stable REST endpoint, and heavier batch work as a scheduled Python ETL flow - both quoted per scope on top of the base plan. See [Access](https://supanode.xyz/docs/solana/indexer/access#custom-rest-api) or message [@supanode_tgs](https://telegram.me/supanode_tgs).

## See also

<CardGroup cols={3}>
  <Card title="Table reference" icon="table" href="https://supanode.xyz/docs/solana/indexer/tables">
    Every column these queries use.
  </Card>
  <Card title="Schema conventions" icon="database" href="https://supanode.xyz/docs/solana/indexer/database-schema">
    Padding, units, partitions, retention.
  </Card>
  <Card title="Access" icon="key" href="https://supanode.xyz/docs/solana/indexer/access">
    Connect from Node, Python, or Go.
  </Card>
</CardGroup>