SELECT tx_id AS transaction_id

, signers[0] AS user_id

, fact_transactions.block_timestamp::DATETIME AS blocktime_ts

, JSON_EXTRACT_PATH_TEXT(account.value, 'pubkey') AS pubkey

, JSON_EXTRACT_PATH_TEXT(pre_tb.value, 'mint') AS mint

, JSON_EXTRACT_PATH_TEXT(pre_tb.value, 'uiTokenAmount.uiAmountString')::FLOAT AS amount

FROM solana.core.fact_transactions,

LATERAL FLATTEN(input => fact_transactions.account_keys) AS account,

LATERAL FLATTEN(input => fact_transactions.pre_token_balances) AS pre_tb

WHERE TRUE

AND fact_transactions.block_timestamp::DATE BETWEEN '{{start_date}} 00:00:00.000' AND '{{end_date}} 23:59:59.000'

AND date(block_timestamp) = date'2023-05-22'

AND succeeded

AND tx_id = 'LEzhMyqhTRSckojNdvLhHUDUqktEB5Fb47uhQ2HYBjEPSARQ8XQZJArPZffwEaTjkJ8TitkBqT9g95hRXGgw2cs'

),

post AS (

SELECT tx_id AS transaction_id

, signers[0] AS user_id

, fact_transactions.block_timestamp::DATETIME AS blocktime_ts

, JSON_EXTRACT_PATH_TEXT(account.value, 'pubkey') AS pubkey

, JSON_EXTRACT_PATH_TEXT(post_tb.value, 'mint') AS mint

, JSON_EXTRACT_PATH_TEXT(post_tb.value, 'uiTokenAmount.uiAmountString')::FLOAT AS amount

FROM solana.core.fact_transactions,

LATERAL FLATTEN(input => fact_transactions.account_keys) AS account,

LATERAL FLATTEN(input => fact_transactions.post_token_balances) AS post_tb

WHERE TRUE

AND fact_transactions.block_timestamp::DATE BETWEEN '{{start_date}} 00:00:00.000' AND '{{end_date}} 23:59:59.000'

AND date(block_timestamp) = date'2023-05-22'

AND succeeded

AND tx_id = 'LEzhMyqhTRSckojNdvLhHUDUqktEB5Fb47uhQ2HYBjEPSARQ8XQZJArPZffwEaTjkJ8TitkBqT9g95hRXGgw2cs'

),