angealOptimist: NFT Trades
Updated 2023-11-07Copy Reference Fork
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
›
⌄
-- get the SDK to pull > 100k records:
-- https://github.com/FlipsideCrypto/sdk
-- also check out the repo for more queries:
-- https://github.com/FlipsideCrypto/user_metrics/tree/main/sql
WITH sells AS (
SELECT
seller_address AS user_address,
platform_name AS marketplace,
nft_address AS nf_token_contract,
count(distinct(tokenid)) AS n_nfts_sold,
currency_symbol AS sell_symbol,
currency_address AS currency,
count(tx_hash) AS n_sells,
sum(price) AS token_sell_volume,
sum(price_usd) AS sell_usd_volume
FROM
optimism.core.ez_nft_sales ns
WHERE
block_timestamp > current_date - 180
AND
user_address NOT IN (SELECT address FROM FLIPSIDE_PROD_DB.CROSSCHAIN.ADDRESS_LABELS where blockchain = 'optimism')
GROUP BY
seller_address, marketplace, nf_token_contract, sell_symbol, currency
),
buys AS (
SELECT
buyer_address AS user_address,
platform_name AS marketplace,
nft_address AS nf_token_contract,
count(distinct(tokenid)) AS n_nfts_bought,
currency_symbol AS buy_symbol,
currency_address AS currency,
count(tx_hash) AS n_buys,
Run a query to Download Data