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 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 'Uniswap Frontend' as type, 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),

tab5 as (select 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 label as type, 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)

select * from tab4

union all

select * from tab6