Query editor
WITH get_trove_id AS (SELECTmax_by(address, day) AS owner,collateral_type,token_id AS trove_idFROM query_6370564 -- NFT holders / trove ownersWHERE balance > 0GROUP BY collateral_type, token_id),get_latest AS (SELECTtv.*,gt.ownerFROM (SELECTtrove_id,collateral_type,max_by(debt, (block_number, tx_index)) AS debt,max_by(collateral, (block_number, tx_index)) AS collateral,max_by(interest_rates, (block_number, tx_index)) AS interest_rates,max_by(interest_batch_manager, (block_number, tx_index)) AS interest_batch_managerFROM query_6370622 -- trove updatesGROUP BY trove_id, collateral_type) tvLEFT JOIN get_trove_id gtON tv.trove_id = gt.trove_idAND tv.collateral_type = gt.collateral_type),get_prices AS (SELECTdate_trunc('day', p.timestamp) AS day,s.collateral_type,max_by(p.price, p.timestamp) AS priceFROM query_6231640 sLEFT JOIN prices.day pON p.symbol = s.price_symbolAND p.blockchain = 'flare'AND p.timestamp >= CURRENT_DATEGROUP BYdate_trunc('day', p.timestamp),s.collateral_type),enrich AS (SELECTconcat('', substring(cast(gl.owner AS varchar), 1, 5), '...', "right"(cast(gl.owner AS varchar), 4)) AS owner_slim,gl.owner,concat('', substring(cast(gl.trove_id AS varchar), 1, 5), '...', "right"(cast(gl.trove_id AS varchar), 4)) AS trove_id,gl.collateral_type,gl.debt,gl.collateral,gl.collateral * gp.price AS collateral_usd,(gl.collateral * gp.price)/gl.debt AS collateral_ratio,gl.interest_rates/100 AS interest_rate,times.created_time,times.last_modified_time,CASEWHEN (to_unixtime(now()) - to_unixtime(times.created_time)) / (7 * 24 * 60 * 60) <= 1 THEN '1 week'