-- forked from xFlowverse - sales tot @ https://flipsidecrypto.xyz/edit/queries/427af9bc-51a4-4042-b5da-6d4db8bf74cb

with base 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'

),

sales as

(select DISTINCT tx_id as hash2

from flow.core.fact_events join base on tx_id=hash

where EVENT_TYPE= 'ListingCompleted'

and EVENT_DATA:purchased = 'true')

select BLOCK_TIMESTAMP::date as date, contract_name as collection , count(DISTINCT EVENT_DATA:to) as "Number of purchasers",

count( EVENT_DATA:id ) as NFTs , count(DISTINCT hash2) as trades

from flow.core.fact_events a join sales on tx_id=hash2

join flow.core.dim_contract_labels b on b.EVENT_CONTRACT = a.EVENT_CONTRACT

where EVENT_TYPE = 'Deposit'

GROUP by 1,2