from solana.fact_transactions a

inner join solana.dim_labels b

on a.instructions[0]:programId = b.address

where b.label_subtype != 'token_contract'

and b.label != 'solana'

and a.succeeded = 'TRUE'

and a.block_timestamp::date >= current_date - 90

group by 1

order by number_users desc

limit 10) -- Top 10 DEFi in Solana

select a.block_timestamp::date as dt, b.label, count(distinct a.signers[0]) as number_users,

sum(number_users) over (partition by b.label order by dt) as cum_tx,

avg(number_users) OVER (partition by b.label order by dt rows between 7 preceding and current row) as users_7

from solana.fact_transactions a

inner join solana.dim_labels b

on a.instructions[0]:programId = b.address

where b.label_subtype != 'token_contract'

and b.label != 'solana'

and a.succeeded = 'TRUE'

and a.block_timestamp::date >= current_date - 90

and b.label in (select label from DEFI)

group by 1, 2

order by number_users desc