jp12[Avalanche] OpenSea - NFT Volume per marketplace
Updated 2022-09-01
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
29
30
31
32
33
34
35
36
›
⌄
-- WITH prices as (
-- SELECT HOUR::date as date, avg(PRICE) as avg_price
-- FROM ethereum.core.fact_hourly_token_prices
-- WHERE TOKEN_ADDRESS = lower('0x85f138bfEE4ef8e540890CFb48F620571d67Eda3') and date > '2022-01-01'
-- GROUP BY 1
-- )
WITH raw as (
SELECT block_timestamp::date as date, tx_hash, ORIGIN_FROM_ADDRESS as FROM_ADDRESS,
CASE WHEN ORIGIN_TO_ADDRESS = '0xc28f1550160478a7fb3b085f25d4b179e08e649a' THEN 'NFTrade'
WHEN ORIGIN_TO_ADDRESS = '0x11ac3118309a7215c6d87c7c396e2df333ae3a9c' THEN 'Kalao'
WHEN ORIGIN_TO_ADDRESS IN ('0xcf91b99548b1c17dd1095c0680e20de380635e20','0x8927985b358692815e18f2138964679dca5d3b79') THEN 'Chikn'
WHEN ORIGIN_TO_ADDRESS = '0xae079eda901f7727d0715aff8f82ba8295719977' THEN 'JoePegs'
WHEN ORIGIN_TO_ADDRESS = '0xbcb09cdb2011fea0591b52e52085bb102e4a082a' THEN 'Campfire'
WHEN ORIGIN_TO_ADDRESS = '0x18cd9270dbdca86d470cfb3be1b156241fffa9de' THEN 'Element'
WHEN ORIGIN_TO_ADDRESS = '0x1c578933fbc981c7e6eb2832fc48a579d9e8033d' THEN 'TofuNFT'
WHEN ORIGIN_TO_ADDRESS = '0x1a7d6ed890b6c284271ad27e7abe8fb5211d0739' THEN 'NFTKEY'
WHEN ORIGIN_TO_ADDRESS = '0x14390f57ccfdb45f969381e7e107acf062d3a592' THEN 'YetiSwapNFT'
END AS market,
SUM(RAW_AMOUNT / POW(10,18)) AS volume, SUM((RAW_AMOUNT / POW(10,18)) * 25) as volume_usd
FROM avalanche.core.fact_token_transfers --t LEFT JOIN prices p ON BLOCK_TIMESTAMP::date = p.date
WHERE 1=1
AND ORIGIN_TO_ADDRESS IN ('0xc28f1550160478a7fb3b085f25d4b179e08e649a', -- NFTrade
'0x11ac3118309a7215c6d87c7c396e2df333ae3a9c', -- Kalao
'0xcf91b99548b1c17dd1095c0680e20de380635e20', -- Roostr
'0x8927985b358692815e18f2138964679dca5d3b79', -- Chikn
'0xae079eda901f7727d0715aff8f82ba8295719977', -- joepegs
'0xbcb09cdb2011fea0591b52e52085bb102e4a082a', -- Campfire.exchange
'0x18cd9270dbdca86d470cfb3be1b156241fffa9de',
'0x1c578933fbc981c7e6eb2832fc48a579d9e8033d',
'0x1a7d6ed890b6c284271ad27e7abe8fb5211d0739',
'0x14390f57ccfdb45f969381e7e107acf062d3a592'
)
GROUP BY 1, 2, 3, 4
UNION
Run a query to Download Data