Latest trades
Real-time buy and sell orders from Coinbase, covering exchanges amongst 20+ assets.
SELECT timestamp,
left(symbol,strpos(symbol,'-')-1) asset,
right(symbol,length(symbol)-strpos(symbol,'-')) counter,
case when side = 'buy' then amount else -amount end quantity,
case when side = 'buy' then -amount*price else amount*price end consideration,
case when (now()-timestamp)/1000000<0.3 then 'x' else '' end new
from trades where dateadd('m', -1, now()) < timestamp order by timestamp desc
limit 50
Real-time trades
Filled exchange orders between 20+ crypto assets, with USD notional along the y-axis. Positive values are buy orders, negative are sell orders.
SELECT timestamp time, symbol,
case when side ='buy' then amount*price
else -1*amount*price end trade
from trades where dateadd('m', -1, now()) < timestamp and
symbol like '%-USD'
Powered byQuestDB andGrafana
Volume heatmap
Trade volume (USD notional) distribution per asset for the past 5 minutes.
select timestamp time, left(symbol,strpos(symbol,'-')-1) asset, sum(abs(amount)) from
trades where dateadd('m', -5, now()) < timestamp and
right(symbol,length(symbol)-strpos(symbol,'-'))= 'USD'
sample by 5s
order by symbol, time
Moving averages for Bitcoin price
Moving averages on the USD price of Bitcoin in the past 5 minutes, over 10-, 30-, and 45-second windows.
SELECT timestamp time, symbol, price as priceBtc
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 10 seconds PRECEDING AND CURRENT ROW) movingAvg10Sec
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 30 seconds PRECEDING AND CURRENT ROW) movingAvg30Sec
, avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 45 seconds PRECEDING AND CURRENT ROW) movingAvg45Sec
FROM trades WHERE dateadd('m', -5, now()) < timestamp AND symbol = 'BTC-USD'
To demonstrate the power of QuestDB's analytics, we've created a live cryptocurrency dashboard.
Monitor executed trades on Coinbase from more than 20 assets in real time.
Hover over each panel to see what data it represents, and the QuestDB SQL query that powers it.
Try the queries out for yourself, or follow our tutorial series on building Grafana dashboards with market data.
Bitcoin Candlestick Chart (OHLC)
An OHLC (Open, High, Low, Close) chart for the notional price in USD of Bitcoin, sampled every second over the past 5 minutes.
SELECT timestamp as time, first(price) as open, last(price) as close, min(price) as lo, max(price) as hi, sum(amount) as vol
FROM trades
WHERE symbol = 'BTC-USD' and dateadd('m', -5, now()) < timestamp
SAMPLE BY 1s ALIGN TO CALENDAR
Rolling BTC-ETH correlation coefficient
Calculation of the Pearson correlation coefficient (ρ) between the prices of Ethereum and Bitcoin. Data is sampled every minute over the past 12 hours, and correlation is calculated over hour- and day-long windows.
WITH data as (
WITH
BTCUSD as (select timestamp, price from trades where dateadd('h', -12, now()) < timestamp and symbol = 'BTC-USD' ),
ETHUSD as (select timestamp, price from trades where dateadd('h', -12, now()) < timestamp and symbol = 'ETH-USD' )
SELECT BTCUSD.timestamp, corr(BTCUSD.price,ETHUSD.price) from BTCUSD asof join ETHUSD sample by 1m)
SELECT timestamp
, avg(corr) over(ORDER BY timestamp range between 1 hour preceding and current row) hourly_corr_rolling
, avg(corr) over(ORDER BY timestamp range between 24 hour preceding and current row) daily_corr_rolling
from data
High performance ingest & slick visualizations.
Perfect for financial data.