Query editor
withget_trove_id as (selectmax_by(address, day) as owner,collateral_type,token_id as trove_idfromquery_6370564where address != 0x0000000000000000000000000000000000000000group by 2, 3),get_deets as (selectdate_trunc('minute', q4.block_time) as block_minute,q4.block_number,q4.block_time,q4.tx_hash,gt.owner,q4.trove_id,q4.collateral_type,q4.debt_change,q4.collateral_change,q4.interest_rates,l._price/1e18 as pricefromquery_6370673 q4left join query_6370531 l on q4.tx_hash = l.tx_hashleft joinget_trove_id gton q4.trove_id = gt.trove_idand q4.collateral_type = gt.collateral_typewhere cast(operation as double) = 5)selectgd.block_time,gd.block_number,get_href(get_chain_explorer_tx_hash('flare', gd.tx_hash),concat('', substring(cast(gd.tx_hash as varchar), 1, 5), '...', "RIGHT"(cast(gd.tx_hash as varchar), 4))) as tx_hash,get_href(get_chain_explorer_address('flare', gd.owner),concat('', substring(cast(gd.owner as varchar), 1, 5), '...', "RIGHT"(cast(gd.owner as varchar), 4))) as owner,concat('', substring(cast(gd.trove_id as varchar), 1, 5), '...', "RIGHT"(cast(gd.trove_id as varchar), 4)) as trove_id,gd.collateral_type,gd.debt_change,gd.collateral_change,gd.price as collateral_price,gd.interest_rates/100 as interest_rates,(u.collateral - gd.collateral_change) * gd.price / (u.debt - gd.debt_change) as collateral_ratiofromget_deets gdleft join query_6370622 u -- trove updateson gd.trove_id = u.trove_idand gd.collateral_type = u.collateral_typeand gd.block_number = u.block_numberand gd.tx_hash = u.tx_hashorder by block_time desc, interest_rates desc