date(block_timestamp) as date,

nonce,

to_address,

from_address

from bsc.core.fact_transactions

where date(block_timestamp) between date('2022-10-01') and date('2023-01-01')

and nonce < 5

),

counts as (

select

date,

to_address,

count(from_address) as txns,

count(distinct from_address) as users

from base group by date, to_address ),

ranks as (

select

date,

to_address,

txns,

users,

rank() over (partition by date order by users desc ) as rank

from counts where txns > 100000)

select * from ranks --where rank=1

order by date desc, users desc;