with receivet as (

select nft_address,

tokenid,

project_name,

nft_to_address as receiver,

nft_from_address as sender,

block_timestamp as receive_date

from ethereum.core.ez_nft_transfers

where block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2023-01-01'

and nft_to_address != '0x0000000000000000000000000000000000000000'),

sendt as (

select t1.nft_address,

t1.tokenid,

t1.project_name,

nft_to_address as receiver,

nft_from_address as sender,

block_timestamp as send_date,

from ethereum.core.ez_nft_transfers t1 join receivet t2 on t1.nft_address = t2.nft_address and t1.tokenid = t2.tokenid and t1.nft_from_address = t2.receiver and t1.block_timestamp > t2.receive_date

where block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2023-01-01')

select coalesce (initcap(project_name),initcap(address_name),nft_address) as Project_Title,

case when project_title = '0x5a44ff097652acaa29d0ab52dd25343f213326c6' then 'Metabad Donuts'

when project_title = '0x5f9283a06e86ae04391fad38f76ac84e078b4270' then 'Portals'

when project_title = '0xba4a96b53d73232eb4b3f8fb02b62e8f4d65f887' then 'Therese Isabel Herzog (RESI)'

when project_title = '0x9cca06462f995edd0fedc72a99501367684f30f3' then 'Basic. (BASIC)'

when project_title = '0x279b1d26dfe55332a7b70324ce05b51f8a4fa078' then 'Funy Monki (FYMI)'

when project_title = '0x141ee909f4de4e198c3287e061e3ce95f28d3441' then 'MiamiHome (MIA)'

when project_title = '0xc151e527ac4c5beb7cbb23db5975058efeb646aa' then 'HexApeYachtClub (HAYC)'

else project_title end as project_title1,

avg (datediff(day,receive_date,send_date)) as Holding_Time

from sendt t1 left outer join ethereum.core.dim_labels t2 on t1.nft_address = t2.address

group by 1,2

order by 3 DESC