with flow_price as (

SELECT date(TIMESTAMP) as pdate, avg(PRICE_USD) as p

from flow.core.fact_prices

where SYMBOL = 'FLOW'

GROUP BY pdate

SELECT player,

case when date(block_timestamp) > '2022-11-24' THEN 'After ThanksGiving' when date(block_timestamp) = '2022-11-24' THEN 'ThanksGiving Day' else 'Before ThanksGiving' end as splitter,

COUNT(DISTINCT TX_ID) as tx_count,

sum(case when CURRENCY = 'A.1654653399040a61.FlowToken' then PRICE * fp.p else PRICE end) as usd_volume,

avg(case when CURRENCY = 'A.1654653399040a61.FlowToken' then PRICE * fp.p else PRICE end) as avg_usd_volume,

COUNT(DISTINCT SELLER) as sellers,

COUNT(DISTINCT BUYER) as buyers,

row_number() over (partition by splitter order by usd_volume DESC) as r

FROM flow.core.ez_nft_sales sa join flow_price fp on fp.pdate = date(BLOCK_TIMESTAMP)

join flow.core.dim_allday_metadata m on sa.nft_id = m.nft_id and player != 'N/A'

where TX_SUCCEEDED = 'TRUE'

and date(block_timestamp) BETWEEN '2022-11-18' and '2022-11-30'

and sa.nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

GROUP by player, splitter

qualify r <= 5