SELECT

hour::date AS day,

AVG(price) AS avg_price

FROM

aptos.price.ez_prices_hourly

WHERE

symbol = 'APT'

AND hour >= '2024-01-01'

GROUP BY

1

),

nfts AS (

SELECT

TRUNC(block_timestamp,'hour') AS hourly,

*

FROM

aptos.nft.ez_nft_sales

WHERE

project_name NOT IN ('Cellana Voting Tokens')

),

purchases_after_2024 AS (

SELECT

buyer_address,

MIN(hourly) AS first_purchase_hourly

FROM

nfts

GROUP BY

buyer_address

HAVING

MIN(hourly) >= '2024-01-01'

),

new_or_returning AS (

SELECT

f.hourly::date AS purchase_date,

f.buyer_address,