SELECT DISTINCT from_address as address

FROM berachain.testnet.fact_transactions

),

dex_users AS (

SELECT

COUNT(DISTINCT b.address) as total_bera_users,

COUNT(DISTINCT CASE WHEN s.trader IS NOT NULL THEN b.address END) as users_with_dex_activity

FROM bera_users b

LEFT JOIN crosschain.defi.ez_dex_swaps s

ON LOWER(b.address) = LOWER(s.trader)

AND s.block_timestamp >= DATEADD(day, -30, CURRENT_DATE)

SELECT

'Users with DEX activity' as category,

users_with_dex_activity as user_count,

(users_with_dex_activity * 100.0 / total_bera_users) as percentage

FROM dex_users

UNION ALL

SELECT

'Users without DEX activity' as category,

(total_bera_users - users_with_dex_activity) as user_count,

((total_bera_users - users_with_dex_activity) * 100.0 / total_bera_users) as percentage

FROM dex_users;