​
Hourly Token Prices

common.prices.hourly provides hourly price data by token address across various Blockchains combined with token prices computed from DEX trades.

We preferentially select non-DEX data. That is, if for the same hour, there is a same chain + address that has DEX token prices and externally ingested prices, we will select the externally ingested price.

This table is unique by timestamp (hour), chain and address.

​
Sample Query

Using common.prices.hourly to investigate the USD volume of Flash loans performed on Balancer in the last 7 days

select
    date(block_timestamp) as date,
    params['token']::varchar as token,
    hourly.symbol as token_symbol,
    sum(params ['amount'] / pow(10, hourly.decimals) * hourly.price) as usd_volume
from
    ethereum.decoded.logs
    left join common.prices.hourly on date_trunc('hour', block_timestamp) = hourly.timestamp
    and hourly.address = token
    and chain = 'ethereum'
where logs.block_timestamp >= current_timestamp - interval '7 days'
    and logs.address = '0xba12222222228d8ba445958a75a0704d566bf2c8'
    and logs.topic0 = '0x0d7d75e01ab95780d3cd1c8ec0dd6c2ce19e3a20427eec8bf53283b6fb8e95f0'
group by all

To get the hourly USD price of ETH, filter by chain = ‘ethereum’ and is_native = true:

select *
from common.prices.hourly
where chain = 'ethereum' and is_native = true

​
Table Columns

Unique key: timestamp, chain, address

ColumnDescriptionExample
timestampHourly timestamp.2020-12-03 09:00:00
chainBlockchain (in lowercase).ethereum
addressToken contract address equivalent on the blockchain.
For native tokens on EVM chains, they will be represented by the zero address.		0x0000000000000000000000000000000000000000
symbolToken symbol in lower casing.eth
priceUSD Value of the token at the hour.606.345
decimalsToken decimals (if present).18
is_nativeBoolean flag indicating whether the token is a native (gas) token.TRUE
is_dex_onlyBoolean flag indicating whether the price source was solely computed from DEX trades.FALSE
_created_atTimestamp of entry creation.2024-07-02 04:12:42
_updated_atTimestamp of entry update.2024-07-02 04:12:42