distinct address,

from solana.core.dim_labels

where LABEL_TYPE='dex' and LABEL_SUBTYPE='pool' and LABEL='orca'

),

final as (select

'Orca Whirlpools' as AMM,

(s.block_timestamp) as date,

tx_from as user

from solana.core.fact_transfers s

left join solana.core.fact_events b on s.tx_id=b.tx_id

where PROGRAM_ID='whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc'

and TX_TO in (select address from orca_pools)

select

'Uniswap V3' as AMM,

(block_timestamp) as date,

LIQUIDITY_PROVIDER as user

from ethereum.uniswapv3.ez_lp_actions where ACTION='INCREASE_LIQUIDITY'

),

final_2 as ( select lag(date, 1) ignore nulls over (partition by user order by date asc) as tx_date,

datediff('day',tx_date, date) as n_days, AMM, user

from final

qualify tx_date is not null

select

AMM,

count(DISTINCT(user)) as total_user,

case when n_days = 0 then 'Same Day'

when n_days = 1 then '1 Day'

when n_days between 1 and 7 then 'Less than a week'

when n_days between 8 and 30 then 'Less than a month'