Query editor
-- Fork of Query 7595081 (Aave USDC Supply Rate — Daily TWA with Midnight Gap Fix)-- Change: V3 end bound TIMESTAMP '2026-01-01' -> rolling CURRENT_DATE (complete days only).-- USDC (Ethereum mainnet): 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48-- liquidityRate is in Ray units (1e27), representing annualized supply APR.WITHv2_events AS (SELECTevt_block_time AS ts,CAST(liquidityRate AS DOUBLE) / 1e27 AS rate_ann,'v2' AS sourceFROM aave_v2_ethereum.LendingPool_evt_ReserveDataUpdatedWHERE reserve = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND evt_block_time >= TIMESTAMP '2020-12-01'AND evt_block_time < TIMESTAMP '2023-01-27'),v3_events AS (SELECTevt_block_time AS ts,CAST(liquidityRate AS DOUBLE) / 1e27 AS rate_ann,'v3' AS sourceFROM aave_v3_ethereum.Pool_evt_ReserveDataUpdatedWHERE reserve = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND evt_block_time >= TIMESTAMP '2023-01-27'AND evt_block_time < CURRENT_DATE),all_events AS (SELECT * FROM v2_eventsUNION ALLSELECT * FROM v3_events),events_with_lead AS (SELECTts,rate_ann,source,date_trunc('day', ts) AS dt,LEAD(ts) OVER (ORDER BY ts) AS next_tsFROM all_events),first_event_per_day AS (SELECT dt, MIN(ts) AS first_tsFROM events_with_leadGROUP BY dt),last_event_per_day AS (SELECT dt, rate_ann AS last_rate, sourceFROM (SELECTdt,rate_ann,source,ROW_NUMBER() OVER (PARTITION BY dt ORDER BY ts DESC) AS rnFROM events_with_lead