select *

from solana.core.fact_transactions

where

instructions[0]:programId = 'HubbLeXBb7qyLHt3x7gvYaRrxQmmgExb7fCJgDqFuB6T' and

succeeded = TRUE and

(inner_instructions[0]:instructions[0]:parsed:info:mint = 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' or

inner_instructions[1]:instructions[0]:parsed:info:mint = 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX')

),

info as (

select

date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as tx_count,

count(distinct signers[0]) as unique_user,

sum(inner_instructions[1]:instructions[0]:parsed:info:amount/1e6) as tx_volume,

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

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

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

(select sum(inner_instructions[1]:instructions[0]:parsed:info:amount/1e6) from base) as total_tx_volume

from base

group by 1

),

avg_info as (

select

avg(tx_count) as avg_tx_count,

avg(tx_volume) as avg_tx_volume

from info

select *

from info, avg_info