base as (

SELECT

t.block_timestamp,

t.tx_id,

e.inner_instruction['instructions'] [0] ['parsed'] ['info'] ['source'] as sender,

e.inner_instruction['instructions'] [0] ['parsed'] ['info'] ['destination'] as receiver,

e.inner_instruction['instructions'] [0] ['parsed'] ['info'] ['lamports'] / pow(10, 9) as amount,

t.pre_balances[0] / pow(10, 9) as sender_pre,

t.post_balances[0] / pow(10, 9) as sender_post,

t.pre_balances[1] / pow(10, 9) as receiver_pre,

t.post_balances[1] / pow(10, 9) as receiver_post,

t.fee,

t.units_consumed,

t.units_limit,

t.tx_size,

t.log_messages,

s.first_tx_date as sender_first_tx_date,

s.last_tx_date as sender_last_tx_date,

s.first_program_id as sender_first_program_id,

s.last_program_id as sender_last_program_id,

s.num_days_active as sender_num_days_active,

s.num_txs as sender_num_txs,

s.total_fees as sender_total_fees,

s.programs_used as sender_programs_used,

array_size(array_distinct (sender_programs_used)) as sender_n_programs_used,

r.first_tx_date as receiver_first_tx_date,

r.last_tx_date as receiver_last_tx_date,

r.first_program_id as receiver_first_program_id,

r.last_program_id as receiver_last_program_id,

r.num_days_active as receiver_num_days_active,

r.num_txs as receiver_num_txs,

r.total_fees as receiver_total_fees,

r.programs_used as receiver_programs_used,