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.nft_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.nft_collection = 'A.4eded0de73020ca5.CricketMoments'

AND s.block_timestamp >= '2024-01-01'

SELECT

CASE