SELECT date,

median(CASE when symbol = 'SEI' then usd_price end) as sei_price,

((sei_price - LAG(sei_price)over(ORDER BY date))/LAG(sei_price)over(ORDER BY date))*100 as daily_change_sei,

CASE WHEN daily_change_sei>=0 THEN 'pos' ELSE 'neg' END as direction_sei,

median(CASE when symbol = 'WBTC' then usd_price end)as btc_price,

((btc_price - LAG(btc_price)over(ORDER BY date))/LAG(btc_price)over(ORDER BY date))*100 as daily_change_btc,

CASE WHEN daily_change_btc>=0 THEN 'pos' ELSE 'neg' END as direction_btc,

median(CASE when symbol = 'ATOM' then usd_price end) as atom_price,

((atom_price - LAG(atom_price)over(ORDER BY date))/LAG(atom_price)over(ORDER BY date))*100 as daily_change_atom,

CASE WHEN daily_change_atom>=0 THEN 'pos' ELSE 'neg' END as direction_atom

FROM (

SELECT

date_Trunc('d',RECORDED_HOUR) as date,

symbol,

median(price) as usd_price

FROM osmosis.price.ez_prices

WHERE symbol IN ('SEI' , 'WBTC', 'ATOM')

AND date BETWEEN '2023-08-18' AND current_date-1

GROUP BY 1 , 2

GROUP BY 1

ORDER BY date DESC