SELECT l2.contract_address

--, SUM(tokenflow_eth.hextoint(substr(l1.data, 67, 64))*pow(10, -18)) AS volume

--, COUNT(*) AS sales

, COUNT(DISTINCT l1.origin_from_address) AS buyers

FROM optimism.core.fact_event_logs l1

JOIN optimism.core.fact_event_logs l2

ON l1.tx_hash = l2.tx_hash

WHERE l1.topics[0] = '0x70ba0d31158674eea8365d0f7b9ac70e552cc28b8bb848664e4feb939c6578f8'

AND l2.topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

AND l1.origin_to_address = '0x3f9da045b0f77d707ea4061110339c4ea8ecfa70' -- same tx, only check 1

AND l1.block_timestamp >= CURRENT_DATE -30 -- same tx, only check 1

GROUP BY 1

SELECT *

, CASE

WHEN contract_address = '0x81b30ff521d1feb67ede32db726d95714eb00637' THEN 'Optimistic Explorer'

WHEN contract_address = '0xb91b2276bd5a98994bf1f496e3886f688f8d4581' THEN 'Genesis Kami'

WHEN contract_address = '0x69a68eb548a37ee475d9f89646945588558796d1' THEN 'Oliens'

WHEN contract_address = '0xa95579592078783b409803ddc75bb402c217a924' THEN 'Optimism Collective'

WHEN contract_address = '0xc36442b4a4522e871399cd717abdd847ab11fe88' THEN 'Uniswap V3 Positions'

END AS collection

--, row_number() OVER (ORDER BY volume DESC) rank

--, row_number() OVER (ORDER BY sales DESC) rank

, row_number() OVER (ORDER BY buyers DESC) rank

FROM collection_data

ORDER BY rank

limit 5