WITH meprice AS (

SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price

FROM

solana.price.ez_prices_hourly

WHERE

token_address = 'MEFNBXixkEbait3xn9bkm8WsJzXtVsaJEn4c8Sam21u'

GROUP BY

day

order by day DESC

limit 1;

),

staking AS (

SELECT

TRUNC(block_timestamp, 'day') AS day, -- Truncate to match `meprice`

block_timestamp,

tx_id,

signers[0] AS owner,

utils.udf_hex_to_int(

TO_CHAR(REVERSE(TO_BINARY(SUBSTR(utils.udf_base58_to_hex(instruction:data), 19, 16))))

) / POW(10, 6) AS amount,

'stake' AS claim_program

FROM

solana.core.fact_events

WHERE

succeeded

AND fact_events.program_id = 'veTbq5fF2HWYpgmkwjGKTYLVpY6miWYYmakML7R7LRf'

AND SUBSTR(utils.udf_base58_to_hex(fact_events.instruction:data), 3, 16) = 'ceb0ca12c8d1b36c' -- staking

AND block_timestamp >= '2024-12-10 14:05:13.000'

And amount > 1