> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dune.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Daily Prices

> Daily token price data across 70+ blockchains - hybrid approach combining centralized exchange and decentralized exchange data for comprehensive coverage

export const TableSample = ({tableName, tableSchema}) => <>
    <div className="hidden dark:block">
      <iframe src={`https://dune.com/embeds/3419983/5785629?table_schema_t6f0df=${tableSchema}&table_name_t6f0df=${tableName}&darkMode=true`} style={{
  width: '100%',
  height: '500px',
  border: 'none',
  marginTop: '10px'
}} />
    </div>
    <div className="dark:hidden">
      <iframe src={`https://dune.com/embeds/3419983/5785629?table_schema_t6f0df=${tableSchema}&table_name_t6f0df=${tableName}`} style={{
  width: '100%',
  height: '500px',
  border: 'none',
  marginTop: '10px'
}} />
    </div>
  </>;

The `prices.day` table provides daily token prices using a **hybrid approach** that combines multiple data sources for maximum coverage:

1. **Coinpaprika prices** for tokens where we have reliable external data (\~2,000 major tokens, defined in [token lists](https://github.com/duneanalytics/spellbook/tree/main/dbt_subprojects/tokens/models/prices))
2. **DEX-derived prices** for the long tail of tokens not covered by coinpaprika

This hybrid methodology ensures comprehensive token coverage while maintaining price quality through different data sources optimized for different token categories.

## 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')              |

## Table Sample

<TableSample tableSchema="prices" tableName="day" />

## Implementation Details

The daily prices are built through these steps:

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](https://github.com/duneanalytics/spellbook/tree/main/dbt_subprojects/tokens/models/prices)
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

If you need prices from specific sources only:

#### `prices_external.day`

For purely coinpaprika-based daily prices:

* **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`

For purely DEX algorithm-based daily prices:

* **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'

**Example - Compare DEX vs combined pricing:**

```sql theme={null}
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

This table provides comprehensive daily price coverage by combining coinpaprika and DEX-derived sources. It's ideal for:

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

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

**For specific use cases**: If you need purely coinpaprika or purely DEX-derived prices, use the dedicated source-specific tables instead.

## 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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
-- 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