with mint_tokenid as ( select tokenid from ethereum.core.ez_nft_mints where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' ), transfers_date as ( select distinct block_timestamp::date as day from ethereum.core.ez_nft_transfers where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' ), mint_x_transfers_date as ( select* from mint_tokenid cross join transfers_date ), mint_tx as ( select tx_hash from ethereum.core.ez_nft_mints where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' ), minters as ( select tokenid, nft_to_address as minters_address from ethereum.core.ez_nft_mints where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' ), raw_transfers as ( select block_timestamp::date as day, tokenid, nft_to_address as transferred_address , 1 as owned from ethereum.core.ez_nft_transfers where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' union all select block_timestamp::date as day, tokenid, nft_from_address, -1 as owned from ethereum.core.ez_nft_transfers where nft_address = '0x18df6c571f6fe9283b87f910e41dc5c8b77b7da5' ), agg_transfers as ( select day, tokenid, sum(owned) as owning, transferred_address, lag(transferred_address) over (partition by tokenid order by day asc) as previous_address from raw_transfers group by day, tokenid, transferred_address ), agg_transfers_owned as ( select* from agg_transfers where owning > 0), current_owner as ( select d.day, d.tokenid, m.minters_address, t.transferred_address, t.previous_address, coalesce(t.transferred_address, t.previous_address) as daily_current_owner_address, lag(daily_current_owner_address) ignore nulls over (partition by d.tokenid order by d.day asc) as previous_daily_current_owner_address, coalesce(daily_current_owner_address, previous_daily_current_owner_address ,m.minters_address ) as latest_owner, 1 as base_count from mint_x_transfers_date d left join minters m on d.tokenid = m.tokenid left join agg_transfers_owned t on (t.day = d.day and t.tokenid = m.tokenid) --where d.tokenid in ('9267', '5222') ), current_owner_holding_raw as ( select tokenid, day, latest_owner, case when latest_owner = previous_daily_current_owner_address then sum(base_count) over (partition by latest_owner, tokenid order by day asc) else 1 end as holding_days from current_owner ), current_owner_holding_days_max as ( select tokenid, latest_owner, max(holding_days) as holding_days_max from current_owner_holding_raw group by tokenid, latest_owner ) select case when holding_days_max = 1 then 'a. 1 day' when holding_days_max >1 and holding_days_max <= 30 then 'b. <1 month' when holding_days_max >30 and holding_days_max <= 60 then 'c. <2 months' when holding_days_max >60 and holding_days_max <= 90 then 'd. <3 months' when holding_days_max >90 and holding_days_max <= 120 then 'e. <4 month' when holding_days_max >120 and holding_days_max <= 150 then 'f. <5 months' when holding_days_max >150 and holding_days_max <= 180 then 'g. <6 months' when holding_days_max >180 and holding_days_max <= 210 then 'h. <7 months' when holding_days_max >210 and holding_days_max <= 240 then 'i. <8 months' when holding_days_max >240 and holding_days_max <= 270 then 'j. <9 months' when holding_days_max >270 and holding_days_max <= 300 then 'k. <10 months' when holding_days_max >300 and holding_days_max <= 330 then 'l. <11 months' when holding_days_max >330 and holding_days_max <= 365 then 'm. <12 months' end as holding_months , count(tokenid) from current_owner_holding_days_max group by holding_months order by holding_months asc

Animetas 1 Year Anniversary Dashboard