Daily Prices

1. Coinpaprika prices for tokens where we have reliable external data (~2,000 major tokens, defined in token lists)
2. DEX-derived prices for the long tail of tokens not covered by coinpaprika

​

Table Schema

​

Table Sample

​

Implementation Details

1. Source prioritization: Coinpaprika prices take precedence where available, DEX-derived prices fill gaps
2. Token list management: Lists defining which tokens use coinpaprika vs DEX are managed in the open-source repository
3. Data aggregation: Group by day, calculating volume-weighted average price per day
4. Forward filling: Fill missing days with the previous day’s price (forward filling)
5. Expiration: Set a 30-day expiration for forward filling to avoid stale data

​

Alternative Tables

​

prices_external.day

• Coverage: Only tokens with Coinpaprika listings (~2,000 tokens)
• Source: External exchange-aggregated pricing
• Use case: When you need stable, externally-validated daily pricing
• Special note: Used by tokens.transfers for stable pricing
• Schema: Same as prices.day but source is always ‘coinpaprika’

​

prices_dex.day

• Coverage: All tokens with sufficient DEX trading volume
• Source: On-chain DEX trading activity only (from dex.trades table)
• Use case: When you need pricing that reflects actual on-chain liquidity and trading activity
• Methodology: Comprehensive outlier detection and quality filtering applied to DEX trades
• Key difference: Even tokens with coinpaprika coverage use DEX-derived prices for consistency
• Schema: Same as prices.day but source is always ‘dex.trades’

SELECT
  d.timestamp,
  d.symbol,
  d.price as dex_price,
  c.price as combined_price,
  (d.price - c.price) / c.price * 100 as price_diff_pct
FROM prices_dex.day d
JOIN prices.day c 
  ON d.blockchain = c.blockchain 
  AND d.contract_address = c.contract_address 
  AND d.timestamp = c.timestamp
WHERE d.blockchain = 'ethereum'
AND d.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND d.timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY d.timestamp

​

Usage

• Daily price trend analysis
• Portfolio valuations
• Day-over-day price comparisons
• General token price lookups

​

Latency and Update Frequency

​

Usage Examples

​

Get ETH price history for the last month:

SELECT
  timestamp,
  price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​

Calculate monthly average price for multiple tokens:

SELECT
  blockchain,
  symbol, -- Symbol included for readable output only
  date_trunc('month', timestamp) as month,
  avg(price) as avg_price
FROM prices.day
WHERE blockchain = 'ethereum'
AND contract_address IN (
  0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, -- WETH
  0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, -- USDC
  0x2260fac5e5542a773aa44fbcfedf7c193bc2c599  -- WBTC
)
AND timestamp >= NOW() - INTERVAL '90' DAY
GROUP BY 1,2,3
ORDER BY 1,2,3

​

Get native token price using dune.blockchains:

-- First get the native token address
WITH native_token AS (
  SELECT token_address
  FROM dune.blockchains
  WHERE name = 'ethereum'
)
-- Then use it to query the price
SELECT
  timestamp,
  price
FROM prices.day, native_token
WHERE blockchain = 'ethereum'
AND contract_address = token_address
AND timestamp >= NOW() - INTERVAL '30' DAY
ORDER BY timestamp

​

Data Quality Notes

• Prices older than 30 days will not be forward-filled to avoid using stale data
• Native tokens (like ETH, BNB) are assigned fixed addresses for consistency
• For native tokens, use dune.blockchains table to get the standardized address for each blockchain
• The same token may have different prices on different blockchains due to bridging inefficiencies
• Always use contract_address and blockchain for precise token identification, never use symbol for joins or filters
• Prices are calculated using volume-weighted averages for more accurate representation
• Quality Filtering: The system includes filtering mechanisms to reduce the impact of anomalous trades
• DEX-derived limitations: DEX-derived prices can contain errors due to low liquidity, market manipulation, or anomalous trades
• Fallback option: For critical applications, consider using prices_external.day for more reliable pricing where available
• Data validation: Always validate prices for critical applications, especially for lesser-known tokens