WITH activities AS (

SELECT

SIGNERS[0] AS user,

date_trunc('day', block_timestamp) AS activity_date

FROM solana.core.fact_events

WHERE program_id = 'MFv2hWf31Z9kbCa1snEPYctwafyhdvnV7FZnsebVacA'

AND block_timestamp::date >= '2023-02-07'

GROUP BY 1, 2

),

active_wallets AS (

SELECT

user,

datediff(day, current_date - 90, current_date) AS days,

count(activity_date) AS activity_days,

CASE

WHEN activity_days = 1 THEN '1 day active'

WHEN activity_days <= days * 10 / 100 THEN '10% >= active'

WHEN activity_days <= days * 30 / 100 THEN '30% >= active'

WHEN activity_days <= days * 50 / 100 THEN '50% >= active'

WHEN activity_days <= days * 70 / 100 THEN '70% >= active'

WHEN activity_days <= days * 90 / 100 THEN '90% >= active'

ELSE 'Highly Active'

END AS activity_type,

CASE

WHEN activity_days = 1 THEN '1 day'

WHEN activity_days <= days * 10 / 100 THEN 'active <= ' || floor(days * 10 / 100) || ' days'

WHEN activity_days <= days * 30 / 100 THEN 'active <= ' || floor(days * 30 / 100) || ' days'

WHEN activity_days <= days * 50 / 100 THEN 'active <= ' || floor(days * 50 / 100) || ' days'

WHEN activity_days <= days * 70 / 100 THEN 'active <= ' || floor(days * 70 / 100) || ' days'

WHEN activity_days <= days * 90 / 100 THEN 'active <= ' || floor(days * 90 / 100) || ' days'

ELSE 'active >' || floor(days * 90 / 100) || ' days'

END AS days_active,

CASE