tokenid as token_no,

ROUND(AVG(holding_time_in_hrs),2) AS avg_holding_hr

(

SELECT

tokenid,

block_timestamp AS latest_purchase_date,

LAG(block_timestamp, 1) OVER (

PARTITION BY tokenid

ORDER BY

block_timestamp ASC

) AS previous_purchase_date,

DATEDIFF('hours', previous_purchase_date, block_timestamp) AS holding_time_in_hrs

FROM

ethereum.nft.ez_nft_sales

WHERE

tokenid IN (

SELECT

tokenid

FROM

(

SELECT

sales.tokenid,

COUNT(DISTINCT(sales.block_timestamp)) AS resold_count

FROM

ethereum.nft.ez_nft_sales sales,

ethereum.nft.dim_nft_metadata metadata

WHERE

metadata.token_name LIKE '%fidenza%'

AND metadata.project_name LIKE '%art%'

AND sales.event_type = 'sale'

AND metadata.token_id = sales.tokenid

GROUP BY