-- forked from nftuser5 @ https://flipsidecrypto.xyz/edit/queries/c6cff9c4-9a15-4c77-a47b-01003d516d57

with t1 as (select

from avalanche.core.dim_labels

),

tb2 as (select

distinct from_address,

PROJECT_NAME,

count (Distinct s.block_timestamp::Date) as active_days

from avalanche.core.fact_transactions s

left join t1 on s.TO_ADDRESS=t1.ADDRESS

where BLOCK_TIMESTAMP>=current_date-{{Period}}

and PROJECT_NAME is not null

and STATUS = 'SUCCESS'

and LABEL_TYPE in ('cex')

and label_subtype not in ('token_contract')

group by 1,2

select

PROJECT_NAME,

avg(active_days) as avg_active_days,

avg_active_days/{{Period}}*100 as share_active_days,

case when share_active_days>100 then 100 else share_active_days end as "Share of active days"

from tb2

group by 1

order by 3 desc