– Find the average gas price per galon in one month intervals
– Note how we are using the SAMPLE BY extension to aggregate records at regular intervals
SELECT timestamp, avg(galon_price) FROM gas_prices SAMPLE BY 1M;
- Track different aggregations for the BTC-USD stock price evolution for the past day in 15 minutes intervals.
SELECT
timestamp,
first(price) AS open,
last(price) AS close,
min(price),
max(price),
sum(amount) AS volume
FROM trades
WHERE symbol = ‘BTC-USD’ AND timestamp > dateadd(‘d’, -1, now())
SAMPLE BY 15m ALIGN TO CALENDAR;
Find the latest value available for each symbol (crypto pair)
SELECT * FROM trades WHERE symbol in (‘BTC-USD’, ‘ETH-USD’) LATEST ON timestamp PARTITION BY symbol;
VWAP
SELECT
timestamp,
vwap(price, amount) AS vwap_price,
sum(amount) AS volume
FROM trades
WHERE symbol = ‘BTC-USD’ AND timestamp > dateadd(‘d’, -1, now())
SAMPLE BY 15m ALIGN TO CALENDAR;
/* Calculates the daily returns in % for BTC-USD for the last 30 days.
We use SAMPLE BY to aggregate the data at regular intervals - in this case daily.
The previous value is obtained with the first_value() window function */
select timestamp, round(100*(last_price - prev_price) / prev_price, 2) as return_pct
from
(
select timestamp, last_price, first_value(last_price) over (rows between 1 preceding and 1 preceding) as prev_price
from
(
SELECT timestamp, last(price) last_price
FROM trades
WHERE symbol = ‘BTC-USD’
and timestamp > dateadd(‘d’, -30, now())
SAMPLE BY 1d ALIGN TO CALENDAR
)
)
where prev_price is not null;
/* Calculates the rolling moving average of BTC-USD using Window Functions */
SELECT timestamp time, symbol, price as priceBtc,
avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 15 days PRECEDING AND CURRENT ROW) moving_avg_15_days,
avg(price) over (PARTITION BY symbol ORDER BY timestamp RANGE between 30 days PRECEDING AND CURRENT ROW) moving_avg_30_days
FROM trades
WHERE timestamp > dateadd(‘M’, -1, now())
AND symbol = ‘BTC-USD’;