select distinct address, address_name, balance, balance_type, balance_usd from terra.daily_balances

where address_name like '%Terraswap%'

and date = CURRENT_DATE - 1

and balance > 0

),

price as (

select symbol , currency, avg(price_usd) as price_usd, date_trunc('day', block_timestamp) as dt1

from terra.oracle_prices

group by 1,2,4

),

msg as (

select distinct tx_id, date_trunc('day', block_timestamp) as dt, msg_value

from terra.msgs

where tx_status != 'FAILED'

and msg_value:execute_msg like '%swap%'

),

swap as (

select msg_value:coins[0]:amount/1e6 as amount_swap_in,

msg_value:coins[0]:denom::string as token_swap_in,

msg_value:contract::string as contract,

address_name as pairs,

msg_value:sender::string as trader,

dt

from msg m

left join terraswap ter on

m.msg_value:contract = ter.address

),

final_swap as (

select amount_swap_in, price_usd,(amount_swap_in * price_usd) as volume_swap, token_swap_in, contract, pairs,

case when contract = 'terra1c0afrdc5253tkp5wt7rxhuj42xwyf2lcre0s7c' then 'Terraswap bETH- UST pair'