Tips & Best Practices

-- Slow: No ORDER BY filters
SELECT * FROM markets WHERE title LIKE '%election%';

-- Better: Use the sort-key prefix and bound the requested period
SELECT * FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= '2024-01-01'
  AND title LIKE '%election%';

-- Slow: Scans all fills
SELECT * FROM fills WHERE price > 0.9;

-- Fast: Filter by ORDER BY columns
SELECT * FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= '2024-01-01'
  AND price > 0.9;

-- Bad: Loads massive result set
SELECT * FROM fills;

-- Good: Quick exploration
SELECT * FROM fills LIMIT 100;

-- Slow: FINAL for exploration
SELECT * FROM markets FINAL LIMIT 100;

-- Fast: Skip FINAL for data browsing
SELECT * FROM markets LIMIT 100;

-- Use FINAL for accurate counts
SELECT count() FROM markets FINAL;

-- Bad: Transfers 20+ columns unnecessarily
SELECT * FROM fills;

-- Good: Only needed columns
SELECT timestamp, normalized_price, size, taker_side FROM fills;

SELECT
    toStartOfHour(timestamp) AS hour,
    avg(price) AS average_price
FROM fills
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY hour
ORDER BY hour;

SELECT quantile(0.95)(price) as p95_price
FROM fills
WHERE market_id = 'your-market-uuid-here';

SELECT uniq(taker_id) as unique_takers FROM fills;

SELECT outcome.name
FROM markets
ARRAY JOIN outcomes AS outcome
WHERE platform = 'POLYMARKET';

SELECT
    bids[1].price as best_bid,
    asks[1].price as best_ask,
    asks[1].price - bids[1].price as spread
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND outcome.id = 'your-outcome-uuid-here'
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 1;