select

date(date_trunc('day' , block_timestamp)) dt

, tx_id

, instructions[0]:programId program_id

, signers

, account_keys

from solana.core.fact_transactions

where date(block_timestamp) >= '{{start_dt}}' and date(block_timestamp) <= '{{end_dt}}'

and succeeded = 'TRUE'

),

s as (

select

dt

, tx_id

, program_id

, s.value signers

from t

, Table(Flatten(t.signers)) s

),

k as (

select

tx_id

, pubkeys

, case when l.label_type in ('chadmin','layer2') and a.pubkeys != '11111111111111111111111111111111' then 1 else 0 end ignore

from

(

select

tx_id

, ak.value:pubkey pubkeys

from t

, Table(Flatten(t.account_keys)) ak

where ak.value:writable = 'false'

and ak.value:signer = 'false'

and ak.value:pubkey != program_id

) a