--credit : 0xHaM☰d
with avatokens as (
select
distinct token_in,
symbol_in
from avalanche.sushi.ez_swaps
),
maintable as (
select
block_timestamp::date as date,
contract_address,
symbol_in,
count (distinct tx_hash) as Swaps_Count,
count (distinct origin_from_address) as Swappers_Count
from avalanche.core.fact_event_logs t1 join avatokens t2 on t1.contract_address = t2.token_in
where origin_to_address = lower('0x60aE616a2155Ee3d9A68541Ba4544862310933d4')
and event_name = 'Transfer'
and tx_status = 'SUCCESS'
and block_timestamp >= CURRENT_DATE - 30
and block_timestamp <= CURRENT_DATE - 1
and event_inputs:from = origin_from_address
--and contract_address in (select token_in from avatokens)
group by 1,2,3
),
finaltable as (
select *,
row_number() over (partition by date order by Swaps_Count desc) as RN
from maintable
)
select * from finaltable
where RN <= 10
order by date