date_trunc('day', block_timestamp) as day,

contract_address,

count(tx_hash) as n_of_transfers

from ethereum.core.fact_token_transfers

where block_timestamp::date between '2022-05-04' and '2022-05-15'

group by 1,2

qualify row_number() over (partition by day order by n_of_transfers desc) <= 5),

labels as (select

address,

address_name,

when address_name = 'terra: luna token' then 'LUNA'

when address_name = 'weth' then 'WETH'

when address_name = 'hop protocol: usdt l1canonicaltoken' then 'USDT'

when address_name = 'usdc' then 'USDC'

when address_name = 'Gas refund OTHR.claims' then 'Refund for failed Otherside transactions'

when address_name = 'wormhole: ust token' then 'UST: Wormhole'

when address_name = 'ApeWL.io (Otherside Beta)' then 'Otherside Beta Access: Scam'

when address_name = 'looksrare: looks token' then 'LooksRare: LOOKS'

when address_name = 'Stronger' then 'StrongBlock: STRNGR'

when address_name = 'project galaxy: gal token' then 'Galxe: GAL'

when address_name = 'Otherside Beta Access ApeWL.io' then 'Otherside Beta Access: Scam'

when address_name = 'hop protocol: dai l1canonicaltoken' then 'DAI'

end as label

from ethereum.core.dim_labels

group by 1,2)

select transactions.day, transactions.n_of_transfers, labels.label

from transactions

join labels

on transactions.contract_address=labels.address