WITH tab1 AS (

SELECT

tx_id AS tx,

SIGNERS[0] AS signer

FROM solana.core.fact_events

WHERE program_id LIKE 'TRoLL7U1qTaqv2FFQ4jneZx5SetannKmrYCR778AkQZ'

AND block_timestamp > '2023-10-01'

GROUP BY 1, 2

),

tab2 AS (

SELECT

tx_id,

signer,

block_timestamp,

SUM(

CASE

WHEN tx_to LIKE 'HATUHhpGy5moXuwTZKr1qZREmKANcLy3kRiMQZvUseLE' THEN AMOUNT

ELSE -1 * AMOUNT

END

) AS net_spend

FROM solana.core.fact_transfers

LEFT OUTER JOIN tab1 ON tab1.tx = solana.core.fact_transfers.tx_id

WHERE mint LIKE 'So11111111111111111111111111111111111111112'

AND (

tx_to LIKE 'HATUHhpGy5moXuwTZKr1qZREmKANcLy3kRiMQZvUseLE'

OR tx_from LIKE 'HATUHhpGy5moXuwTZKr1qZREmKANcLy3kRiMQZvUseLE'

)

AND (

tx_to LIKE tab1.signer

OR tx_from LIKE tab1.signer

)

AND block_timestamp > '2023-10-01'

AND tx_id IN (SELECT tx FROM tab1)

GROUP BY 1, 2, 3