Query editor
-- First find the trove managers for each trove id at all times-- When there is a "troveUpdated" event, it means the trove is no longer managed by a batch managerwith fxrp_trove_manager_address_updates as (SELECT evt_block_number,evt_block_time,evt_index,_troveId as trove_id,_interestBatchManager as interest_batch_managerFROM enosys_loans_flare.trovemanager_fxrp_evt_batchedtroveupdated btUNION ALLSELECTevt_block_number,evt_block_time,evt_index,_troveId as trove_id,0x0000000000000000000000000000000000000000 as _interestBatchManager -- i.e. no batch managerFROM enosys_loans_flare.trovemanager_fxrp_evt_troveupdated)-- For each batch updated (bu) event, make sure to apply it to the relevant troves (with latest manager = bu manager), fxrp_bu_with_trove_rows as (SELECTbu.*,tm.trove_id,tm.interest_batch_manager,ROW_NUMBER() OVER (PARTITION BY bu.evt_tx_hash, bu.evt_block_number, bu.evt_index, bu._interestBatchManager, tm.trove_idORDER BY (bu.evt_block_number - tm.evt_block_number) ASC, (bu.evt_index - tm.evt_index) ASC) AS rnFROM enosys_loans_flare.trovemanager_fxrp_evt_batchupdated buLEFT JOIN fxrp_trove_manager_address_updates tmON (tm.evt_block_number < bu.evt_block_number -- Ensure tm event happened before bu eventOR (tm.evt_block_number = bu.evt_block_number AND tm.evt_index <= bu.evt_index)))-- Keep latest value and right managers, fxrp_bu_with_trove as(select *from fxrp_bu_with_trove_rowswhere rn = 1 and _interestBatchManager = interest_batch_manager)-- Now get the bu events enriched with debt numbers from the latest BatchedTroveUpdated (bt) events-- We need to do that because we don't have the debt numbers in the bu events, fxrp_bu_closest_previous_events AS (-- Find the closest previous batchedTroveUpdated for any batchUpdatedSELECTcast(bu._debt/1e18 as double)/cast((case when bu._totalDebtShares = 0 then 1 else bu._totalDebtShares end)/1e18 as double)as debt_to_shares_ratio,bu.evt_block_time as block_time,bu.evt_tx_hash as tx_hash,bu.evt_index as tx_index,