main as (

select

tx_hash,

case

when platform ilike 'uniswap%' then 'Uniswap'

when platform ilike 'pancakeswap%' then 'Pancakeswap'

when platform ilike 'dodo%' then 'Dodo'

when platform ilike 'hashflow%' then 'Hashflow'

when platform ilike 'kyberswap%' then 'Kyberswap'

when platform ilike 'trader-joe%' then 'Trader Joe'

else initcap(platform)

end as program,

block_timestamp,

origin_from_address as user

from

ethereum.defi.ez_dex_swaps

where

block_timestamp :: date >= '2021-01-01'

),

user_activity as (

select

program,

from_address as user,

min(a.block_timestamp) as created_at,

abs(datediff('day', created_at, current_date)) as wallet_age

from

ethereum.core.fact_transactions a

join main on main.user = from_address

group by

1,

2

program,

avg(wallet_age) as avg_wallet_age