I want to calculate spikes on my trading data. I have candles aggregated at 30 seconds intervals, and I basically want to show a flag 'spike' if volume is bigger than twice the latest record for the same symbol. Otherwise it should display 'normal'
I have this query
DECLARE
@anchor_date := timestamp_floor('30s', now()),
@start_date := dateadd('h', -7,@anchor_date),
@symbol := 'SPX 250815P05070000'
WITH prev_trades AS (
SELECT
timestamp,
symbol,
size AS trade_size,
LAG(size) OVER (partition by symbol ORDER BY timestamp) AS prev_trade_size
FROM top_of_book
WHERE timestamp between @start_date and @anchor_date
AND symbol = @symbol
)
SELECT
*
FROM prev_trades
;
This works and gives me timestamp, symbol, trade_size, prev_trade_size.
What I cannot figure out now is how to show a label if prev_trade_size is bigger than 2 * trade_size
Answer
This can be solved with just a case statement at the end of the query
DECLARE
@anchor_date := timestamp_floor('30s', now()),
@start_date := dateadd('h', -7,@anchor_date),
@symbol := 'SPX 250815P05070000'
WITH prev_trades AS (
SELECT
timestamp,
symbol,
size AS trade_size,
LAG(size) OVER (partition by symbol ORDER BY timestamp) AS prev_trade_size
FROM top_of_book
WHERE timestamp between @start_date and @anchor_date
AND symbol = @symbol
)
SELECT
*,
CASE WHEN trade_size > 2 * prev_trade_size THEN 'spike' ELSE 'normal' END AS spike_flag
FROM prev_trades
;