​
prices.hour

​
Overview

The prices.hour table provides hourly token price data across all supported blockchains. It contains the last known price 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
decimalsintToken decimals
volumedoubleTrading volume in USD (from price source)
sourcevarcharData source (‘coinpaprika’ or ‘dex.trades’)
source_timestamptimestampExact timestamp of the source data point

​
Implementation Details

The hourly prices are built through these steps:
  1. Collect sparse price observations from different sources
  2. Group by hour, taking the most recent price observation 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.

​
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