Query editor
-- =================================================================-- ANALYSIS: dbo.hyperoute_total_tvl_usd-- =================================================================-- DESCRIZIONE: Calculates total aggregated TVL in USD by summing-- deposited HYPE & USD₮0 in the Hyperoute Vaults.-- =================================================================WITH-- >> STEP 1: Calculate closing daily TVL of the HYPE Strategy in USD.hype_tvl_daily AS (WITHhype_balance_history AS (SELECT T.block_time, SUM(T.amount) OVER (ORDER BY T.block_time ASC, T.tx_hash ASC) AS cumulative_balance_hypeFROM query_5741394 AS T WHERE T.strategy_name = 'HYPE Strategy'),price_history AS (SELECT day, price_usd FROM query_5973523),enriched_history AS (SELECT B.block_time, (B.cumulative_balance_hype * P.price_usd) AS tvl_usdFROM hype_balance_history AS BLEFT JOIN price_history AS P ON CAST(B.block_time AS DATE) = P.day)SELECT CAST(block_time AS DATE) AS day, 'HYPE Strategy' AS strategy_name, tvl_usdFROM (SELECT block_time, tvl_usd, ROW_NUMBER() OVER (PARTITION BY CAST(block_time AS DATE) ORDER BY block_time DESC) AS rnFROM enriched_history WHERE tvl_usd IS NOT NULL) WHERE rn = 1),-- >> STEP 2: Calculate closing daily TVL of the USD₮0 Strategy.usdt0_tvl_daily AS (SELECT CAST(block_time AS DATE) AS day, 'USD₮0 Strategy' AS strategy_name, cumulative_balance AS tvl_usdFROM (SELECTblock_time,cumulative_balance,ROW_NUMBER() OVER (PARTITION BY CAST(block_time AS DATE) ORDER BY block_time DESC) AS rnFROM (SELECT block_time, SUM(amount) OVER (ORDER BY block_time ASC, tx_hash ASC) AS cumulative_balanceFROM query_5741394 WHERE strategy_name = 'USD₮0 Strategy')) WHERE rn = 1),-- >> STEP 3: Merge the two time series in one dataset.combined_daily_tvl AS (SELECT day, strategy_name, tvl_usd FROM hype_tvl_dailyUNION ALLSELECT day, strategy_name, tvl_usd FROM usdt0_tvl_daily),-- Step 4: Pivot & Apply LOCF Logic to create complete time series.pivoted_with_nulls AS (SELECTday,CASE WHEN strategy_name = 'HYPE Strategy' THEN tvl_usd END AS hype_tvl,CASE WHEN strategy_name = 'USD₮0 Strategy' THEN tvl_usd END AS usdt0_tvlFROM combined_daily_tvl),