In Part 1, we identified Jupiter Lend’s liquidation instructions, mapped their account arguments, and decoded the raw amounts of debt repaid and collateral seized.

In this walkthrough, we’ll combine those findings into a single DuneSQL query using common table expressions (CTEs). The query reconstructs liquidation records from raw instructions and joins Dune’s prices.hour table to calculate the USD value of debt repaid and collateral seized.

We’ll work through the query in four phases, following how each CTE transforms and passes data to the next. Select a CTE in the interactive query below to explore its role in building the final dataset.

You can view the complete query on Dune or explore the source code and exported dataset on GitHub.

Select a CTE to inspect its role in the pipeline October 10, 2025
-- JUPITER LEND LIQUIDATIONS (BASE TABLE)

WITH

Phase 1 Identify liquidation contexts

    SELECT
        block_time,
        outer_instruction_index,
        tx_id 
    FROM solana.instruction_calls
    WHERE executing_account = 'jupr81YtYssSyPt8jbnGuiWon5f6x9TcDEFxYe3Bdzi'
      AND substr("data", 1, 8) = from_hex('dfb3e27d302e274a')
      --AND inner_instruction_index IS NULL 
      AND tx_success = TRUE
      AND block_time >= TIMESTAMP '2025-10-10 00:00:00 UTC'
      AND block_time < TIMESTAMP '2025-10-11 00:00:00 UTC'
),

Phase 2 Reconstruct the amount legs

    SELECT
        a.block_time,
        a.tx_id,
        a.account_arguments,
        a.outer_instruction_index,
        a.inner_instruction_index,
        a."data" AS inner_data
    FROM solana.instruction_calls a
    JOIN liq_keys b
      ON a.tx_id = b.tx_id
     AND a.outer_instruction_index = b.outer_instruction_index
    WHERE a.inner_instruction_index IS NOT NULL
      AND a.block_time >= TIMESTAMP '2025-10-10 00:00:00 UTC'
      AND a.block_time < TIMESTAMP '2025-10-11 00:00:00 UTC'
),
    SELECT
        tx_id,
        outer_instruction_index,
        inner_instruction_index,
        inner_data,
        ROW_NUMBER() OVER (
            PARTITION BY tx_id, outer_instruction_index
            ORDER BY inner_instruction_index ASC
        ) AS rn
    FROM inners_raw
    WHERE substr(inner_data, 1, 8) = from_hex('d96ad06374972a87')
),
    SELECT
        tx_id,
        outer_instruction_index,
        rn,
        CAST(
          from_big_endian_64(
            reverse(substr(inner_data, 9, 8))
          ) AS DECIMAL(38, 0)
        ) AS arg1_i128,
        CAST(
          from_big_endian_64(
            reverse(substr(inner_data, 25, 8))
          ) AS DECIMAL(38, 0)
        ) AS arg2_i128
    FROM operate_ranked
),
    SELECT
        tx_id,
        outer_instruction_index,
        rn,
        CASE
            WHEN rn = 1 THEN
                CASE WHEN arg1_i128 <> 0 THEN arg1_i128 ELSE arg2_i128 END
        END AS borrow_raw_i128,
        CASE
            WHEN rn = 2 THEN
                CASE WHEN arg1_i128 <> 0 THEN arg1_i128 ELSE arg2_i128 END
        END AS supply_raw_i128
    FROM op_decoded
),
    SELECT
        tx_id,
        outer_instruction_index,
        MAX(borrow_raw_i128) AS borrow_raw_i128,
        MAX(supply_raw_i128) AS supply_raw_i128
    FROM op_ui_per_rn
    GROUP BY 1, 2
),

Phase 3 Attach liquidation metadata

    SELECT
        sc.block_time,
        sc.tx_id,
        sc.outer_instruction_index,
        sc.tx_signer            AS liquidator,
        sc.account_arguments,
        sc.account_arguments[7]  AS supply_mint,
        sc.account_arguments[8]  AS borrow_mint,
        sc.account_arguments[14] AS position,
        sc.executing_account,
        to_hex(substr(sc."data", 1, 8)) AS discr_hex
    FROM solana.instruction_calls sc
    JOIN liq_keys lk
      ON sc.tx_id = lk.tx_id
     AND sc.outer_instruction_index = lk.outer_instruction_index
    --WHERE sc.inner_instruction_index IS NULL
      WHERE substr(sc."data", 1, 8) = from_hex('dfb3e27d302e274a')
      AND sc.block_time >= TIMESTAMP '2025-10-10 00:00:00 UTC'
      AND sc.block_time < TIMESTAMP '2025-10-11 00:00:00 UTC'
)

Phase 4 Value and present the results

SELECT
    lm.block_time AS block_time,
    lm.tx_id,
    lm.outer_instruction_index,
    lm.borrow_mint AS debt_mint,
    pb.symbol AS debt_symbol,
    lm.supply_mint AS collateral_mint,
    ps.symbol AS collateral_symbol,
    lm.liquidator,
    lm.position,

    ABS(CAST(ou.supply_raw_i128 AS DOUBLE))
        / POWER(10, ps.decimals) AS collateral_seized_token,
    ABS(CAST(ou.supply_raw_i128 AS DOUBLE))
        / POWER(10, ps.decimals)
        * ps.price AS collateral_seized_usd,
    ABS(CAST(ou.borrow_raw_i128 AS DOUBLE))
        / POWER(10, pb.decimals) AS debt_repaid_token,
    ABS(CAST(ou.borrow_raw_i128 AS DOUBLE))
        / POWER(10, pb.decimals)
        * pb.price AS debt_repaid_usd,

    SUM(
        ABS(CAST(ou.supply_raw_i128 AS DOUBLE))
        / POWER(10, ps.decimals)
        * ps.price
    ) OVER (
        ORDER BY lm.block_time, lm.tx_id, lm.outer_instruction_index
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_collateral_seized_usd,

    SUM(
        ABS(CAST(ou.borrow_raw_i128 AS DOUBLE))
        / POWER(10, pb.decimals)
        * pb.price
    ) OVER (
        ORDER BY lm.block_time, lm.tx_id, lm.outer_instruction_index
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_debt_repaid_usd
FROM liq_meta lm
LEFT JOIN op_ui ou
    ON lm.tx_id = ou.tx_id
   AND lm.outer_instruction_index = ou.outer_instruction_index
LEFT JOIN prices.hour pb
  ON pb.contract_address = from_base58(lm.borrow_mint)
 AND pb.timestamp = DATE_TRUNC('hour', lm.block_time)
 AND pb.blockchain = 'solana'
LEFT JOIN prices.hour ps
  ON ps.contract_address = from_base58(lm.supply_mint)
 AND ps.timestamp = DATE_TRUNC('hour', lm.block_time)
 AND ps.blockchain = 'solana';