date_trunc('day', b.block_timestamp) as date,

case

when event_address in ( '0x54ad3d30af77b60d939ae356e6606de9a4da67583f02b962d2d3f2e481484e90', '0x43d8cad89263e6936921a0adb8d5d49f0e236c229460f01b14dca073114df2b9') then 'layerzero'

when event_address = '0x867ed1f6bf916171b1de3ee92849b8978b7d1b9e0a8cc982a3d19d535dfd9c0c' then 'Aptos name v1'

when event_address = '0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e' then 'BlueMove Exchange'

when event_address = '0x16fe2df00ea7dde4a63409201f7f4e536bde7bb7335526a35d05111e68aa322c' then ' AnimeSwap'

when event_address = '0xaa90e0d9d16b63ba4a289fb0dc8d1b454058b21c9b5c76864f825d5c1f32582e' then 'MSafe Wallet'

else ADDRESS_NAME end as name,

count(DISTINCT sender) as users,

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

FROM aptos.core.fact_events a

JOIN aptos.core.fact_transactions b ON a.tx_hash = b.tx_hash

left join aptos.core.dim_labels l on a.event_address= l.ADDRESS

where EVENT_ADDRESS not in ('0x1','0x3','0x4')

GROUP BY 1,2

having name is not null

order by 1 asc)

where rank <= 5

and date < current_date

and date >= current_date - 30