Daily Prices

Historical daily price data for tokens across all supported blockchains, aggregated at 00:00 UTC

prices.day

Overview

The prices.day table provides daily token price data across all supported blockchains. It contains volume-weighted average prices for each day, ensuring a continuous time series for price analysis.

Table Schema

Column	Type	Description
blockchain	varchar	Blockchain identifier (e.g., ‘ethereum’, ‘arbitrum’)
contract_address	varbinary	Token contract address (fixed address for native tokens)
symbol	varchar	Token symbol (e.g., ‘ETH’, ‘USDC’)
timestamp	timestamp	Date timestamp (00:00 UTC of each day)
price	double	Token price in USD (volume-weighted average)
decimals	int	Token decimals
volume	double	Trading volume in USD (from price source)
source	varchar	Data source (‘coinpaprika’ or ‘dex.trades’)

Implementation Details

The daily prices are built through these steps:

1. Collect sparse price observations from different sources
2. Group by day, calculating volume-weighted average price per day
3. Fill missing days with the previous day’s price (forward filling)
4. Set a 30-day expiration for forward filling to avoid stale data

Usage

This table is ideal for analyzing daily price trends and performing day-over-day comparisons. It provides a good balance between data granularity and query performance for longer-term analyses.

Note: The underlying data sources are aligned to 5-minute intervals for consistency and improved data quality.

Latency and Update Frequency

The prices.day table is updated daily at 00:00 UTC, providing the closing price for the previous day. Data is typically available within 30 minutes of the daily close.

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
• Data Limitations: While filtering helps improve data quality, users should always validate prices for critical applications