date_trunc('day', block_timestamp) as days,

case

when ADDRESS_NAME = 'hop protocol: usdc l2canonicaltoken' then 'USDC'

when ADDRESS_NAME = 'hop protocol: eth l2canonicaltoken' then 'ETH'

when ADDRESS_NAME = 'hop protocol: usdt l2canonicaltoken' then 'USDT'

when ADDRESS_NAME = 'hop protocol: dai l2canonicaltoken' then 'DAI'

when ADDRESS_NAME = 'hop protocol: wbtc l2canonicaltoken' then 'BTC'

else ADDRESS_NAME end as token_out,

count(DISTINCT tx_hash) as swaps,

count(DISTINCT ORIGIN_FROM_ADDRESS) as swappers

FROM arbitrum.core.fact_event_logs

LEFT outer JOIN flipside_prod_db.crosschain.address_labels

ON contract_address = address

WHERE tx_hash IN (

SELECT DISTINCT tx_hash

FROM arbitrum.core.fact_event_logs

WHERE event_name LIKE 'Swap'

AND address_name IN (

SELECT address_name FROM(

SELECT

ADDRESS_NAME,

count(DISTINCT tx_hash) as swaps

FROM arbitrum.core.fact_event_logs

LEFT outer JOIN flipside_prod_db.crosschain.address_labels

ON contract_address = address

WHERE tx_hash IN (

SELECT DISTINCT tx_hash

FROM arbitrum.core.fact_event_logs

WHERE event_name LIKE 'Swap'

-- AND origin_function_signature LIKE '0x13dcfc59'

)

AND event_name LIKE 'Transfer'

AND not address_name is NULL