with margin_users as (

SELECT

DISTINCT signers[0] as user

FROM solana.core.fact_events

WHERE program_id = 'MFv2hWf31Z9kbCa1snEPYctwafyhdvnV7FZnsebVacA'

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

,

main as (

SELECT

label,

count(DISTINCT signers[0]) as count_user,

count(DISTINCT tx_id) as n_action

FROM solana.core.fact_events a

JOIN solana.core.dim_labels b ON a.program_id = b.address

WHERE signers[0] IN (SELECT user FROM margin_users)

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

AND label != 'solana'

GROUP BY label )

SELECT

label,

case when label = 'marginfi' then 1 else 0 end as sub_label,

count_user,

100 * count_user / (f.margin_user) as rate_of_user,

n_action / count_user as n_action_per_user

FROM main

CROSS JOIN (

SELECT

count(DISTINCT user) as margin_user

FROM margin_users

) as f

ORDER BY count_user DESC