with swap_data as (

-- Sell swaps

SELECT block_timestamp as time,

tx:tx_result:log[0]:events[5]:attributes[6]:value/1e6 as luna_amount,

tx:tx_result:log[0]:events[5]:attributes[7]:value/1e6 as usd_amount

FROM terra.core.fact_transactions

-- Get transactions of the axlUSD - LUNA pool in Astroport

WHERE block_timestamp::DATE > '2023-01-01'

AND tx:body:messages[0]:contract = 'terra1fd68ah02gr2y8ze7tm9te7m70zlmc7vjyyhs6xlhsdmqqcjud4dql4wpxr'

AND tx:tx_result:log[0]:events[5]:attributes[4]:value = 'uluna' --offer_asset

UNION ALL

-- Buy swaps

SELECT block_timestamp as time,

tx:tx_result:log[0]:events[5]:attributes[7]:value/1e6 as luna_amount,

tx:tx_result:log[0]:events[5]:attributes[6]:value/1e6 as usd_amount

FROM terra.core.fact_transactions

-- Get transactions of the axlUSD - LUNA pool in Astroport

WHERE block_timestamp::DATE > '2023-01-01'

AND tx:body:messages[0]:contract = 'terra1fd68ah02gr2y8ze7tm9te7m70zlmc7vjyyhs6xlhsdmqqcjud4dql4wpxr'

AND tx:tx_result:log[0]:events[5]:attributes[5]:value = 'uluna' --buy_asset

SELECT date_trunc('hour',time) as time,

round(usd_amount/luna_amount,3) as luna_price

FROM swap_data

ORDER BY 2 DESC