Query editor
WITH sot AS (SELECT *FROM query_6231640),time_seq as (selectsequence(CAST('2025-05-01' as timestamp),date_trunc('hour', cast(now() as timestamp)),interval '1' hour) as time),hours as (selecttime.time as hourfrom time_seqcross join unnest(time) as time(time)),hourly_rates as (select*,lead(hour, 1, current_timestamp) over (partition by trove_id, collateral_type order by hour asc) as next_hourfrom (selectdate_trunc('hour', block_time) as hour,trove_id,collateral_type,max_by(collateral, (block_number, tx_index)) as collateral,max_by(debt, (block_number, tx_index)) as debtfromquery_6370622group by 1, 2, 3)),filled_rates as (selecth.hour,c.trove_id,c.collateral_type,c.collateral,c.debtfromhourly_rates cinner joinhours hon c.hour <= h.hourand h.hour < c.next_hour),get_prices AS (SELECTs.collateral_type,s.price_symbol,max_by(p.price, p.timestamp) AS latest_priceFROM query_6231640 sLEFT JOIN prices.day p