-- currency volume joined the pools between block_id 4712064 and 4713064

SELECT b.currency, sum(b.amount/pow(10, b.decimal)) as total

FROM osmosis.core.fact_transactions as a JOIN osmosis.core.fact_liquidity_provider_actions as b

on a.tx_id = b.tx_id

WHERE a.tx_id IN (

select DISTINCT tx_id

FROM osmosis.core.fact_msgs

WHERE block_id BETWEEN 4707300 AND 4713064

AND msg_type = 'pool_joined'

) AND

b.amount > 0

GROUP BY b.currency

ORDER BY total DESC

), currency_volume_left AS (

-- currency volume leaving the pools between block_id 4712064 and 4713064

SELECT b.currency, sum(b.amount/pow(10, b.decimal)) as total

FROM osmosis.core.fact_transactions as a JOIN osmosis.core.fact_liquidity_provider_actions as b

on a.tx_id = b.tx_id

WHERE a.tx_id IN (

select DISTINCT tx_id

FROM osmosis.core.fact_msgs

WHERE block_id BETWEEN 4707300 AND 4713064

AND msg_type = 'pool_exited'

) AND

b.amount > 0

GROUP BY b.currency

ORDER BY total DESC

), avg_price AS (

SELECT symbol, avg(price) AS average_price

FROM osmosis.core.dim_prices

WHERE recorded_at BETWEEN '2022-06-07' AND '2022-06-09'

GROUP BY symbol

), usd_volume_joined AS (

SELECT sum(total*average_price)*(-1) AS usd_volume

FROM currency_volume_joined as a JOIN osmosis.core.dim_labels as b ON a.currency = b.address JOIN avg_price ON b.project_name = avg_price.symbol