​
prices.hour

​
Overview

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

​
Table Schema

ColumnTypeDescription
blockchainvarcharBlockchain identifier (e.g., ‘ethereum’, ‘arbitrum’)
contract_addressvarbinaryToken contract address (fixed address for native tokens)
symbolvarcharToken symbol (e.g., ‘ETH’, ‘USDC’)
timestamptimestampHour timestamp (00:00, 01:00, etc. UTC of each hour)
pricedoubleToken price in USD (volume-weighted average)
decimalsintToken decimals
volumedoubleTrading volume in USD (from price source)
sourcevarcharData source (‘coinpaprika’ or ‘dex.trades’)

​
Implementation Details

The hourly prices are built through these steps:
  1. Collect sparse price observations from different sources
  2. Group by hour, calculating volume-weighted average price per hour
  3. Fill missing hours with the previous hour’s price (forward filling)
  4. Set a 7-day (168 hour) expiration for forward filling to avoid stale data

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

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:

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