avg(price) as avg_price

from aptos.price.ez_prices_hourly

where symbol = 'APT'

and day >= '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 first_purchase_hourly >= '2024-01-01'

),

new_or_returning AS (

SELECT

f.hourly::date AS purchase_date,

f.buyer_address,

CASE

WHEN p.first_purchase_hourly = f.hourly THEN 'New'

ELSE 'Returning'

END AS user_type

FROM nfts f

JOIN purchases_after_2024 p ON f.buyer_address = p.buyer_address

SELECT

purchase_date,

user_type,

COUNT(DISTINCT buyer_address) AS user_count

FROM new_or_returning