raw AS (

SELECT

block_timestamp,

tx_id,

marketplace,

buyer,

nft_id,

price * IFNULL(price_usd, 1) AS amount

FROM

flow.core.ez_nft_sales sales

LEFT JOIN (

SELECT

timestamp::date AS day,

token_contract,

AVG(price_usd) AS price_usd

FROM

flow.core.fact_prices

GROUP BY

day,

token_contract

) prices

ON

sales.block_timestamp::date = prices.day AND sales.currency = prices.token_contract

WHERE

tx_succeeded = TRUE

AND nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

AND price > 0

),

before_thanksgiving AS (

SELECT

*

FROM

raw

WHERE