raw_transactions as (

select

case when fe.inner_instruction:instructions[0]:program = 'system' then 'from Solana' else 'from Other Currencies' end as transfer_type,

fe.inner_instruction as instructions_json,

fe.block_timestamp,

fe.tx_id,

fe.inner_instruction:instructions[0]:parsed:info:source as user_id,

case when transfer_type = 'from Solana' then (fe.inner_instruction:instructions[0]:parsed:info:lamports) else fe.inner_instruction:instructions[0]:parsed:info:amount end as token_transfered

from solana.core.fact_events fe

where

fe.succeeded = 'true'

and fe.program_id = 'CQ36xjMHgmgwEM1yvJYUWg3YxMvzwM4Mntn6vZrMk86z' --deposit txs

and date(fe.block_timestamp) between date('2022-11-01') and date('2023-10-31')

union all

select

case when fe.program_id = '11111111111111111111111111111111' then 'to Solana' else 'to Other Currencies' end as transfer_type,

fe.instruction as instructions_json,

fe.block_timestamp,

fe.tx_id,

fe.instruction:parsed:info:destination as user_id,

case when transfer_type = 'to Solana' then (fe.instruction:parsed:info:lamports) else fe.instruction:instructions[0]:parsed:info:amount end as token_transfered

from solana.core.fact_events fe

where

fe.signers[0] = '7RNYdRkMWSKL8BM2pES8g8WZEp3Gm9azYh8yJoiFwtrb' --withdraw txs

and fe.succeeded = 'true'

and date(fe.block_timestamp) between date('2022-11-01') and date('2023-10-31')

),

tab1 as (

select

case when transfer_type in ('from Other Currencies', 'to Other Currencies') then b.pre_token_balances[0]:mint else 'So11111111111111111111111111111111111111112' end as token_address,

a.transfer_type,

a.block_timestamp,