WITH parsed_data AS (

SELECT

tx.BLOCK_TIMESTAMP,

tx.tx_id,

tx.INSTRUCTIONS,

tx.SIGNERS,

tx.SUCCEEDED

FROM solana.core.fact_transactions tx

WHERE tx.block_timestamp >= '2024-11-01 00:00:00'

AND tx.block_timestamp < '2024-12-01 00:00:00'

AND tx.SIGNERS[1] = '2vTuoHNQiief6RGTwmeUKNTkHdK5DpzaRHcs6XNfUxsV'

AND tx.SUCCEEDED = TRUE

);

BLOCK_TIMESTAMP as date,

tx.tx_id,

CASE WHEN INSTRUCTIONS[4]:programId='CoREENxT6tW1HoK8ypY1SxRMZTcVPm7R94rH4PZNhX7d' THEN INSTRUCTIONS[4]:accounts[0] ELSE INSTRUCTIONS[4]:parsed:info:mint END AS token_address,

CASE WHEN INSTRUCTIONS[4]:programId='CoREENxT6tW1HoK8ypY1SxRMZTcVPm7R94rH4PZNhX7d' THEN INSTRUCTIONS[4]:accounts[1] ELSE '' END AS Collection_ID,

'BUY' AS market_order,

CASE WHEN INSTRUCTIONS[4]:programId='CoREENxT6tW1HoK8ypY1SxRMZTcVPm7R94rH4PZNhX7d' THEN 1 WHEN INSTRUCTIONS[4]:program = 'spl-associated-token-account' THEN INSTRUCTIONS[5]:parsed:info:tokenAmount:uiAmount ELSE INSTRUCTIONS[4]:parsed:info:tokenAmount:uiAmount END AS token_amount,

CASE WHEN INSTRUCTIONS[4]:program = 'spl-associated-token-account' THEN INSTRUCTIONS[7]:parsed:info:source ELSE INSTRUCTIONS[6]:parsed:info:source END AS Buyer,

CASE WHEN INSTRUCTIONS[4]:program = 'spl-associated-token-account' THEN INSTRUCTIONS[7]:parsed:info:destination ELSE INSTRUCTIONS[6]:parsed:info:destination END AS Seller,

CASE WHEN INSTRUCTIONS[4]:program = 'spl-associated-token-account' THEN INSTRUCTIONS[7]:parsed:info:lamports/0.94/1e9 ELSE INSTRUCTIONS[6]:parsed:info:lamports/0.94/1e9 END AS sol_amount

FROM solana.core.fact_transactions tx

WHERE LEFT(INSTRUCTIONS[3]:parsed, 8) = 'VVII Buy'