Elprognerd9 - Top 10 nft collection with the most volume (Solanart) copy
Updated 2023-03-23
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
›
⌄
⌄
-- forked from 4d897c22-88cb-4b30-a5f2-1e0f030988f3
WITH price_table AS (SELECT
date_trunc('day', hour) AS DATE,
avg(price) AS price
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address ilike '0xD31a59c85aE9D8edEFeC411D448f90841571b89c'
GROUP BY 1)
SELECT
project_name,
/*CASE WHEN MARKETPLACE = 'solanart' THEN 'Solanart'
WHEN MARKETPLACE ilike '%magic eden%' THEN 'Magic Eden' END AS Marketplace,*/
COUNT(DISTINCT tx_id) AS n_sales,
COUNT(DISTINCT purchaser) AS n_buyers,
COUNT(DISTINCT seller) AS n_sellers,
COUNT(DISTINCT x.MINT) AS n_nfts,
sum(SALES_AMOUNT*price) AS tot_volume_usd
--avg(SALES_AMOUNT*price) AS avg_volume_usd,
--median(SALES_AMOUNT*price) AS med_volume_usd
FROM solana.core.fact_nft_sales x JOIN price_table ON date_trunc('day', BLOCK_TIMESTAMP) = price_table.DATE
JOIN solana.dim_nft_metadata y on x.mint = y.mint
WHERE (MARKETPLACE = 'solanart' /*OR MARKETPLACE ilike '%magic eden%'*/) AND SUCCEEDED = 'TRUE'
GROUP BY 1
ORDER BY 6 DESC
LIMIT 10
Run a query to Download Data