Query editor
WITH silos AS (SELECTsilo as silo_address,t.symbol as silo_symbolFROM silo_finance_optimism.silorepository_evt_newsilo sleft jointokens.erc20 t on s.asset=t.contract_addressand t.blockchain='optimism'),liqs as (SELECTblock_time,contract_address,s.silo_symbol,-- Decode the indexed parameters from topics.bytearray_substring(topic1, 1+12, 20) as asset,bytearray_substring(topic2, 1+12, 20) as user,-- Decode non-indexed parameters from data:bytearray_to_uint256(bytearray_substring(data, 1, 32)) as shareAmountRepaid,bytearray_to_uint256(bytearray_substring(data, 33, 32)) as seizedCollateral,tx_hash,index,CASEWHEN tx_to = 0xa673ddce04554ad794972509c5e821780f92ae67 THEN tx_from -- liquidator helperELSE tx_toEND AS liquidatorFROM optimism.logs lleft join silos son l.contract_address = s.silo_addressWHERE l.topic0 = 0xf3fa0eaee8f258c23b013654df25d1527f98a5c7ccd5e951dd77caca400ef972and l.contract_address IN (SELECT silo_address FROM silos)), joined as (selectliqs.block_time as time,-- liqs.asset,-- liqs.user,p.price as price,p.symbol as symbol,p.decimals as decimals,liqs.shareAmountRepaid,liqs.seizedCollateral,liqs.tx_hash,liqs.silo_symbol,liqs.liquidator,liqs.user,liqs.index,ABS(to_unixtime(p.minute) - to_unixtime(liqs.block_time)) AS time_difffrom liqsleft join (select * from prices.usd where blockchain='optimism') p ondate_trunc('hour', p.minute) = date_trunc('hour', liqs.block_time)and p.contract_address = liqs.asset), ranked AS (SELECT*,ROW_NUMBER() OVER (PARTITION BY tx_hash, index ORDER BY time_diff ASC) AS rn