select

tx_id,

date_trunc('day', block_timestamp) as block_day,

iff(array_size(fee[0]:amount) = 2, array_construct(fee[0]:amount[0]:amount/1e6, fee[0]:amount[1]:amount/1e6), array_construct(fee[0]:amount[0]:amount/1e6)) as total_fee

from terra.transactions

where tx_id in (select tx_id from terra.msgs

where msg_value:contract::string = 'terra10nmmwe8r3g99a9newtqa7a75xfgs2e8z87r2sf'

and msg_value:execute_msg:initiate_transfer:recipient_chain::string = '5'

and tx_status = 'SUCCEEDED')),

luna_price as (

SELECT date_trunc('day', block_timestamp) as block_day, AVG(price_usd) as price

FROM terra.oracle_prices

WHERE symbol = 'LUNA'

AND block_day >= '2021-10-6'

GROUP BY 1

),

final_temp as (select raw_prices.tx_id,

raw_prices.block_day,

raw_prices.total_fee,

luna_price.price,

raw_prices.total_fee[0]*luna_price.price as luna_times_price,

iff(raw_prices.total_fee[1] is null, 0, raw_prices.total_fee[1]) as ust_fee,

luna_times_price+ust_fee as total_final_fee from raw_prices

inner join luna_price

where raw_prices.block_day = luna_price.block_day)

select block_day, avg(total_final_fee)

from final_temp

group by 1

order by 1 desc