WITH sales_summary AS (

SELECT

date(date_trunc(day, s.block_timestamp)) AS timespan,

tx_id,

buyer,

seller,

price * p.prices AS sales_amount,

tx_succeeded,

COUNT(*) OVER (PARTITION BY date(date_trunc(day, s.block_timestamp))) AS sales_tx_count,

COUNT(DISTINCT buyer) OVER (PARTITION BY date(date_trunc(day, s.block_timestamp))) AS buyer_count,

COUNT(DISTINCT seller) OVER (PARTITION BY date(date_trunc(day, s.block_timestamp))) AS seller_count,

SUM(price * p.prices) OVER (PARTITION BY date(date_trunc(day, s.block_timestamp))) AS total_amount

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%'

AND tx_succeeded = 'true'

timespan,

MAX(sales_tx_count) AS sales_tx,

MAX(buyer_count) AS buyers,

MAX(seller_count) AS sellers,

SUM(sales_amount) AS amount,

MAX(total_amount) AS max_sales_amount

FROM sales_summary

GROUP BY timespan

ORDER BY timespan;