token_prices

Schema: public

Description

The token_prices table is used to store data on the USD prices of various tokens in the cryptocurrency market. It contains information such as the quote time, token name, price, source, and market cap. This table is useful for tracking the performance of different tokens and analyzing trends in the market.

Also, if less granular prices over time are needed, a continuous aggregate view named daily_token_prices also exists; this aggregate records the open, high, low, and close prices for each day.

Columns

Granular prices

Name	Type	Description	Default
id	character	Unique identifier for each row	None
quote_time	timestamptz	Time at which the price was quoted	None
token	text	Name of the token	None
price	numeric	Current price of the token (in USD)	None
source	text	Source of the price data (e.g. CoinGecko, CoinMarketCap, etc.)	None
market_cap	numeric	Market cap of the token; not provided for all sources/tokens	None (nullable)

Daily prices

Name	Type	Description	Default
quote_day	timestamptz	The day which the price was quoted	None
token	text	Name of the token	None
source	text	Source of the price data (e.g. CoinGecko, CoinMarketCap, etc.)	None
first_price	numeric	The first price recorded for the day (i.e. "Open" price)	None
last_price	numeric	The last price recorded for the day (i.e. "Close" price)	None
maximum_price	numeric	The highest price recorded for the day (i.e. "High" price)	None
minimum_price	numeric	The lowest price recorded for the day (i.e. "Low" price)	None

Examples

Last week of KAVA prices

select *
from token_prices
where token = 'KAVA'
and quote_time > now() - '1 week'::interval
order by quote_time;

Last known price for KAVA, ATOM, and USDC

select
	token,
	max(quote_time),
	last(source, quote_time),
	last(price, quote_time)
from token_prices
where token in ('KAVA', 'ATOM', 'USDC')
and quote_time > now() - '1 week'::interval
group by token;

Get a year's worth of OHLC (candlestick) data using daily price aggregate

select
  token,
  quote_day,
  source,
  first_price,
  maximum_price,
  minimum_price,
  last_price
from daily_token_prices
where token = 'USDC'
and quote_day > now() - '1 year'::interval;