SELECT

BLOCK_TIMESTAMP,

TX_ID,

MINT,

OWNER,

PRE_BALANCE,

BALANCE,

CASE

WHEN COUNT(TX_ID) OVER (PARTITION BY TX_ID) > 1 AND BALANCE > PRE_BALANCE THEN 'TRANSFER_RECEIVING'

WHEN COUNT(TX_ID) OVER (PARTITION BY TX_ID) > 1 AND BALANCE < PRE_BALANCE THEN 'TRANSFER_SENDING'

WHEN PRE_BALANCE > BALANCE THEN 'BURN'

WHEN BALANCE > PRE_BALANCE THEN 'MINT'

ELSE 'UNKNOWN'

END AS TRANSACTION_TYPE,

ABS(BALANCE - PRE_BALANCE) AS TOKEN_CHANGE

FROM

solana.core.fact_token_balances

WHERE

MINT IN ('9mkbt5gx6XsdCBVPLbioQ1NvgNjmNBktccGWctrExwJC', '28nC2fTmasMuLnbx9uuAJbavyxmA9EVnGfiYHVXdjtzC')

AND BLOCK_TIMESTAMP >= '2024-11-01'

AND SUCCEEDED = TRUE

SELECT

BLOCK_TIMESTAMP,

TX_ID,

MINT,

OWNER,

PRE_BALANCE,

BALANCE,

TRANSACTION_TYPE,

TOKEN_CHANGE,

-- Calculate minutes since the transaction occurred from the start date

DATEDIFF(MINUTE, '2024-11-01', BLOCK_TIMESTAMP) AS MINUTES_SINCE_TRANSACTION,

-- Multiply token change with minutes since transaction

ABS(TOKEN_CHANGE * DATEDIFF(MINUTE, '2024-11-01', BLOCK_TIMESTAMP)) AS TOKEN_MINUTES