with tab1 as (select POOL_NAME, SENDER, TX_HASH, PLATFORM, ORIGIN_FROM_ADDRESS

from ethereum.core.ez_dex_swaps

where block_timestamp::date <= current_date - 1

and SENDER = ORIGIN_FROM_ADDRESS and platform ilike 'uniswap%'

),

tab2 as (select POOL_NAME, SENDER, TX_HASH, PLATFORM, ORIGIN_FROM_ADDRESS

FROM ethereum.core.ez_dex_swaps

where block_timestamp::date <= current_date - 1

and SENDER != ORIGIN_FROM_ADDRESS and platform ilike 'uniswap%'

and SENDER in (select ADDRESS from ethereum.core.dim_labels where label ilike 'uniswap%')),

tab3 as

(select * from tab1

union all

select * from tab2),

tab4 as (select POOL_NAME, count (distinct tx_hash) as count_transactions,

count (distinct origin_from_address) as count_users, count_transactions/count_users as avg_txn_per_user

from tab3 group by 1 order by 3 desc limit 10),

tab5 as (select POOL_NAME, SENDER, TX_HASH, PLATFORM, ORIGIN_FROM_ADDRESS, label

from ethereum.core.ez_dex_swaps left join ethereum.core.dim_labels

on SENDER = address

where platform ilike 'uniswap%' and block_timestamp::date <= current_date - 1

and SENDER in (select ADDRESS from ethereum.core.dim_labels where label not like 'uniswap%')),

tab6 as (select POOL_NAME, count (distinct tx_hash) as count_transactions,

count (distinct origin_from_address) as count_users, count_transactions/count_users as avg_txn_per_user

from tab5 group by 1 order by 3 desc limit 10)

select *, 'FRONTEND' as type from tab4