Query editor
WITH getFlows as (SELECT *FROM query_4669480),getBalance as (SELECT date_trunc('day',evt_block_time) as date,pool_address,symbol,underlying_price_symbol,SUM(SUM(amount)) OVER (PARTITION BY pool_address,symbol,underlying_price_symbol ORDER BY date_trunc('day',evt_block_time)) as cumulative_balanceFROM getFlowsGROUP BY 1,2,3,4),setLeadData as (SELECT *,lead(date,1,NOW()) OVER (PARTITION BY pool_address,symbol,underlying_price_symbol ORDER BY date) as latest_day FROM getBalance),gs as (SELECT gen_date FROM UNNEST(sequence(CAST('2025-01-01' AS TIMESTAMP),CAST(NOW() AS TIMESTAMP),interval '1' day)) as tbl(gen_date)),getPrices as (SELECT date_trunc('day',minute) as date,a.underlying_price_symbol,avg(price) as priceFROM query_4669480 a LEFT JOIN prices.usd b ON UPPER(a.underlying_price_symbol) = UPPER(b.symbol) AND b.blockchain = 'ethereum'GROUP BY 1,2)SELECT date,CASE WHEN pool_address = 0xca8711dAF13D852ED2121E4bE3894Dae366039E4 THEN 'VEDA ETHERFI' ELSE 'Concrete' END as label,SUM(cumulative_balance_usd) as cumulative_balance_usdFROM (SELECT x.date,x.pool_address,x.symbol,cumulative_balance,cumulative_balance * COALESCE(price,LAG(price,1) IGNORE NULLS OVER (PARTITION BY x.symbol ORDER BY x.date)) as cumulative_balance_usdFROM (SELECTgs.gen_date as date,pool_address,symbol,underlying_price_symbol,cumulative_balanceFROM setLeadData gINNER JOIN gs ON g.date <= gs.gen_date AND gs.gen_date < g.latest_day) x LEFT JOIN getPrices y ON x.date = y.date AND UPPER(x.underlying_price_symbol) = UPPER(y.underlying_price_symbol)) gGROUP BY 1,2ORDER BY 1 DESC