Polymarket query examples

Queries you can paste, each written against the real columns in the Table reference.

Amounts and probability

amount_usdc, amount_token and fee are UInt256 in base units with 6 decimals. Both sides share those decimals, so their ratio is the executed probability on a 0 to 1 scale.

SELECT
    block_timestamp,
    transaction_hash,
    wallet,
    asset,
    side,
    is_maker,
    amount_token / 1e6                       AS shares,
    amount_usdc  / 1e6                       AS usdc,
    fee          / 1e6                       AS fee_usdc,
    amount_usdc / nullIf(amount_token, 0)    AS implied_probability
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 1 DAY
ORDER BY amount_usdc DESC
LIMIT 100

Deduplicating the metadata tables

raw_market_meta and raw_event_meta are append-style snapshots. Reduce them to the latest row per key before joining, or every duplicate snapshot multiplies your fill rows.

CREATE VIEW IF NOT EXISTS latest_market AS
SELECT
    clob_token_id,
    argMax(condition_id,  inserted_at) AS condition_id,
    argMax(question,      inserted_at) AS question,
    argMax(outcome,       inserted_at) AS outcome,
    argMax(slug,          inserted_at) AS slug,
    argMax(category,      inserted_at) AS category,
    argMax(is_closed,     inserted_at) AS is_closed,
    argMax(outcome_price, inserted_at) AS outcome_price,
    argMax(best_bid,      inserted_at) AS best_bid,
    argMax(best_ask,      inserted_at) AS best_ask,
    argMax(spread,        inserted_at) AS spread
FROM polymarket.raw_market_meta
GROUP BY clob_token_id

If you cannot create objects with your credentials, inline the same block as a CTE - every example below works either way.

Busiest markets in the last 24 hours

WITH latest_market AS (
    SELECT clob_token_id,
           argMax(question, inserted_at) AS question,
           argMax(outcome,  inserted_at) AS outcome,
           argMax(category, inserted_at) AS category
    FROM polymarket.raw_market_meta
    GROUP BY clob_token_id
)
SELECT
    m.question,
    m.outcome,
    m.category,
    count()                        AS fills,
    sum(f.amount_usdc) / 1e6       AS usdc_volume,
    uniq(f.wallet)                 AS wallets,
    sum(f.amount_usdc) / nullIf(sum(f.amount_token), 0) AS vwap_probability
FROM polymarket.polymarket_order_filled_v3 AS f
INNER JOIN latest_market AS m ON f.asset = m.clob_token_id
PREWHERE f.block_timestamp >= now() - INTERVAL 1 DAY
GROUP BY m.question, m.outcome, m.category
ORDER BY usdc_volume DESC
LIMIT 50

Probability over time for one outcome

Once you have the clob_token_id of the outcome you care about, this is the price series the market actually traded at - not a quoted mid, but executed volume-weighted probability.

SELECT
    toStartOfFiveMinute(block_timestamp)               AS bucket,
    sum(amount_usdc) / nullIf(sum(amount_token), 0)    AS vwap_probability,
    min(amount_usdc / nullIf(amount_token, 0))         AS low,
    max(amount_usdc / nullIf(amount_token, 0))         AS high,
    sum(amount_usdc) / 1e6                             AS usdc_volume,
    count()                                            AS fills
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 7 DAY
WHERE asset = 'YOUR_CLOB_TOKEN_ID'
GROUP BY bucket
ORDER BY bucket

Finding a market by its question

SELECT
    clob_token_id,
    argMax(question,  inserted_at) AS question,
    argMax(outcome,   inserted_at) AS outcome,
    argMax(slug,      inserted_at) AS slug,
    argMax(is_closed, inserted_at) AS is_closed,
    argMax(volume_num, inserted_at) AS volume,
    max(inserted_at)               AS last_seen
FROM polymarket.raw_market_meta
WHERE positionCaseInsensitive(assumeNotNull(question), 'bitcoin') > 0
GROUP BY clob_token_id
ORDER BY volume DESC
LIMIT 50

Event rollup

WITH latest_event AS (
    SELECT event_id,
           argMax(title,    inserted_at) AS title,
           argMax(slug,     inserted_at) AS slug,
           argMax(category, inserted_at) AS category,
           argMax(end_dttm, inserted_at) AS ends
    FROM polymarket.raw_event_meta
    GROUP BY event_id
)
SELECT
    e.title,
    e.category,
    e.ends,
    count()                    AS fills,
    sum(f.amount_usdc) / 1e6   AS usdc_volume,
    uniq(f.wallet)             AS wallets,
    uniq(f.asset)              AS outcomes_traded
FROM polymarket.polymarket_order_filled_v3 AS f
INNER JOIN latest_event AS e ON f.event_id = e.event_id
PREWHERE f.block_timestamp >= now() - INTERVAL 7 DAY
GROUP BY e.title, e.category, e.ends
ORDER BY usdc_volume DESC
LIMIT 50

Maker versus taker

Each fill has two legs and is_maker separates them. Fees land on the taker side.

SELECT
    toDate(block_timestamp)             AS day,
    countIf(is_maker)                   AS maker_legs,
    countIf(NOT is_maker)               AS taker_legs,
    sumIf(amount_usdc, is_maker) / 1e6      AS maker_usdc,
    sumIf(amount_usdc, NOT is_maker) / 1e6  AS taker_usdc,
    sum(fee) / 1e6                      AS fees_usdc
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Top wallets by volume

SELECT
    wallet,
    count()                    AS fills,
    sum(amount_usdc) / 1e6     AS usdc_volume,
    sum(fee) / 1e6             AS fees_paid,
    uniq(asset)                AS outcomes_traded,
    uniq(event_id)             AS events_traded,
    countIf(is_maker)          AS maker_legs,
    round(100 * countIf(is_maker) / count(), 2) AS maker_pct
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 30 DAY
GROUP BY wallet
ORDER BY usdc_volume DESC
LIMIT 100

Net position and cost basis per wallet

side is B for buy and S for sell. Netting them gives shares held and average entry probability.

SELECT
    wallet,
    asset,
    sumIf(amount_token, side = 'B') / 1e6 AS shares_bought,
    sumIf(amount_token, side = 'S') / 1e6 AS shares_sold,
    (sumIf(amount_token, side = 'B') - sumIf(amount_token, side = 'S')) / 1e6 AS net_shares,
    sumIf(amount_usdc, side = 'B') / nullIf(sumIf(amount_token, side = 'B'), 0) AS avg_buy_probability,
    sumIf(amount_usdc, side = 'S') / nullIf(sumIf(amount_token, side = 'S'), 0) AS avg_sell_probability,
    (sumIf(amount_usdc, side = 'S') - sumIf(amount_usdc, side = 'B')) / 1e6 AS realized_usdc
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 90 DAY
WHERE wallet = 'YOUR_WALLET'
GROUP BY wallet, asset
HAVING abs(net_shares) > 0.000001
ORDER BY abs(net_shares) DESC
LIMIT 100

Who was right? Entry price versus resolution

Closed markets carry their settled outcome_price - 1 for the outcome that happened, 0 for the one that did not. Compare it to what people paid.

WITH latest_market AS (
    SELECT clob_token_id,
           argMax(question,      inserted_at) AS question,
           argMax(outcome,       inserted_at) AS outcome,
           argMax(is_closed,     inserted_at) AS is_closed,
           argMax(outcome_price, inserted_at) AS settled
    FROM polymarket.raw_market_meta
    GROUP BY clob_token_id
)
SELECT
    m.question,
    m.outcome,
    m.settled,
    sum(f.amount_usdc) / nullIf(sum(f.amount_token), 0) AS avg_paid_probability,
    m.settled - sum(f.amount_usdc) / nullIf(sum(f.amount_token), 0) AS edge,
    sum(f.amount_usdc) / 1e6 AS usdc_volume
FROM polymarket.polymarket_order_filled_v3 AS f
INNER JOIN latest_market AS m ON f.asset = m.clob_token_id
PREWHERE f.block_timestamp >= now() - INTERVAL 90 DAY
WHERE m.is_closed AND m.settled IS NOT NULL
GROUP BY m.question, m.outcome, m.settled
HAVING usdc_volume > 10000
ORDER BY abs(edge) DESC
LIMIT 50

Longshot bias

Bucket every closed outcome by the probability it traded at, then check how often it actually happened. A calibrated market sits on the diagonal.

WITH latest_market AS (
    SELECT clob_token_id,
           argMax(is_closed,     inserted_at) AS is_closed,
           argMax(outcome_price, inserted_at) AS settled
    FROM polymarket.raw_market_meta
    GROUP BY clob_token_id
)
SELECT
    floor(avg_prob * 10) / 10          AS probability_bucket,
    count()                            AS outcomes,
    avg(avg_prob)                      AS avg_traded_probability,
    avg(settled)                       AS actual_hit_rate,
    avg(settled) - avg(avg_prob)       AS calibration_gap
FROM
(
    SELECT
        f.asset AS asset,
        sum(f.amount_usdc) / nullIf(sum(f.amount_token), 0) AS avg_prob,
        any(m.settled) AS settled
    FROM polymarket.polymarket_order_filled_v3 AS f
    INNER JOIN latest_market AS m ON f.asset = m.clob_token_id
    PREWHERE f.block_timestamp >= now() - INTERVAL 180 DAY
    WHERE m.is_closed AND m.settled IS NOT NULL
    GROUP BY f.asset
    HAVING sum(f.amount_usdc) / 1e6 > 5000
)
GROUP BY probability_bucket
ORDER BY probability_bucket

Polygon transaction costs

The fills table carries the full EIP-1559 transaction envelope, so gas is queryable alongside the trade.

SELECT
    toDate(block_timestamp)                                 AS day,
    count()                                                 AS fills,
    avg(gas_used)                                           AS avg_gas_used,
    avg(base_fee_per_gas / 1e9)                             AS avg_base_fee_gwei,
    avg(max_priority_fee_per_gas / 1e9)                     AS avg_priority_gwei,
    sum(gas_used * base_fee_per_gas) / 1e18                 AS base_fee_pol
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

Performance notes

• block_timestamp in PREWHERE - it feeds toYYYYMMDD(block_timestamp), the partition key.
• Dedup metadata before joining, never after. Joining raw raw_market_meta to fills multiplies rows by the number of snapshots.
• asset before wallet - filtering to one outcome token cuts the scan far harder than filtering to one wallet.
• raw_json is the escape hatch for any field Polymarket ships before it is typed.

EXPLAIN indexes = 1
SELECT count() FROM polymarket.polymarket_order_filled_v3
WHERE block_timestamp >= now() - INTERVAL 1 DAY

Get started

Provision access or start a trial: @supanode_tgs.