select date_trunc (hour,block_timestamp) as date1,

median (to_amount/from_amount) as LUNA_Price

from terra.core.ez_swaps

where from_currency = 'uluna'

and to_currency = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'

and to_amount < 1e8 and from_amount < 1e8

and block_timestamp >= '2023-01-01'

group by 1 having LUNA_Price < 2 and LUNA_Price > 1.22 and LUNA_Price != 1.333487

order by 1),

maintable as (

select date_trunc (hour,block_timestamp) as date,

LUNA_Price,

case when block_timestamp::date = '2023-01-09' then '9 January'

else 'Other Days' end as timespan,

case when from_currency = 'uluna' then 'Swaps From LUNA'

when to_currency = 'uluna' then 'Swaps To LUNA'

else null end as swap_type,

count (distinct tx_id) as TX_Count,

sum (case when from_currency = 'uluna' then from_amount/pow(10,from_decimal) when to_currency = 'uluna' then to_amount/pow(10,to_decimal) end) as Volume,

avg (case when from_currency = 'uluna' then from_amount/pow(10,from_decimal) when to_currency = 'uluna' then to_amount/pow(10,to_decimal) end) as Average_Volume

from terra.core.ez_swaps t1 join lunapricet t3 on date_trunc(hour,t1.block_timestamp) = t3.date1

where tx_succeeded = 'TRUE'

and block_timestamp >= '2023-01-01'

and swap_type is not null

group by 1,2,3,4)

select * from maintable

where swap_type = 'Swaps From LUNA'