mohammadh3.5) Cryptopunk Sales and Traders(top 10 usd profit -wallet analysis)
Updated 2022-08-30
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 A as (select
SELLER_ADDRESS as seller,
sum(PRICE_USD) as sell_usd_volume ,
sum(PRICE) as sell_eth_volume
from ethereum.core.ez_nft_sales
where (PROJECT_NAME = 'cryptopunks' and NFT_ADDRESS = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb' or NFT_ADDRESS='0xb7f7f6c52f2e2fdb1963eab30438024864c313f6' and PROJECT_NAME='wrapped cryptopunks')
and EVENT_TYPE = 'sale'
and PRICE_USD > 0
and BUYER_ADDRESS !='0x0000000000000000000000000000000000000000'
and (CURRENCY_ADDRESS ='ETH' or CURRENCY_ADDRESS ='WETH')
group by seller),
B as (select
BUYER_ADDRESS as buyer,
sum(PRICE_USD) as buy_usd_volume,
sum(PRICE) as buy_eth_volume
from ethereum.core.ez_nft_sales
where (PROJECT_NAME = 'cryptopunks' and NFT_ADDRESS = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb' or NFT_ADDRESS='0xb7f7f6c52f2e2fdb1963eab30438024864c313f6' and PROJECT_NAME='wrapped cryptopunks')
and EVENT_TYPE = 'sale'
and PRICE_USD > 0
and BUYER_ADDRESS !='0x0000000000000000000000000000000000000000'
and (CURRENCY_ADDRESS ='ETH' or CURRENCY_ADDRESS ='WETH')
group by buyer),
C as (select
buyer as wallet,
sum(sell_usd_volume - buy_usd_volume) as usd_profit
from A join B on A.seller=B.buyer
group by wallet)
select
count(Wallet) as wallets,
case
when usd_profit > 0 then 'Users that benefited'
else 'Users who suffered losses' end as user_status
from C
Run a query to Download Data