Query editor
withtroves as (selectdate_trunc('day', block_time) as day,trove_id,collateral_type,max_by(debt, (block_number, tx_index)) as balancefromquery_6370622group by 1, 2, 3),missing_days as (select*,lead(day, 1, current_timestamp) over (partition by trove_id, collateral_type order by day asc) as next_dayfromtroves),time_seq AS (selectsequence(CAST('2025-12-01' as timestamp),date_trunc('day', cast(now() as timestamp)),interval '1' day) as time),days AS (selecttime.time as dayfrom time_seqcross join unnest(time) as time(time))selectday,collateral_type,count(trove_id) as num_trovesfrom (selectd.day,c.trove_id,c.collateral_type,c.balancefrommissing_days cinner joindays don c.day <= d.dayand d.day < c.next_day)where balance > 0group by 1, 2order by day desc