Query examples

Queries you can paste, each written against the live column names in the Table reference.

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.

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.

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.

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.

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

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.

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

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.

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

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.

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 or message @supanode_tgs.

See also