​

prices.day

​

Overview

The prices.day table provides daily token price data across all supported blockchains. It contains the last known price for each day, ensuring a continuous time series for price analysis.

​

Table Schema

​

Implementation Details

The daily prices are built through these steps:

1. Collect sparse price observations from different sources
2. Group by day, taking the most recent price observation 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.

​

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