Skip to main content

​
prices.hour

​
Overview

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

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. 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_coinpaprika.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:

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
  • 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_coinpaprika.hour for more reliable pricing where available
  • Data validation: Always validate prices for critical applications, especially for lesser-known tokens
⌘I