avg(price) as avg_price

from aptos.price.ez_prices_hourly

where symbol = 'APT'

and day >= '2024-01-01'

group by 1)

select TO_CHAR(TRUNC(block_timestamp, 'month'), 'YYYY-MM-DD') AS date,

month_name,

TO_VARCHAR(count(DISTINCT tx_hash), 'FM999,999,999,999') as "Transactions",

TO_VARCHAR(count(*), 'FM999,999,999,999') as "Sales",

TO_VARCHAR(count(DISTINCT buyer_address), 'FM999,999,999,999') as "Buyers",

TO_VARCHAR(count(DISTINCT seller_address), 'FM999,999,999,999') as "Sellers",

TO_VARCHAR(count(DISTINCT nft_address), 'FM999,999,999,999') as "Collections",

TO_VARCHAR(count(DISTINCT platform_address), 'FM999,999,999,999') as "Platforms",

TO_VARCHAR(sum(total_price), 'FM999,999,999,999') as "Volume (APT)",

TO_VARCHAR(SUM(CASE WHEN total_price_usd IS NULL THEN total_price * avg_price ELSE total_price_usd END), '$999,999,999,999') AS "Volume (USD)",

avg(total_price) as "Avg Price (APT)",

median(total_price) as "Median Price (APT)",

max(total_price) as "Max Price (APT)",

TO_VARCHAR(sum(platform_fee), 'FM999,999,999,999') as "Platform Fees (APT)"

from aptos.nft.ez_nft_sales a join ethereum.core.dim_dates c on a.block_timestamp::date = c.date_day

left outer join price b on a.block_timestamp::date = b.day

where project_name not in ('Cellana Voting Tokens')

and date >= '2024-01-01'

group by 1,2

order by 1 asc