-- forked from Marketplace Comparision @ https://flipsidecrypto.xyz/edit/queries/471678d5-0422-4db2-a8a8-3e456367a09a with flowverse_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 IN ('flowverse-nft-marketplace') ), flowverse_tab2 AS ( SELECT DISTINCT tx_id AS hash2 FROM flow.core.fact_events JOIN flowverse_tab1 ON tx_id = hash WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:purchased = 'true' ) , gaia_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 IN ('gaiamarketplace') ), gaia_tab2 AS ( SELECT DISTINCT tx_id AS hash2, event_data:salePrice::float as sales_price FROM flow.core.fact_events JOIN gaia_tab1 ON tx_id = hash WHERE EVENT_TYPE = 'ListingCompleted' AND EVENT_DATA:purchased = 'true' ) , final as ( SELECT DISTINCT (CASE when EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2') AND EVENT_TYPE = 'ListingCompleted' then event_data:buyer::string when EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2') AND EVENT_TYPE = 'OfferCompleted' then event_data:offerAddress::string end) AS "Unique Buyers", 'Flowty' as "Marketplace", COUNT(DISTINCT tx_id) AS "Total Transactions", SUM(CASE when EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2') AND EVENT_TYPE = 'ListingCompleted' then event_data:salePrice::float when EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2') AND EVENT_TYPE = 'OfferCompleted' then event_data:offerAmount::float end) AS "Total Sale Volume", count(DISTINCT BLOCK_TIMESTAMP::date) as n_day FROM FLOW.CORE.FACT_EVENTS WHERE ( EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2') AND EVENT_TYPE = 'ListingCompleted' AND TX_SUCCEEDED = TRUE AND EVENT_DATA:purchased::string = 'true' ) OR ( EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2') AND EVENT_TYPE = 'OfferCompleted' AND BLOCK_TIMESTAMP >= '2023-01-01' AND tx_succeeded = 'true' AND EVENT_DATA::string LIKE '%0x6590f8918060ef13%' AND EVENT_DATA:purchased LIKE '%true%' ) AND block_timestamp::date >= '2023-01-01' AND WEEK(BLOCK_TIMESTAMP::date) < WEEK(CURRENT_DATE()) GROUP BY 1,2 UNION SELECT EVENT_DATA:to AS "Unique Buyers", 'Floverse' as "Marketplace", COUNT(DISTINCT hash2) AS "Total Transactions", sum(case when currency='A.1654653399040a61.FlowToken' then price*close end) as "Total Sale Volume", count(DISTINCT a.BLOCK_TIMESTAMP::date) AS n_day FROM flow.core.fact_events AS a JOIN flowverse_tab2 ON a.tx_id = hash2 JOIN flow.core.dim_contract_labels AS b ON b.EVENT_CONTRACT = a.EVENT_CONTRACT JOIN flow.nft.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' AND a.block_timestamp::date >= '2023-01-01' AND WEEK(a.BLOCK_TIMESTAMP::date) < WEEK(CURRENT_DATE()) GROUP BY 1,2 UNION SELECT EVENT_DATA:to AS "Unique Buyers", 'Gaia' as "Marketplace", COUNT(DISTINCT hash2) AS "Total Transactions", SUM(sales_price) AS "Total Sale Volume", count(DISTINCT a.BLOCK_TIMESTAMP::date) AS n_day -- sum(case when currency='A.1654653399040a61.FlowToken' then price*close else price end) as "Total Sale Volume" FROM flow.core.fact_events AS a JOIN gaia_tab2 ON a.tx_id = hash2 -- JOIN flow.core.dim_contract_labels AS b ON b.EVENT_CONTRACT = a.EVENT_CONTRACT JOIN flow.nft.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' AND a.block_timestamp::date >= '2023-01-01' AND WEEK(a.BLOCK_TIMESTAMP::date) < WEEK(CURRENT_DATE()) -- AND marketplace = 'Gaia' GROUP BY 1, 2 ) SELECT "Marketplace", CASE when "Total Transactions" < 10 then 'a. <10 Txs' when "Total Transactions" >= 10 and "Total Transactions" < 50 then 'b. 10 ~ 50 Txs' when "Total Transactions" >= 50 and "Total Transactions" < 100 then 'c. 50 ~ 100 Txs' when "Total Transactions" >= 100 and "Total Transactions" < 500 then 'd. 100 ~ 500 Txs' when "Total Transactions" >= 500 and "Total Transactions" < 1000 then 'e. 500 ~ 1000 Txs' when "Total Transactions" >= 1000 then 'f. + 1000 Txs' end as "Txs Distribution", CASE when "Total Sale Volume" BETWEEN 0 and 10 then 'a. < $10' when "Total Sale Volume" BETWEEN 10 and 50 then 'b. 10 ~ $50' when "Total Sale Volume" BEtWEEN 50 and 100 then 'c. 50 ~ $100' when "Total Sale Volume" BEtWEEN 100 and 500 then 'd. 100 ~ $500' when "Total Sale Volume" BEtWEEN 500 and 1000 then 'e. 500 ~ $1K' else 'f. + $1K' end as "Trade Vol Distribution", CASE when n_day = 1 then 'a. only 1 d' when n_day > 1 and n_day <= 5 then 'b. 2 ~ 5 d' when n_day > 5 and n_day <= 10 then 'c. 6 ~ 10 d' when n_day > 10 and n_day <= 25 then 'd. 11 ~ 25 d' when n_day > 25 and n_day <= 50 then 'e. 25 ~ 50 d' when n_day > 50 then 'f. + 50 d' end as "Active Days Distribution", count(DISTINCT "Unique Buyers") as "Buyers Count" FROM final GROUP by 1,2,3,4 -- HAVING "Trade Vol Distribution" is NOT NULL ORDER by 5 DESC

Flowty Marketplace and JollyJokers Analyze