nimasadjadiUntitled Query
    Updated 2022-07-30
    with DEFI as (select b.label, count(distinct a.signers[0]) as number_users
    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
    Run a query to Download Data