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

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.

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

Last updated