Madiholding
Updated 2023-04-30Copy 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 df as (
select
buyer_address,
nft_address,
tokenid,
min (block_timestamp::date) as purchase_date
from optimism.core.ez_nft_sales t1
where nft_address = '0x0deaac29d8a3d4ebbaaa3ecd3cc97c9def00f720' and EVENT_TYPE = 'sale'
group by buyer_address,nft_address,tokenid),
df1 as (
select
seller_address,
t1.nft_address,
t1.tokenid,
min (block_timestamp::date) as sale_date
from optimism.core.ez_nft_sales t1
where nft_address = '0x0deaac29d8a3d4ebbaaa3ecd3cc97c9def00f720'
and EVENT_TYPE = 'sale'
and seller_address in (select buyer_address from df)
and tokenid in (select tokenid from df)
and nft_address in (select nft_address from df)
group by 1,2,3),
df3 as (
select
datediff (day,purchase_date, sale_date) as Holding_Time,
buyer_address,
seller_address,
t1.nft_address,
t1.tokenid
from df1 t1 join df t2 on t1.seller_address = t2.buyer_address and t1.nft_address = t2.nft_address and t1.tokenid = t2.tokenid
where Holding_Time >= 0)
select avg (Holding_Time) as AVG_Holding_Time
from df3
Run a query to Download Data