Token Prices

Fetch hourly token prices of tokens to power your analytic needs.

Hourly Token Prices

common.prices.hourly provides hourly price data for tokens across various blockchains, combining external price feeds with contract addresses and computed DEX prices.

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 

Table Columns

Unique key: timestamp, chain, address

Column	Description	Example
timestamp	Hourly timestamp.	2020-12-03 09:00:00
chain	Blockchain (in lowercase).	ethereum
address	Token contract address. For native tokens on EVM chains, they will be represented by the zero address.	0x0000000000000000000000000000000000000000
symbol	Token symbol in lower casing.	eth
price	USD Value of the token	606.345
decimals	Token decimals (if present).	18
is_native	Boolean flag indicating whether the token is a native (gas) token.	TRUE
is_dex_only	Boolean flag indicating whether the price source was solely computed from DEX trades.	FALSE
_created_at	Timestamp of entry creation.	2024-07-02 04:12:42
_updated_at	Timestamp of entry update.	2024-07-02 04:12:42

---

Centralized Exchange (CEX) Token Prices

common.prices.token_prices_hourly &common.prices.token_prices_hourly_latest contains the hourly prices from Centralized Exchanges.

This table only includes tokens paired with quote assets: USDC, USDT, and USD. The underlying price in this table reflects the closing price of the asset at the hour.

For one Base Asset there can be multiple quote asset.

Sample Query

Avg hourly ETH USD price across Centralized Exchanges in the last 24 hours

select 
    timestamp, 
    base_asset_symbol, 
    avg(price) as avg_usd_price,
    listagg(distinct source, ', ') as price_sources,
    listagg(distinct symbol, ', ') as price_symbols
from common.prices.token_prices_hourly
where 1=1 
    and base_asset_symbol = 'ETH' 
    and quote_asset_symbol in ('USD', 'USDT', 'USDC')
and timestamp >= current_timestamp - interval '24 hours'
group by all
order by timestamp desc 

Table Columns

Unique key: timestamp, symbol, source

Column Name	Descriptipn	Example
timestamp	Hourly timestamp of the token price.	2022-12-14T23:00:00
symbol	The currencies pairs that are referenced for price data. The first currency is called the base currency and the second currency is called the quote currency.	ETH/USDC
base_asset_symbol	The base asset is traded against the quote asset.	ETH
quote_asset_symbol	The base asset is traded against the quote asset. Sometimes known as the counter currency.	USDC
price	USD equivalent price of the base_asset_symbol	1308.22
source	The source of the token price.	binance_us