date_trunc('day',block_timestamp) as dt,

sum(msg_value:execute_msg:send:amount /POW(10,6)) as collateral_provide,

'bLUNA' as basset

FROM terra.msgs

WHERE msg_value:execute_msg:send:msg:deposit_collateral IS NOT NULL

and msg_value:contract::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun'

and tx_status = 'SUCCEEDED' and dt > '2021-08-10'

group by 1,3),

bluna_w as (select date_trunc('day', block_timestamp) as dt,

sum(msg_value:execute_msg:withdraw_collateral:amount/1e6) as amount_withdraw,

'bLUNA' as basset

from terra.msgs

where msg_value:execute_msg:withdraw_collateral is not null

and msg_value:contract::string = 'terra10cxuzggyvvv44magvrh3thpdnk9cmlgk93gmx2'

and tx_status = 'SUCCEEDED'

group by 1,3),

bluna as (select date_trunc('day', block_timestamp) as dt, symbol, avg(price_usd) as price_beth

from terra.oracle_prices

where symbol = 'bETH token contract'

group by 1,2)

select a.dt, a.collateral_provide, b.amount_withdraw, (a.collateral_provide - b.amount_withdraw) as net_beth, c.price_beth, (net_beth*c.price_beth) as Net_bETH_USD,

SUM(net_beth_usd) OVER(ORDER BY a.dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Cumulative_Net_bETH

from bluna_p a

left join bluna_w b on a.dt = b.dt

left join bluna c on a.dt = c.dt