SELECT

hour,

avg(price) AS avg_price

FROM

flow.price.ez_prices_hourly

WHERE

symbol = 'FLOW'

GROUP BY

hour

),

filtered_sales AS (

SELECT

s.tx_id,

s.buyer,

s.seller,

s.block_timestamp,

s.nft_collection,

CASE

WHEN s.currency ILIKE '%flow%' THEN s.price * fp.avg_price

ELSE s.price

END AS adjusted_price

FROM

flow.nft.ez_nft_sales s

JOIN

flow_price fp

ON

date_trunc('hour', s.block_timestamp) = fp.hour

WHERE

s.block_timestamp::date between '2024-01-01' and '2024-12-31'

AND s.nft_collection = 'A.0b2a3299cc857e29.TopShot'

date_trunc('day', block_timestamp) as date,

COUNT(DISTINCT tx_id) AS transaction_count,