SELECT date_trunc('day',recorded_at) AS dayt

,tokens.project_name

,tokens.address

,tokens.decimal

,avg(prices.price) AS avg_daily_token_price

FROM osmosis.core.dim_prices prices

JOIN osmosis.core.dim_tokens tokens

ON prices.symbol = tokens.project_name

GROUP BY 1,2,3,4)

SELECT sender

,SUM(deposited_token_volume_USD_by_depositor)

FROM (

SELECT sender

,decimal

,avg_daily_token_price

,SUM(amount/POW(10,decimal))*avg_daily_token_price AS deposited_token_volume_USD_by_depositor

FROM osmosis.mars.ez_redbank_actions a

JOIN avg_daily_tokens_prices b

ON a.currency = b.address

AND date_trunc('day', a.block_timestamp) = dayt

WHERE action = 'deposit'

AND currency = 'uosmo'

GROUP BY 1,2,3

UNION all

SELECT sender

,decimal

,avg_daily_token_price

,-SUM(amount/POW(10,decimal))*avg_daily_token_price

FROM osmosis.mars.ez_redbank_actions a

JOIN avg_daily_tokens_prices b

ON a.currency = b.address

AND date_trunc('day', a.block_timestamp) = dayt

WHERE action = 'withdraw'