block_timestamp::date as day,

label as platform,

count(distinct (tx_id)) as txn_count,

count(distinct (instruction:accounts[1])) as users_count,

(txn_count / users_count) as avg_txn_per_user,

sum (users_count) over (partition by label order by day asc) as comulative_users_count,

sum (txn_count) over (partition by label order by day asc) as comulative_txn_count,

row_number() over (partition by day order by txn_count desc) as rank

from solana.core.dim_labels

join solana.core.fact_events on address = program_id

where (

label_type = 'dex' or label_type = 'defi'

)

and block_timestamp::date > current_date - interval '1 month'

and succeeded = 1

group by day, label

qualify rank <= 10

order by day asc