SELECT

date,

price,

MAX(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max

FROM (

SELECT

CAST(hour AS DATE) AS date,

AVG(price) AS price

FROM

aptos.price.ez_prices_hourly

WHERE

token_address = '0x96baeee6d7a4a8cd712144d1225cfcb6c26d0c6fefd463bd77a878e4526c7411::hair_coin::HairCoin'

GROUP BY

date

)

),

drawdowns AS (

SELECT

date,

price,

running_max,

(price - running_max) / running_max AS drawdown

FROM

running_max

),

max_drawdown AS (

SELECT

MIN(drawdown) AS max_drawdown

FROM

drawdowns

SELECT

max_drawdown

FROM

max_drawdown;