marqudecoding drift borrow
    Updated 2024-11-29
    with

    withdraw_txs as (

    select

    block_timestamp
    , tx_id
    , amount
    , mint

    , replace(f_logs.value, 'Program data: ', '') as log_data
    , to_varchar(base64_decode_binary(log_data), 'HEX') AS hex_data

    from solana.core.ez_events_decoded
    inner join solana.core.fact_transactions
    using(block_timestamp, tx_id)
    inner join lateral flatten (input => fact_transactions.log_messages) f_logs
    left join solana.core.fact_transfers
    using(block_timestamp, tx_id)
    where program_id = 'dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH'
    and event_type = 'withdraw'
    and f_logs.value like 'Program data: tPHaz2aHLI%'
    and substr(hex_data, length(hex_data) - 3, 2) = '02'
    and (ez_events_decoded.decoded_instruction :accounts[3] :pubkey = fact_transfers.tx_to -- authority
    or ez_events_decoded.decoded_instruction :accounts[6] :pubkey = fact_transfers.tx_to) -- userTokenAccount
    and block_timestamp > current_date() - 30
    ),

    aggregated as (

    select

    date_trunc('day', block_timestamp) as date
    , mint as token_address
    QueryRunArchived: QueryRun has been archived