Tables & Schemas

Array(Tuple(
    id UUID,            -- Probalytics outcome ID
    platform_id String, -- Platform's native outcome ID
    name String,        -- Outcome name (e.g., "Yes", "No", "Trump")
    index UInt8         -- Position in the outcomes list
))

SELECT
    id,
    title,
    outcome.id as outcome_id,
    outcome.name as outcome_name
FROM markets
ARRAY JOIN outcomes as outcome
WHERE platform = 'POLYMARKET'
LIMIT 5;

Tuple(
    id UUID,            -- Probalytics outcome ID
    platform_id String, -- Platform's native outcome ID
    name String,        -- Outcome name
    index UInt8         -- Position index
)

SELECT
    outcome.id as outcome_id,
    outcome.platform_id as platform_outcome_id,
    outcome.name as outcome_name,
    outcome.index as outcome_index,
    price,
    size
FROM fills
LIMIT 5;

Tuple(
    price Decimal64(6), -- Price level
    size Decimal64(6)   -- Total size at this price
)

SELECT
    market_platform_id,
    outcome.name as outcome_name,
    bids[1].price as best_bid_price,
    bids[1].size as best_bid_size,
    asks[1].price as best_ask_price,
    asks[1].size as best_ask_size,
    timestamp
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
ORDER BY timestamp DESC
LIMIT 10;

SELECT
    toStartOfMinute(timestamp) as minute,
    outcome.name as outcome,
    avg(asks[1].price - bids[1].price) as avg_spread,
    avg(bids[1].size + asks[1].size) as avg_top_of_book_size
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 1 HOUR
  AND length(bids) > 0
  AND length(asks) > 0
GROUP BY minute, outcome
ORDER BY minute ASC;

SELECT
    m.title,
    f.price,
    f.size,
    f.outcome.name
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE m.platform = 'POLYMARKET'
LIMIT 10;

ORDER BY (platform, created_at, id, platform_id)

ORDER BY (platform, market_id, timestamp, id)
PARTITION BY toYYYYMM(timestamp)

ORDER BY (market_id, outcome.id, timestamp)
PARTITION BY toYYYYMM(timestamp)

-- Filter by date range
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01'

-- Extract components
SELECT toYear(timestamp), toMonth(timestamp), toDayOfWeek(timestamp)

-- Prices are typically 0-1 (probability as decimal)
WHERE price BETWEEN 0.1 AND 0.5

-- Safe arithmetic
SELECT sum(size), avg(price)

-- String comparison
WHERE platform = 'POLYMARKET'

-- Also works (position-based)
WHERE platform = 1

-- Check if array contains value
WHERE has(tags, 'politics')

-- Expand array to rows using ARRAY JOIN
SELECT outcome.name
FROM markets
ARRAY JOIN outcomes as outcome

-- Array length
WHERE length(outcomes) > 2

-- Access by index (1-based)
SELECT bids[1].price as best_bid FROM orderbook_snapshots

SELECT
    outcome.id,
    outcome.platform_id,
    outcome.name,
    outcome.index
FROM fills