WITH weekly_stats AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS week,

COUNT(TX_HASH) AS Sales_count,

COUNT(DISTINCT BUYER_ADDRESS) AS Buyers_count,

SUM(PRICE_USD) AS total_volume_usd

FROM base.nft.ez_nft_sales where block_timestamp >= '{{Start_date}}' and Block_timestamp <= '{{Target_date}}'

and PRICE_USD is not null

GROUP BY DATE_TRUNC('week', BLOCK_TIMESTAMP)

),

weekly_changes AS (

SELECT

week,

Sales_count,

Buyers_count,

total_volume_usd,

LAG(Sales_count) OVER (ORDER BY week) AS prev_Sales_count,

LAG(Buyers_count) OVER (ORDER BY week) AS prev_Buyers_count,

LAG(total_volume_usd) OVER (ORDER BY week) AS prev_total_volume_usd

FROM weekly_stats

week,

Sales_count,

Buyers_count,

total_volume_usd,

ROUND((Sales_count - prev_Sales_count) * 100.0 / prev_Sales_count, 2) AS Sales_count_change_pct,

ROUND((Buyers_count - prev_Buyers_count) * 100.0 / prev_Buyers_count, 2) AS Buyers_count_change_pct,

ROUND((total_volume_usd - prev_total_volume_usd) * 100.0 / prev_total_volume_usd, 2) AS total_volume_usd_change_pct

FROM weekly_changes

ORDER BY week DESC;