SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS week_start_date,

SUM(TOTAL_PRICE) AS weekly_sales_usd

FROM

aptos.nft.ez_nft_sales

WHERE

BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP()) -- transactions in the last 30 days

AND TOTAL_PRICE IS NOT NULL -- ensure TOTAL_PRICE is not null

GROUP BY

DATE_TRUNC('week', BLOCK_TIMESTAMP)

),

weeks AS (

SELECT DISTINCT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS week_start_date

FROM

aptos.nft.ez_nft_sales

WHERE

BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP()) -- generate all weeks in the date range

weeks.week_start_date AS week_date,

COALESCE(weekly_nft_sales.weekly_sales_usd, 0) AS weekly_sales_usd

weeks

LEFT JOIN

weekly_nft_sales ON weeks.week_start_date = weekly_nft_sales.week_start_date

ORDER BY

week_date DESC;