Volume Spike Detector

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
;