project_name,

count (distinct tx_id) as trxs,

count (distinct liquidity_provider_address) as users

from (

select block_timestamp as lp_date,

t1.tx_id,

liquidity_provider_address,

currency,

amount,

from osmosis.core.fact_liquidity_provider_actions t1 join (

select block_timestamp as swap_date,

tx_id,

trader,

to_currency,

to_amount,

from osmosis.core.fact_swaps

where tx_id in (select tx_id from (

select * from (

select block_timestamp,

tx_id,

tx_from,

row_number () over (partition by tx_from order by block_timestamp) as rn

from osmosis.core.fact_transactions)

where rn in ('1','2')) where rn = '1')) t2 on t1.liquidity_provider_address = t2.trader and t1.currency = t2.to_currency and t1.block_timestamp > t2.swap_date

where action = 'pool_joined'

and t1.tx_id in (select tx_id from (

select * from (

select block_timestamp,

tx_id,

tx_from,

row_number () over (partition by tx_from order by block_timestamp) as rn

from osmosis.core.fact_transactions)

where rn in ('1','2')) where rn ='2')) t1 left outer join osmosis.core.dim_tokens t2 on t1.currency = t2.address