from osmosis.core.fact_liquidity_provider_actions

where BLOCK_ID >= 4707300

and BLOCK_ID <= 4713064

and ACTION = 'pool_joined'

and TX_STATUS = 'SUCCEEDED') ,

add_address as ( select add_date , count(DISTINCT(tx_id)) as total_add, LIQUIDITY_PROVIDER_ADDRESS as add_address , LABEL as lb, sum(total) as add_amount

from osmosis.core.dim_labels y join add_liq x on y.address = x.currency

where total is not null

group by 1,3,4

order by 2 desc),

remove as ( select trunc(BLOCK_TIMESTAMP,'day') as remove_date , LIQUIDITY_PROVIDER_ADDRESS , tx_id , CURRENCY , amount/pow(10,decimal) as total

from osmosis.core.fact_liquidity_provider_actions

where BLOCK_ID >= 4707300 and BLOCK_ID <= 4713064

and ACTION = 'pool_exited'

and TX_STATUS = 'SUCCEEDED') ,

remove_address as ( select remove_date , count(DISTINCT(tx_id)) as total_remove,LIQUIDITY_PROVIDER_ADDRESS as remove_address , LABEL , sum(total) as remove_amount

from osmosis.core.dim_labels b join remove a on b.address = a.currency

where total is not null

group by 1,3,4

order by 2 desc),

total as ( select DATEDIFF(day,add_date , remove_date ) as days , add_address , remove_address , total_add ,total_remove, label, lb, add_amount , remove_amount

,add_amount - remove_amount as remain_amount

from add_address x join remove_address y on x.add_address = y.remove_address and x.lb = y.label

order by total_add desc),

price as ( select 'Cosmos' as token , '8.25' as price from osmosis.core.dim_prices

select 'Osmosis' as token , '1.1' as price from osmosis.core.dim_prices

select 'Wrapped Bitcoin' as token , '30000' as price from osmosis.core.dim_prices