date_trunc('day', block_timestamp) as dt,

sum(event_attributes:"0_amount"/1e6) as amount,

event_attributes:asset_token::string as massets

from terra.msg_events

where event_attributes:"0_contract_address"::string = 'terra17f7zu97865jmknk7p2glqvxzhduk78772ezac5'

and event_attributes:"0_action"::string = 'unbond'

and event_type = 'wasm' --unbond/withdraw from mirror

and block_timestamp >= CURRENT_DATE - 30

group by 1,3),

labels as (select address, address_name

from terra.labels

where address in (select massets from raw)

),

price as (select date_trunc('day', block_timestamp) as dt, currency, avg(price_usd) as price

from terra.oracle_prices

where currency in (select massets from raw)

group by 1,2),

final as (select a.dt, a.amount, b.address_name, concat(b.address_name,'-UST') as LP_Pair, c.price, (a.amount*c.price*2) as Volume_USD

from raw a

join labels b on a.massets = b.address

join price c on a.dt = c.dt and a.massets = c.currency

where a.massets is not null and a.amount > 0)

select LP_pair, sum(Volume_USD)

from final

group by 1