-- Display the buyer's identifier

buy_stats.buyer AS "Buyer",

-- Calculate the total amount spent by the buyer (total buy volume)

COALESCE(buy_stats.total_buy_volume, 0) AS "Total Buy Volume",

-- Calculate the total amount received by the buyer from sales (total sell volume)

COALESCE(sell_stats.total_sell_volume, 0) AS "Total Sales Volume",

-- Calculate the net spend as total buy volume minus total sales volume

COALESCE(buy_stats.total_buy_volume, 0) - COALESCE(sell_stats.total_sell_volume, 0) AS "Net Spend"

(

-- Subquery to calculate total buy volume for each buyer

SELECT

buyer,

SUM(price) AS total_buy_volume

FROM

flow.nft.ez_nft_sales

WHERE

NFT_COLLECTION IN (

'A.e4cf4bdc1751c65d.AllDay', -- NFL All Day collection

'A.e4cf4bdc1751c65d.PackNFT' -- NFL Pack NFTs collection

)

AND DATE_TRUNC(

'day',

CONVERT_TIMEZONE('America/Los_Angeles', block_timestamp)

) BETWEEN '2024-01-01 00:00:00' AND '2024-06-30 23:59:59'

AND marketplace = 'A.4eb8a10cb9f87357.NFTStorefront' -- Filter for the specific marketplace

GROUP BY

buyer