with final as

(

select block_timestamp,

date_trunc('day', block_timestamp) as date,

date_trunc('hour', block_timestamp) as hour,

date_trunc('minute', block_timestamp) as minute,

case

when from_currency = 'uluna' then from_amount / 1e6

when to_currency = 'uluna' then to_amount / 1e6

end as luna_amount,

case

when from_currency = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'

then from_amount / 1e6

when to_currency = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'

then to_amount / 1e6

end as usdc_amount

from terra.core.ez_swaps

where pool_id =

'terra1fd68ah02gr2y8ze7tm9te7m70zlmc7vjyyhs6xlhsdmqqcjud4dql4wpxr' -- axlUSDC - LUNA pool address

and TX_SUCCEEDED = TRUE

)

select date,

coalesce(price, last_price) as price

from (

select *,

lag(price) ignore nulls over (order by date) as last_price

from

(

select date,

sum(usdc_amount) / sum(luna_amount) as price

from final

group by 1

)

)

),