WITH sales_summary AS (

SELECT

DATE_TRUNC('day', s.block_timestamp) AS hour,

COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN tx_id END) AS sales_tx,

COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN buyer END) AS buyers,

COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN seller END) AS sellers,

COUNT(DISTINCT CASE WHEN tx_succeeded = 'true' THEN nft_id END) AS tokens_sold,

SUM(CASE WHEN tx_succeeded = 'true' THEN price * p.prices ELSE 0 END) AS amount,

AVG(CASE WHEN tx_succeeded = 'true' THEN price * p.prices ELSE 0 END) AS avg_price,

MIN(CASE WHEN tx_succeeded = 'true' THEN price * p.prices ELSE 0 END) AS floor_price,

MAX(CASE WHEN tx_succeeded = 'true' THEN price * p.prices ELSE 0 END) AS max_price

FROM flow.nft.ez_nft_sales s

JOIN (

SELECT recorded_hour::date AS date1, AVG(close) AS prices

FROM flow.price.fact_hourly_prices

WHERE token = 'Flow'

GROUP BY 1

) p ON s.block_timestamp::date = p.date1::date

WHERE nft_collection LIKE '%GarageCard%'

GROUP BY 1

SELECT * FROM sales_summary

ORDER BY hour;