SELECT

hour,

symbol,

price,

ABS(price - 0.75) as peg_deviation,

((price - 0.75) * 100 / 0.75) as peg_deviation_percentage,

CASE

WHEN price > 0.75 THEN 'Above SGD Rate'

WHEN price < 0.75 THEN 'Below SGD Rate'

ELSE 'At SGD Rate'

END as peg_status

FROM avalanche.price.ez_prices_hourly

WHERE token_address = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')

AND hour < CURRENT_TIMESTAMP()

ORDER BY hour DESC

SELECT

trunc(hour,'day') as hour,

symbol,

round(avg(price),3) as price,

avg(peg_deviation) as peg_deviation,

avg(ROUND(peg_deviation_percentage, 4)) as peg_deviation_percentage

FROM xsgd_prices

group by 1,2 order by 1 desc