base as (

select

t.tx_id

, date(t.block_timestamp) as block_date

, s.value as pub_key

, pk.value['pubkey'] as program_id

, case

when pk.value['pubkey'] = 'ZETAxsqBRek56DhiGXrn75yj2NHU3aYUnxvHXpkf3aD' then 'zeta'

when pk.value['pubkey'] = 'mv3ekLzLbnVPNxjSKvqBpU3ZeZXPQdEC3bp5MDEBG68' then 'mango_v3'

when pk.value['pubkey'] = '4MangoMjqJ2firMokCjjGgoK8d4MXcrgL7XJaL3w6fVg' then 'mango_v4'

when pk.value['pubkey'] = 'dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH' then 'drift_v2'

end as program_name

from solana.core.fact_transactions t,

lateral flatten(input => t.signers) s,

lateral flatten (input => t.account_keys) pk

where

date(t.block_timestamp) between date('2022-12-08') and date('2023-02-07')

and pk.value['pubkey'] in (

'ZETAxsqBRek56DhiGXrn75yj2NHU3aYUnxvHXpkf3aD' -- zeta

'mv3ekLzLbnVPNxjSKvqBpU3ZeZXPQdEC3bp5MDEBG68', -- mango v3

'4MangoMjqJ2firMokCjjGgoK8d4MXcrgL7XJaL3w6fVg', -- mango v4

'dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH' -- drift v2

)

b.pub_key

, count(case when b.program_name = 'zeat' and b.block_date between date('2023-02-01') and date('2023-02-07') then b.tx_id end) as zeta_txns_last7d

from base b

group by 1