date_trunc('day', block_timestamp) as block_day,

tx_id,

msg_value:coins[0]:denom as currency,

msg_value:coins[0]:amount/1e6 as transfer_amount

from terra.msgs

where msg_value:contract::string = 'terra10nmmwe8r3g99a9newtqa7a75xfgs2e8z87r2sf'

and tx_status = 'SUCCEEDED'

and msg_value:execute_msg:submit_vaa is null

and array_size(msg_value:coins) > 0),

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

),

raw_volume_price as (select

raw_volume.block_day,

raw_volume.tx_id,

raw_volume.currency,

raw_volume.transfer_amount,

luna_price.price

from raw_volume, luna_price

where raw_volume.block_day = luna_price.block_day

),

hist_values as (select block_day, iff(currency = 'uluna', transfer_amount*price, transfer_amount) as net_volume_ust

from raw_volume_price)

select block_day, sum(net_volume_ust) as volume from hist_values

group by block_day

order by block_day desc