select

signers[0] as address,

date_trunc('quarter', block_timestamp::date) as quarter,

count(distinct date(block_timestamp::date)) as active_days

from solana.core.fact_transactions

where date(block_timestamp) between dateadd(quarter, -4, TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD')) and TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD')

group by 1, 2

),

grouped as (

select

quarter,

active_days as days_active_in_quarter,

count(active_days) as number_of_addresses

from

base

group by 1, 2

),

pdf_cdf as (

select

quarter,

days_active_in_quarter,

sum(number_of_addresses::float) over (partition by quarter order by days_active_in_quarter asc rows unbounded preceding) / sum(number_of_addresses) over (partition by quarter) as cdf

from

grouped

days_active_in_quarter,

max(case when quarter = date_trunc('quarter', TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD')) then cdf end) as "Q-0",

max(case when quarter = dateadd(quarter, -1, date_trunc('quarter', TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD'))) then cdf end) as "Q-1",

max(case when quarter = dateadd(quarter, -2, date_trunc('quarter', TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD'))) then cdf end) as "Q-2",

max(case when quarter = dateadd(quarter, -3, date_trunc('quarter', TO_TIMESTAMP('{{End_date}}', 'YYYY-MM-DD'))) then cdf end) as "Q-3"

from pdf_cdf

group by days_active_in_quarter

order by days_active_in_quarter