Table1 as ( select ADDRESS ,DECIMALS from ethereum.core.dim_contracts),

Table2 as ( select

f.block_timestamp ,ADDRESS ,ORIGIN_FROM_ADDRESS,tx_hash ,EVENT_INPUTS:value/pow(10,decimals) as native_amount

,native_amount*(select avg(PRICE) from ethereum.core.fact_hourly_token_prices

where TOKEN_ADDRESS=CONTRACT_ADDRESS and HOUR::date=block_timestamp::date) as price_usd

from ethereum.core.fact_event_logs f join Table1 on Table1.address = CONTRACT_ADDRESS where ORIGIN_TO_ADDRESS='0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'

and EVENT_NAME='Transfer' and TX_STATUS='SUCCESS' and EVENT_REMOVED=false and native_amount>0 and price_usd>0),

Table3 as ( select f.ORIGIN_FROM_ADDRESS as wallet ,min(f.block_timestamp) as date_first_action

from optimism.core.fact_event_logs f join Table2 b on b.ORIGIN_FROM_ADDRESS = f.ORIGIN_FROM_ADDRESS and f.block_timestamp > b.block_timestamp

where TX_STATUS='SUCCESS' and EVENT_REMOVED=false group by 1),

Table4 as ( select block_timestamp ,TX_HASH,ORIGIN_FROM_ADDRESS,ORIGIN_TO_ADDRESS,project_name,EVENT_NAME,EVENT_INPUTS

from optimism.core.fact_event_logs f join Table3 d on d.wallet = f.ORIGIN_FROM_ADDRESS and d.DATE_FIRST_ACTION = f.block_timestamp

left join optimism.core.dim_labels l on l.address = f.ORIGIN_TO_ADDRESS where TX_STATUS='SUCCESS' and EVENT_REMOVED=false and project_name is not null),

Final as (select top 10 project_name ,count(DISTINCT tx_hash) as Txn from Table4 group by 1 order by Txn desc)