SELECT

tx_hash,

block_timestamp,

CASE when DECODED_LOG:offer[0]['token'] ILIKE '0xf2bb3Dd3eA243AD644F1A4026a1d2026D98552E3' then 'Collectible' else 'Drop' end as token_type,

CASE when DECODED_LOG:offer[0]['token'] ILIKE '0xf2bb3Dd3eA243AD644F1A4026a1d2026D98552E3' then (decoded_log['consideration'][0]['amount'] + decoded_log['consideration'][1]['amount'] + decoded_log['consideration'][2]['amount'] + decoded_log['consideration'][3]['amount']) / power(10, 18)

else (decoded_log['consideration'][0]['amount'] + decoded_log['consideration'][1]['amount'] + decoded_log['consideration'][2]['amount'])/ power(10, 18) end as price_usd,

ORIGIN_FROM_ADDRESS

FROM blast.core.ez_decoded_event_logs

WHERE event_name LIKE 'OrderFulfilled'

AND contract_address ILIKE '0x00000000000000adc04c56bf30ac9d3c0aaf14dc'

AND (DECODED_LOG:offer[0]['token'] ILIKE '0xf2bb3Dd3eA243AD644F1A4026a1d2026D98552E3'

or DECODED_LOG:offer[0]['token'] ILIKE '0xC440CB352F687cc4627919F7f4e7C4fBf0f6E068'

AND not EVENT_REMOVED

date(block_timestamp) as date,

token_type,

count(DISTINCT tx_hash) as sales,

sum(price_usd) as volume,

count(DISTINCT origin_from_address) as buyers

FROM tab1

GROUP BY 1,2