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

# Hourly Prices

> Hourly 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.hour` table provides hourly token prices using the same **hybrid approach** as all other price tables:

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

This provides volume-weighted average prices for each hour, ensuring a continuous time series for more granular price analysis than daily data.

## 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 | Hour timestamp (00:00, 01:00, etc. UTC of each hour)     |
| 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="hour" />

## Implementation Details

The hourly prices are built through these steps:

1. **Source prioritization**: Coinpaprika prices for \~2,000 major tokens, DEX-derived prices for long tail tokens
2. **Data aggregation**: Group by hour, calculating volume-weighted average price per hour
3. **Forward filling**: Fill missing hours with the previous hour's price (forward filling)
4. **Expiration**: Set a 7-day (168 hour) expiration for forward filling to avoid stale data

### Source-Specific Variants

#### `prices_external.hour`

For purely coinpaprika-based hourly prices:

* **Coverage**: Only tokens with Coinpaprika listings (\~2,000 tokens)
* **Source**: External exchange-aggregated hourly pricing
* **Use case**: When you need stable, externally-validated hourly pricing
* **Schema**: Same as `prices.hour` but `source` is always 'coinpaprika'

#### `prices_dex.hour`

For purely DEX-derived hourly prices:

* **Coverage**: All tokens with sufficient DEX trading volume
* **Source**: On-chain DEX trading activity only
* **Use case**: When you need pricing that reflects actual on-chain liquidity
* **Schema**: Same as `prices.hour` but `source` is always 'dex.trades'

## Usage

This table is useful for intraday analysis and tracking price movements with higher granularity than daily data. It's suitable for examining price patterns within a day or across multiple days.

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

## Latency and Update Frequency

The `prices.hour` table is updated hourly based on the upstream data pipeline. As a result, prices typically have a latency of approximately 1 hour from real-time.

## Usage Examples

Here are some examples of how to use the prices tables.

### Get hourly ETH prices for the last day:

```sql theme={null}
SELECT
  timestamp,
  price
FROM prices.hour
WHERE blockchain = 'ethereum'
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND timestamp >= NOW() - INTERVAL '1' DAY
ORDER BY timestamp
```

### Calculate volatility by hour of day:

```sql theme={null}
SELECT
  extract(hour from timestamp) as hour_of_day,
  avg(abs(price - lag(price) OVER (PARTITION BY blockchain, contract_address ORDER BY timestamp))
      / lag(price) OVER (PARTITION BY blockchain, contract_address ORDER BY timestamp)) as avg_hourly_change
FROM prices.hour
WHERE blockchain = 'ethereum'
AND contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
AND timestamp >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1
```

## Data Quality Notes

* Prices older than 7 days (168 hours) will not be forward-filled to avoid using stale data
* Native tokens (like ETH, BNB) are assigned fixed addresses for consistency
* Hourly data is useful for analyzing intraday patterns and short-term price movements
* 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.hour` for more reliable pricing where available
* **Data validation**: Always validate prices for critical applications, especially for lesser-known tokens