avax_prices AS (

SELECT

hour,

price

FROM avalanche.price.ez_prices_hourly

WHERE token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'

AND hour >= DATEADD(month, -3, CURRENT_DATE())

),

hyperspace_sales AS (

SELECT

COALESCE(labels.project_name, logs.decoded_log:erc721Token) AS collection,

logs.decoded_log:erc20TokenAmount / POW(10, 18) * prices.price AS sales_amount_usd

FROM

avalanche.core.ez_decoded_event_logs logs

JOIN avax_prices prices ON DATE_TRUNC('hour', logs.block_timestamp) = prices.hour

LEFT JOIN avalanche.core.dim_labels labels ON logs.decoded_log:erc721Token = labels.address

WHERE

logs.tx_status = 'SUCCESS'

AND logs.contract_address = '0x398baa6ffc99126671ab6be565856105a6118a40' -- Hyperspace

AND logs.event_name = 'ERC721OrderFilled'

AND logs.block_timestamp >= DATEADD(month, -3, CURRENT_DATE())

),

nftrade_sales AS (

SELECT

COALESCE(d.name, d.address) AS collection,

decoded_log:takerAssetAmount/POW(10, 18) * p.price AS sales_amount_usd

FROM

avalanche.core.ez_decoded_event_logs a

JOIN avax_prices p ON DATE_TRUNC('hour', a.block_timestamp) = p.hour

LEFT JOIN avalanche.core.dim_contracts d ON CONCAT('0x', RIGHT(LEFT(REGEXP_REPLACE(a.decoded_log:makerAssetData, '000+', ''), 50), 40)) = d.address