Skip to main content
Production-ready queries for common prediction market analysis tasks. All queries include platform and date filters for optimal performance.

​
Market Discovery

​
Search Markets by Keyword

SELECT
    id,
    platform,
    title,
    status,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
  AND (
    title ILIKE '%bitcoin%'
    OR title ILIKE '%btc%'
  )
ORDER BY created_at DESC
LIMIT 50;

​
Markets Closing Soon

Find active markets closing in the next 7 days—useful for time-sensitive analysis. 
SELECT
    id,
    platform,
    title,
    closes_at,
    dateDiff('hour', now(), closes_at) as hours_until_close
FROM markets
WHERE platform = 'POLYMARKET'
  AND status = 'ACTIVE'
  AND closes_at BETWEEN now() AND now() + INTERVAL 7 DAY
ORDER BY closes_at ASC
LIMIT 100;

​
Recently Created Markets

SELECT
    id,
    platform,
    title,
    category,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 24 HOUR
ORDER BY created_at DESC
LIMIT 50;

​
Most Traded Markets (Last 7 Days)

SELECT
    t.market_id,
    m.title,
    m.platform,
    count() as trade_count,
    sum(t.volume) as total_volume,
    uniq(t.trader_id) as unique_traders
FROM trades t
JOIN markets m ON t.market_id = m.id
WHERE t.platform = 'POLYMARKET'
  AND t.created_at >= now() - INTERVAL 7 DAY
GROUP BY t.market_id, m.title, m.platform
ORDER BY total_volume DESC
LIMIT 25;

​
Price Analysis

​
Price History for a Market

Get all trades for a specific market to plot price over time. 
SELECT
    created_at,
    outcome.name as outcome,
    price,
    volume,
    side
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 30 DAY
ORDER BY created_at ASC;

​
OHLC Candles (Hourly)

Build candlestick data for charting. Grouped by outcome for multi-outcome markets. 
SELECT
    toStartOfHour(created_at) as hour,
    outcome.name as outcome,
    argMin(price, created_at) as open,
    max(price) as high,
    min(price) as low,
    argMax(price, created_at) as close,
    sum(volume) as volume,
    count() as trade_count
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 7 DAY
GROUP BY hour, outcome
ORDER BY hour ASC, outcome;

​
OHLC Candles (Daily)

SELECT
    toDate(created_at) as date,
    outcome.name as outcome,
    argMin(price, created_at) as open,
    max(price) as high,
    min(price) as low,
    argMax(price, created_at) as close,
    sum(volume) as volume
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 90 DAY
GROUP BY date, outcome
ORDER BY date ASC, outcome;

​
Volume-Weighted Average Price (VWAP)

SELECT
    outcome.name as outcome,
    sum(price * volume) / sum(volume) as vwap,
    sum(volume) as total_volume
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 24 HOUR
GROUP BY outcome;

​
Latest Price per Outcome

Get the most recent trade price for each outcome in a market. 
SELECT
    outcome.name as outcome,
    argMax(price, created_at) as latest_price,
    max(created_at) as last_trade_time
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
GROUP BY outcome;

​
Trading Activity

​
Recent Trades on a Market

SELECT
    created_at,
    outcome.name as outcome,
    side,
    price,
    amount,
    volume,
    trader_id
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
ORDER BY created_at DESC
LIMIT 100;

​
Hourly Volume Over Time

SELECT
    toStartOfHour(created_at) as hour,
    count() as trades,
    sum(volume) as volume,
    uniq(trader_id) as unique_traders
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour ASC;

​
Large Trades (Whale Watching)

Find trades above a volume threshold. 
SELECT
    t.created_at,
    m.title,
    t.outcome.name as outcome,
    t.side,
    t.price,
    t.volume,
    t.trader_id
FROM trades t
JOIN markets m ON t.market_id = m.id
WHERE t.platform = 'POLYMARKET'
  AND t.created_at >= now() - INTERVAL 24 HOUR
  AND t.volume > 1000  -- adjust threshold
ORDER BY t.volume DESC
LIMIT 100;

​
Buy vs Sell Pressure

SELECT
    toStartOfHour(created_at) as hour,
    outcome.name as outcome,
    sumIf(volume, side = 'BUY') as buy_volume,
    sumIf(volume, side = 'SELL') as sell_volume,
    sumIf(volume, side = 'BUY') - sumIf(volume, side = 'SELL') as net_flow
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 7 DAY
GROUP BY hour, outcome
ORDER BY hour ASC;

​
Maker vs Taker Distribution

SELECT
    liquidity_role,
    count() as trade_count,
    sum(volume) as total_volume,
    avg(fee) as avg_fee
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 30 DAY
GROUP BY liquidity_role;

​
Trader Analysis

​
Top Traders by Volume

SELECT
    trader_id,
    count() as trade_count,
    sum(volume) as total_volume,
    uniq(market_id) as markets_traded,
    min(created_at) as first_trade,
    max(created_at) as last_trade
FROM trades
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 30 DAY
GROUP BY trader_id
ORDER BY total_volume DESC
LIMIT 100;

​
Trader Activity on a Specific Market

SELECT
    trader_id,
    count() as trades,
    sum(volume) as total_volume,
    sumIf(volume, side = 'BUY') as buy_volume,
    sumIf(volume, side = 'SELL') as sell_volume,
    avg(price) as avg_price
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
GROUP BY trader_id
ORDER BY total_volume DESC
LIMIT 50;

​
Trader’s Position (Net Shares per Outcome)

Estimate a trader’s current position by summing buys minus sells. 
SELECT
    outcome.name as outcome,
    sumIf(amount, side = 'BUY') - sumIf(amount, side = 'SELL') as net_shares,
    sumIf(volume, side = 'BUY') as total_bought,
    sumIf(volume, side = 'SELL') as total_sold
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND trader_id = 'trader-id-here'
GROUP BY outcome;

​
Trader’s Recent Activity Across Markets

SELECT
    t.created_at,
    m.title,
    t.outcome.name as outcome,
    t.side,
    t.price,
    t.volume
FROM trades t
JOIN markets m ON t.market_id = m.id
WHERE t.platform = 'POLYMARKET'
  AND t.trader_id = 'trader-id-here'
  AND t.created_at >= now() - INTERVAL 7 DAY
ORDER BY t.created_at DESC
LIMIT 100;

​
Market Resolution

​
Recently Resolved Markets

SELECT
    id,
    platform,
    title,
    resolution_type,
    resolution_resolved_at,
    resolution_winning_outcome_id
FROM markets
WHERE platform = 'POLYMARKET'
  AND status = 'RESOLVED'
  AND resolution_resolved_at >= now() - INTERVAL 7 DAY
ORDER BY resolution_resolved_at DESC
LIMIT 50;

​
Resolution with Winning Outcome Name

SELECT
    m.id,
    m.title,
    m.resolution_resolved_at,
    m.resolution_type,
    winning.name as winning_outcome
FROM markets m
ARRAY JOIN m.outcomes as winning
WHERE m.platform = 'POLYMARKET'
  AND m.status = 'RESOLVED'
  AND m.resolution_resolved_at >= now() - INTERVAL 30 DAY
  AND winning.id = m.resolution_winning_outcome_id
ORDER BY m.resolution_resolved_at DESC
LIMIT 50;

​
Aggregated Statistics

​
Daily Platform Stats

SELECT
    toDate(created_at) as date,
    platform,
    count() as trades,
    sum(volume) as volume,
    uniq(trader_id) as unique_traders,
    uniq(market_id) as active_markets
FROM trades
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY date, platform
ORDER BY date DESC, platform;

​
Markets by Category

SELECT
    category,
    count() as market_count,
    countIf(status = 'ACTIVE') as active_markets,
    countIf(status = 'RESOLVED') as resolved_markets
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
GROUP BY category
ORDER BY market_count DESC;

​
Outcome Count Distribution

How many markets have 2 outcomes vs 3+ outcomes? 
SELECT
    length(outcomes) as outcome_count,
    count() as market_count
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
GROUP BY outcome_count
ORDER BY outcome_count;

​
Advanced Patterns

​
Price Movement Detection

Find markets where price moved significantly in the last hour. 
WITH recent_prices AS (
    SELECT
        market_id,
        outcome.name as outcome,
        argMin(price, created_at) as price_1h_ago,
        argMax(price, created_at) as current_price
    FROM trades
    WHERE platform = 'POLYMARKET'
      AND created_at >= now() - INTERVAL 1 HOUR
    GROUP BY market_id, outcome
)
SELECT
    m.title,
    r.outcome,
    r.price_1h_ago,
    r.current_price,
    r.current_price - r.price_1h_ago as price_change,
    (r.current_price - r.price_1h_ago) / r.price_1h_ago * 100 as pct_change
FROM recent_prices r
JOIN markets m ON r.market_id = m.id
WHERE abs(r.current_price - r.price_1h_ago) > 0.05  -- 5+ point move
ORDER BY abs(pct_change) DESC
LIMIT 25;

​
Markets with Unusual Volume

Find markets trading above their 7-day average. 
WITH volume_stats AS (
    SELECT
        market_id,
        sum(volume) / 7 as avg_daily_volume
    FROM trades
    WHERE platform = 'POLYMARKET'
      AND created_at BETWEEN now() - INTERVAL 8 DAY AND now() - INTERVAL 1 DAY
    GROUP BY market_id
),
today_volume AS (
    SELECT
        market_id,
        sum(volume) as today_volume
    FROM trades
    WHERE platform = 'POLYMARKET'
      AND created_at >= now() - INTERVAL 1 DAY
    GROUP BY market_id
)
SELECT
    m.title,
    t.today_volume,
    v.avg_daily_volume,
    t.today_volume / v.avg_daily_volume as volume_ratio
FROM today_volume t
JOIN volume_stats v ON t.market_id = v.market_id
JOIN markets m ON t.market_id = m.id
WHERE v.avg_daily_volume > 100  -- filter low-volume markets
ORDER BY volume_ratio DESC
LIMIT 25;

​
Spread Between Outcomes

For binary markets, calculate the bid-ask implied spread. 
SELECT
    m.title,
    sumIf(latest_price, outcome_index = 0) as yes_price,
    sumIf(latest_price, outcome_index = 1) as no_price,
    sumIf(latest_price, outcome_index = 0) + sumIf(latest_price, outcome_index = 1) as total,
    abs(1 - (sumIf(latest_price, outcome_index = 0) + sumIf(latest_price, outcome_index = 1))) as spread
FROM (
    SELECT
        market_id,
        outcome.index as outcome_index,
        argMax(price, created_at) as latest_price
    FROM trades
    WHERE platform = 'POLYMARKET'
      AND created_at >= now() - INTERVAL 1 DAY
    GROUP BY market_id, outcome_index
) t
JOIN markets m ON t.market_id = m.id
WHERE m.market_type = 'BINARY'
GROUP BY m.id, m.title
HAVING spread > 0.02  -- markets with >2% spread
ORDER BY spread DESC
LIMIT 25;

​
Time-Weighted Average Price (TWAP)

Weight prices by time between trades, not volume. 
WITH trade_intervals AS (
    SELECT
        created_at,
        price,
        leadInFrame(created_at) OVER (ORDER BY created_at) as next_trade_time,
        dateDiff('second', created_at,
            leadInFrame(created_at) OVER (ORDER BY created_at)
        ) as seconds_until_next
    FROM trades
    WHERE platform = 'POLYMARKET'
      AND market_id = 'your-market-uuid-here'
      AND outcome.name = 'Yes'
      AND created_at >= now() - INTERVAL 24 HOUR
)
SELECT
    sum(price * seconds_until_next) / sum(seconds_until_next) as twap
FROM trade_intervals
WHERE seconds_until_next > 0;

​
Exporting Data

​
Export to CSV Format

Add FORMAT CSV or FORMAT CSVWithNames to any query: 
SELECT
    created_at,
    price,
    volume,
    side
FROM trades
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND created_at >= now() - INTERVAL 30 DAY
ORDER BY created_at
FORMAT CSVWithNames;

​
Export to JSON

SELECT
    id,
    title,
    status,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 7 DAY
FORMAT JSONEachRow;
When using programmatic clients, the format is usually handled by the client library. These FORMAT clauses are useful for CLI exports or direct HTTP queries.