SELECT l.block_timestamp

, l.block_number

, l.tx_hash

, txs.from_address AS tx_from

--, SUM(raw_amount/1e18) AS eth_amount

, CASE WHEN l.origin_function_signature = '0xf66a6799' THEN 'Cancel Order'

WHEN l.origin_function_signature = '0x00cb1eef' THEN 'Sell'

WHEN l.origin_function_signature = '0x0c03ecb3' THEN 'Batch Buy'

END AS event_type

, l.data AS data_raw

, CASE WHEN l.origin_function_signature = '0xf66a6799' THEN l.data

END AS cancelled_order

, CASE WHEN l.origin_function_signature = '0x00cb1eef' THEN '0x' || substr(l.data, 27, 40)

ELSE txs.from_address

END AS buyer

, CASE WHEN l.origin_function_signature = '0x0c03ecb3' THEN '0x' || substr(l.data, 27, 40)

ELSE txs.from_address

END AS seller

, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN utils.udf_hex_to_int(substr(l.data, 579, 64)) END AS token_id

, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN '0x' || substr(l.data, 283, 40) END AS token_address

, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN utils.udf_hex_to_int(substr(l.data, 323, 64))/POWER(10, 18) END AS price

--, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN 0.03*utils.udf_hex_to_int(substr(l.data, 324, 64))/POWER(10, 18) END AS fee

--, *

FROM blast.core.fact_event_logs l

INNER JOIN blast.core.fact_transactions txs ON txs.block_number=l.block_number

AND txs.tx_hash=l.tx_hash

WHERE contract_address IN ('0x56bfb3a51a7a2d4f685e5107cee05a58a0f1ad61', '0x5b8c52f4ebdb7c1eb01405a6e03916bba2a974c9')

AND l.block_timestamp >= date_trunc('hour', current_timestamp - interval '4 days')

)

SELECT date_trunc('hour', block_timestamp) AS time

, COUNT(DISTINCT tx_from) AS traders

, SUM(price) AS eth_volume

, 0.015*SUM(price) AS "Hero Royalties"

, 0.015*SUM(price) AS "Marketplace Fees"