WITH tab1 AS (

SELECT DISTINCT tx_id AS hash, BLOCK_TIMESTAMP::date AS date

FROM flow.core.fact_events

WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:customID = 'flowverse-nft-marketplace'

),

tab2 AS (

SELECT DISTINCT tx_id AS hash2

FROM flow.core.fact_events

JOIN tab1 ON tx_id = hash

WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:purchased = 'true'

SELECT

event_data:to as user,

COUNT(DISTINCT EVENT_DATA:id) AS NFTs,

COUNT(DISTINCT hash2) AS trades,

sum(case when currency='A.1654653399040a61.FlowToken' then price*close else price end) as volume_usd

FROM flow.core.fact_events AS a

JOIN tab2 ON a.tx_id = hash2

JOIN flow.core.dim_contract_labels AS b ON b.EVENT_CONTRACT = a.EVENT_CONTRACT

JOIN flow.core.ez_nft_sales s on s.tx_id=a.tx_id

JOIN flow.core.fact_hourly_prices p on trunc(s.block_timestamp,'hour')=recorded_hour --and s.currency=token

WHERE EVENT_TYPE = 'Deposit' and p.token ='Flow'

GROUP BY 1

order by 4 desc