select *

from solana.core.fact_transactions

where

instructions[0]:programId = '6LtLpnUFNByNXLyCoK9wA2MykKAmQNZKBdY8s47dehDc' and

succeeded = TRUE

),

info as (

select

date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as tx_count,

count(distinct signers[0]) as unique_user,

avg(unique_user) over (order by date, date rows between 6 preceding and current row) as ma7_unique_user,

avg(tx_count) over (order by date, date rows between 6 preceding and current row) as ma7_tx_count,

(select count(distinct signers[0]) from base) as total_unique_user,

(select count(distinct tx_id) from base) as total_tx_count

from base

group by 1

),

avg_info as (

select avg(unique_user) as avg_unique_user, avg(tx_count) as avg_tx_count

from info

select *

from info, avg_info