SELECT

DISTINCT tx_hash

FROM optimism.core.fact_event_logs

WHERE contract_address LIKE lower('0x5c9D55b78FEBCC2061715BA4f57EcF8EA2711F2c')

), tab2 as (

SELECT

tx_hash as txn,

max(AMOUNT) as amt1,

max(AMOUNT_USD) as amt_usd1

FROM optimism.core.ez_eth_transfers

where tx_hash IN (SELECT * FROM tab1)

GROUP BY 1

), tab3 as (

SELECT

origin_from_address,

sum(amount_usd) as volume,

COUNT(DISTINCT tx_hash) as sales

FROM tab2

LEFT outer JOIN optimism.core.ez_eth_transfers

ON txn = tx_hash

AND amt1 = amount

GROUP BY 1

SELECT

CASE WHEN volume < 10 THEN 'Below $10 Volume'

WHEN volume < 100 THEN 'Between $10 and $100 Volume'

WHEN volume < 1000 THEN 'Between $100 and $1,000 Volume'

ELSE 'Over $1,000 Volume' END,

count(*)

FROM tab3

GROUP BY 1