with tab1 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.buyer_address ) AS "buyers" , count( distinct a.seller_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.price_usd ) AS "sale volume" -- , a.project_name AS "collection" -- , a.nft_address AS "nft address" , 'Opensea' AS "market" FROM avalanche.nft.ez_nft_sales a WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.platform_address in ( '0x00000000006c3852cbef3e08e8df289169ede581' , '0x00000000000001ad428e4906ae43d8f9852d0dd6' , '0x00000000000000adc04c56bf30ac9d3c0aaf14dc' ) GROUP BY 1 ORDER BY 1 DESC ) , tab2 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.buyer_address ) AS "buyers" , count( distinct a.seller_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.price_usd ) AS "sale volume" -- , a.project_name AS "collection" -- , a.nft_address AS "nft address" , 'Joepegs' AS "market" FROM avalanche.nft.ez_nft_sales a WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.platform_address in ( '0xae079eda901f7727d0715aff8f82ba8295719977' ) GROUP BY 1 ORDER BY 1 DESC ) , tab3 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.buyer_address ) AS "buyers" , count( distinct a.seller_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.price_usd ) AS "sale volume" -- , a.project_name AS "collection" -- , a.nft_address AS "nft address" , 'Salvor' AS "market" FROM avalanche.nft.ez_nft_sales a WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.platform_address in ( '0xd106ec6e81e9b7f5bd33a6091a3c3e45b6183dc3' ) GROUP BY 1 ORDER BY 1 DESC ) , ---------------------- price ------------------------ wavax AS ( SELECT date_trunc( 'day' , a.hour ) AS date , avg( a.price ) AS price FROM avalanche.price.ez_hourly_token_prices a WHERE a.symbol = 'WAVAX' GROUP BY 1 ORDER BY 1 DESC ) , price AS ( SELECT date_trunc( 'day' , a.hour ) AS date , avg( a.price ) AS price , a.token_address AS tk_add , a.symbol AS tk_smb , a.decimals AS tk_dec FROM avalanche.price.ez_hourly_token_prices a GROUP BY 1 , 3 , 4 , 5 ORDER BY 1 DESC ) ---------------------- price ------------------------ , tab4 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'NFTrade' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.block_timestamp::date = b.block_timestamp::date AND a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0xbf6bfe5d6b86308cf3b7f147dd03ef11f80bfde3' AND a.avax_to_address = '0xbf6bfe5d6b86308cf3b7f147dd03ef11f80bfde3' AND a.origin_function_signature = '0xe98b3b7e' GROUP BY 1 ORDER BY 1 DESC ) , tab5 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'tofuNFT' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.block_timestamp::date = b.block_timestamp::date AND a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x86232f68b5bf2a3a03851d98556352512a3b12b9' AND a.avax_to_address = '0x86232f68b5bf2a3a03851d98556352512a3b12b9' AND a.origin_function_signature = '0xba847759' GROUP BY 1 ORDER BY 1 DESC ) , skill AS ( SELECT to_timestamp_ntz( a.value[0]::numeric / 1000 )::date AS date , a.value[1] AS price FROM ( SELECT livequery.live.udf_api ( 'https://api.coingecko.com/api/v3/coins/cryptoblades/market_chart?vs_currency=usd&{{period}}s=max&interval=daily') AS response ) live , table( flatten( parse_json( response:data:"prices" ) ) ) a ORDER BY 1 DESC ) , tab6 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( ( a.raw_amount_precise / power( 10 , 18 ) ) * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Bazaar Marketplace' AS "market" FROM avalanche.core.fact_token_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.block_timestamp::date = b.block_timestamp::date AND a.tx_hash = b.tx_hash JOIN skill c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x9469ed8d4b86e4442b4aa848bb94b9f9130f123e' AND a.origin_function_signature = '0xa6f95726' GROUP BY 1 ORDER BY 1 DESC ) , tab7 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Element Market' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.block_timestamp::date = b.block_timestamp::date AND a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x398baa6ffc99126671ab6be565856105a6118a40' AND a.avax_to_address = '0x398baa6ffc99126671ab6be565856105a6118a40' AND a.origin_function_signature = '0xfbee349d' GROUP BY 1 ORDER BY 1 DESC ) , tab8 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Kalao' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0xae079eda901f7727d0715aff8f82ba8295719977' AND a.avax_to_address = '0xae079eda901f7727d0715aff8f82ba8295719977' GROUP BY 1 ORDER BY 1 DESC ) , tab9 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Hyperspace' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x398baa6ffc99126671ab6be565856105a6118a40' AND a.avax_to_address = '0x398baa6ffc99126671ab6be565856105a6118a40' GROUP BY 1 ORDER BY 1 DESC ) , tab10 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Campfire' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0xbcb09cdb2011fea0591b52e52085bb102e4a082a' AND a.avax_to_address = '0xbcb09cdb2011fea0591b52e52085bb102e4a082a' GROUP BY 1 ORDER BY 1 DESC ) , tab11 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'Capsule' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x82b7a9ea06e757ec7c8cddecbad6071e6894e4cd' AND a.avax_to_address = '0x82b7a9ea06e757ec7c8cddecbad6071e6894e4cd' GROUP BY 1 ORDER BY 1 DESC ) , tab12 AS ( SELECT date_trunc( '{{period}}' , a.block_timestamp ) AS "date" , count( distinct a.origin_from_address ) AS "buyers" , count( distinct b.nft_from_address ) AS "sellers" , count( distinct a.tx_hash ) AS "sale count" , sum( a.amount * c.price ) AS "sale volume" -- , b.project_name AS "collection" -- , b.nft_address AS "nft address" , 'NFTKEY' AS "market" FROM avalanche.core.ez_avax_transfers a JOIN avalanche.nft.ez_nft_transfers b ON a.tx_hash = b.tx_hash JOIN wavax c ON a.block_timestamp::date = c.date WHERE "date" >= '2023-10-01' AND "date" <= '2024-01-06' AND a.origin_to_address = '0x1a7d6ed890b6c284271ad27e7abe8fb5211d0739' AND a.avax_to_address = '0x1a7d6ed890b6c284271ad27e7abe8fb5211d0739' GROUP BY 1 ORDER BY 1 DESC ) , final AS ( SELECT * FROM tab1 UNION SELECT * FROM tab2 UNION SELECT * FROM tab3 UNION SELECT * FROM tab4 UNION SELECT * FROM tab5 UNION SELECT * FROM tab6 UNION SELECT * FROM tab7 UNION SELECT * FROM tab8 UNION SELECT * FROM tab9 UNION SELECT * FROM tab10 UNION SELECT * FROM tab11 UNION SELECT * FROM tab12 ) SELECT * , sum( "buyers" ) over ( partition BY "market" ORDER BY "date" ASC ) AS "total buyers" , sum( "sellers" ) over ( partition BY "market" ORDER BY "date" ASC ) AS "total sellers" , sum( "sale count" ) over ( partition BY "market" ORDER BY "date" ASC ) AS "total sale count" , sum( "sale volume" ) over ( partition BY "market" ORDER BY "date" ASC ) AS "total sale volume" FROM final ORDER BY 1 DESC

Query	Creator	Created
001 - overview avalanche nft evm	d0bby	Jan 6, 2024
2024-01-18 04:16 PM avalanche evm	d0bby	Jan 19, 2024
AVAX Memecoin Swappers Daily copy kyberswap woofi hashflow trader joe swaps	d0bby	Mar 12, 2024
F ava Avalanche copy avalanche evm	d0bby	Oct 12, 2023
2024-06-11 09:10 AM kyberswap woofi hashflow trader joe swaps	d0bby	Jun 11, 2024
2023-10-19 02:51 PM avalanche evm	d0bby	Oct 19, 2023

d0bby