SalehThe Rise of DeGods- Is it correlated with the wallets that have purchased the most DeGods?
Updated 2022-03-27Copy 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
›
⌄
with lst_swapper as (
select SWAPPER
,sum(iff(swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ',swap_to_amount,0)) as amount_to_dust
,sum(iff( swap_from_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ',swap_from_amount ,0)) as amount_from_dust
,count(tx_id) as swap_count
from solana.fact_swaps
where block_timestamp::date >= '2022-01-01'
and (swap_to_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ'
and swap_program = 'jupiter aggregator v2'
or swap_from_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ')
and succeeded = TRUE
group by 1
having AMOUNT_TO_DUST>0 and AMOUNT_FROM_DUST>0
order by 1
)
,lst_purchased_all as (
select fs.PURCHASER
, sum(fs.sales_amount) as SALES_AMOUNT
from solana.fact_nft_sales fs
join solana.dim_nft_metadata dm on dm.mint = fs.mint
where fs.mint in (select contract_address from solana.dim_nft_metadata where contract_name = 'DeGods')
and succeeded = TRUE
and marketplace like '%magic eden%'
group by 1
)
,lst_top_PURCHASER as (
select PURCHASER , SALES_AMOUNT from lst_purchased_all
order by SALES_AMOUNT DESC
limit 100
)
,lst_all as (
select 'PURCHASER' as type,PURCHASER as wallet,SALES_AMOUNT as amount from lst_top_PURCHASER
union all
select 'Swapper' as type,SWAPPER,amount_to_dust from lst_swapper
where SWAPPER in(select PURCHASER from lst_top_PURCHASER)
Run a query to Download Data