concat('0x', substr(data, 27, 40)) as buyer,

concat('0x', substr(topics[1], 27, 40)) as seller,

concat('0x', substr(topics[2], 27, 40)) as collection,

ethereum.public.udf_hex_to_int(topics[3]) as nft_id,

ethereum.public.udf_hex_to_int(concat('0x', substr(data, 67, 64)))/pow(10,18) as price

from optimism.core.fact_event_logs

where origin_function_signature in ('0xad6c8c5f', '0x912d97fc') and topics[0] = '0x70ba0d31158674eea8365d0f7b9ac70e552cc28b8bb848664e4feb939c6578f8' and block_timestamp::date != '2022-07-02' -- exclude freezed date

, eth_sales as (

select date, 'ETH Market' as type, q.tx_hash, buyer, seller, collection, nft_id, price

from quixotic_sales q join optimism.core.ez_eth_transfers e on q.tx_hash = e.tx_hash

, op_sales as (

select date, 'OP Market' as type, q.tx_hash, buyer, seller, collection, nft_id, price

from quixotic_sales q join optimism.core.fact_token_transfers t on q.tx_hash = t.tx_hash

, final as (

select * from eth_sales

UNION

select * from op_sales

)

select date, type,

count(distinct tx_hash) as sales_count,

sum(price) as sales_volume,

count(distinct buyer) as buyers,

count(distinct seller) as sellers

from final group by date, type

-- limit 100