SELECT

buyer,

count(*) as events,

sum(price * token_price) as volume_usd,

median(price * token_price) as median_price

FROM flow.nft.ez_nft_sales

LEFT OUTER JOIN (

SELECT

date(RECORDED_HOUR) as day,

median(open) as token_price

FROM flow.price.fact_hourly_prices

WHERE token LIKE 'Flow'

GROUP BY 1

) ON day = date(block_timestamp)

WHERE NFT_collection LIKE '%BarbieCard%'

GROUP BY 1

CASE

WHEN events = 1 THEN '1'

WHEN events < 5 THEN '2 - 5'

WHEN events < 10 THEN '6 - 10'

WHEN events < 50 THEN '10 - 50'

WHEN events < 100 THEN '50 - 100'

WHEN events < 500 THEN '100 - 500'

ELSE '500 +'

END AS purchase_volume_group,

COUNT(*) AS users

FROM subquery

GROUP BY 1;