WITH t0 AS (

SELECT DISTINCT tx_id

, ixn.value:programId::TEXT AS pid

FROM solana.core.fact_transactions t

, lateral flatten (input => instructions) ixn

WHERE t.block_timestamp >= CURRENT_DATE - 7

AND succeeded

AND ixn.value:programId != 'SW1TCH7qEPTdLsDHRgPuMQjbQxKdH2aBStViMFnt64f'

AND ixn.value:programId != 'ComputeBudget111111111111111111111111111111'

AND ixn.value:programId != 'ATokenGPvbdGVxr1b2hvZbsiqW5xWH25efTNsLJA8knL'

AND ixn.value:programId != 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'

AND ixn.value:programId != '11111111111111111111111111111111'

AND ixn.value:programId != 'AddressLookupTab1e1111111111111111111111111'

, t1 AS (

SELECT DISTINCT tx_id

, key.value:pubkey::TEXT AS pubkey

FROM solana.core.fact_transactions t

, lateral flatten (input => account_keys) key

WHERE t.block_timestamp >= CURRENT_DATE - 7

, t2 AS (

SELECT

DISTINCT(decoded_instruction:accounts[0]:pubkey::TEXT) AS agg

FROM

solana.core.fact_decoded_instructions

WHERE

block_timestamp::date >= CURRENT_DATE - 7

AND program_id = 'SW1TCH7qEPTdLsDHRgPuMQjbQxKdH2aBStViMFnt64f'

AND decoded_instruction:name = 'aggregatorSaveResult'

, t3 AS (

SELECT pid

, COUNT(DISTINCT t0.tx_id) AS tx

FROM t0