select

FROM_ADDRESS

,MAX_PRIORITY_FEE_PER_GAS

,TO_ADDRESS

,TX_FEE

,TX_HASH

from ethereum.core.fact_transactions

where BLOCK_TIMESTAMP>='2023-04-01'

and CHAIN_ID='1'

and STATUS='SUCCESS'

and TX_TYPE='2'),

tab2 as (

select ADDRESS

,ADDRESS_NAME

,LABEL

,LABEL_TYPE

,LABEL_SUBTYPE

from ethereum.core.dim_labels)

select

ADDRESS_NAME

,count(distinct FROM_ADDRESS) as users

,count(distinct tab1.TX_HASH) as txs

,sum(TX_FEE) as fees

,sum((100*TX_FEE)/(MAX_PRIORITY_FEE_PER_GAS+100)) as burnt

from tab1

left join tab2

on tab1.TO_ADDRESS=tab2.ADDRESS

where LABEL_TYPE is not null

and LABEL_TYPE in('token')

and LABEL_SUBTYPE in('token_contract')

group by 1

order by 4 desc

limit 10