SELECT nft_address, project_name

FROM optimism.nft.ez_nft_transfers

GROUP BY 1,2

),

datas AS (

SELECT

block_timestamp,

e.tx_hash AS tx_hash,

project_name,

decoded_log:buyer AS buyer,

decoded_log:edition AS collection_address,

decoded_log:affiliate AS affiliate_address,

(decoded_log:affiliateFee) / pow(10,18) AS affiliate_fee,

(decoded_log:requiredEtherValue) / pow(10,18) AS required_eth_amount,

(decoded_log:platformFee) / pow(10,18) AS platform_fee,

(decoded_log:quantity) AS QTY,

(required_eth_amount - platform_fee - affiliate_fee) AS collection_price

FROM optimism.core.fact_decoded_event_logs e

INNER JOIN labels on nft_address = collection_address

WHERE contract_address = '0x403471cbcab399896004bc0af2b4674d4ab3b53b'

AND event_name = 'Minted'

buyer,

SUM(QTY) AS "Number of Mints",

COUNT(DISTINCT collection_address) AS "Number of Projects",

COUNT(DISTINCT affiliate_address) AS "Affiliate Addresses",

SUM(collection_price) AS "Collection Earned (ETH)",

SUM(required_eth_amount) AS "ETH Paid By Users",

SUM(affiliate_fee) AS "Affiliate Fee (ETH)",

SUM(platform_fee) AS "Platform Fee (ETH)"

FROM datas

GROUP BY 1