MLDZMNAlls11
Updated 2022-10-10
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 tb1 as (select
distinct purchaser as u1,
case
when TOKEN_METADATA:Clothes in ('Gym Tee', 'World Champion Robe', 'FTX Jersey','God Scout Uniform') then 'Sport-related cloth'
when TOKEN_METADATA:Clothes in ('Mythic War Armor',' War Armor', 'Hazmat Suit',' God Beater', 'Top God Jumper') then 'Army-type cloth'
else 'Other cloth'
end as gp,
min(BLOCK_TIMESTAMP) as x1
from solana.core.fact_nft_sales s left outer join solana.core.dim_nft_metadata b on s.mint=b.MINT
where SUCCEEDED='TRUE' and PROJECT_NAME ilike '%DeGods%'
group by 1,2 having gp is not null ),
tb2 as (SELECT
distinct SELLER as u2,
case
when TOKEN_METADATA:Clothes in ('Gym Tee', 'World Champion Robe', 'FTX Jersey','God Scout Uniform') then 'Sport-related cloth'
when TOKEN_METADATA:Clothes in ('Mythic War Armor',' War Armor', 'Hazmat Suit',' God Beater', 'Top God Jumper') then 'Army-type cloth'
else 'Other cloth'
end as gp,
min(BLOCK_TIMESTAMP) as x2
from solana.core.fact_nft_sales s left outer join solana.core.dim_nft_metadata b on s.mint=b.MINT
where SUCCEEDED='TRUE' and PROJECT_NAME ilike '%DeGods%'
and SELLER in (select u1 from tb1)
group by 1,2 having gp is not null ) ,
tb3 as (select
tb1.u1 as purchasers,
tb1.gp,
avg(DATEDIFF(day,x1, x2 )) as time_between
from tb1
join tb2 on tb1.u1=tb2.u2
group by 1,2)
select
case when time_between<1 then 'whitin 24 Hours'
when time_between>=1 and time_between<7 then 'Under 1 week'
Run a query to Download Data