Crypto Screener: Real-Time Ranking of 1,200+ Pairs Across Multiple Exchanges

Crypto market data is abundant and effectively unusable in raw form. A trader scanning for momentum candidates across 1,200 perpetual futures pairs cannot process that universe manually. The filtering primitives that equity screeners offer do not exist natively across the crypto data layer when the underlying source is a time-series bar table rather than a pre-aggregated snapshot.

The Argus data layer had accumulated 87 million rows of daily bars in argus.bars_1d across spot pairs, perpetual futures, and leveraged tokens on 10+ exchanges. The screener requirement was to expose this full universe as a filterable, sortable table computing derived metrics like 7-day and 30-day returns, 52-week high/low proximity, and daily change percentage on demand from the bar table rather than from a pre-computed snapshot that would immediately stale.

The screener was implemented as a single SQL query against ClickHouse at GET /api/screener, parameterized by exchange filter, price range, volume floor, return range, proximity to 52-week extremes, sort column, and pagination.

ClickHouse was the correct engine. The argMax(close, ts) aggregation function returned the most recent close price per symbol in a single pass over the table. Conditional aggregations using FILTER(WHERE ts >= today() - N) computed windowed statistics in the same pass. The entire screener metric set for a filtered universe was computed in one query scan. The query architecture used a window function to avoid the classical double-scan problem: data and total count were returned in a single pass using count() OVER () as a virtual column.

Exchange routing used a heuristic based on symbol format rather than an explicit exchange column. US equities were identified by symbols without dots and length under 6. Crypto symbols followed the EXCHANGE:BASE-QUOTE convention from the ingest daemons.

SQL injection prevention: sort column names were validated against a Set of allowed columns before interpolation into the query. All numeric parameters were passed through parseFloat before interpolation and were never treated as strings.

The first implementation computed windowed returns using a correlated subquery per symbol, which produced N+1 scans. Conditional aggregation with FILTER ran the same computation in a single GROUP BY pass. The performance difference was approximately 10x for typical filter combinations.

The Redis default cache covered only the exact default parameter combination: US exchange, no filters, page 1, sort by volume DESC, limit 50. The background cache warmer populated this key on a schedule. Cache hit rate for the default view exceeded 85% under normal traffic patterns.

The screener query executed against the full bars_1d table (87 million rows, 19,172 symbols, data from 2010 to 2026) in 180 to 420 milliseconds for typical filter combinations. The combined data-and-count query using count() OVER () avoided a second table scan, reducing query time by approximately 35% compared to the two-query approach. The Redis default-cache path returned in under 15 milliseconds for the most common query variant.