datavortexAverage Held Period before resale
Updated 2025-02-04
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 TopCollections AS (
SELECT
project_name
FROM
aptos.nft.fact_nft_sales
WHERE
block_timestamp >= current_timestamp - INTERVAL '1 month'
AND project_name IS NOT NULL
AND project_name <> ''
GROUP BY
project_name
ORDER BY
COUNT(DISTINCT fact_nft_sales_id) DESC
LIMIT
10
),
NftSales AS (
SELECT
project_name,
block_timestamp,
ROW_NUMBER() OVER (PARTITION BY project_name ORDER BY block_timestamp) AS rn
FROM
aptos.nft.fact_nft_sales
WHERE
project_name IN (SELECT project_name FROM TopCollections)
)
SELECT
ns1.project_name,
AVG(DATEDIFF(day, ns1.block_timestamp, ns2.block_timestamp) * 24) AS AverageHoldingTimeHours
FROM
NftSales ns1
JOIN
NftSales ns2 ON ns1.project_name = ns2.project_name
AND ns1.rn = ns2.rn - 1
QueryRunArchived: QueryRun has been archived