Query editor
-- Base table with vault data, standardizing vault names and filtering for relevant vaults and starting date (June 2025)WITH vault_data AS (SELECThour as timestamp, -- Keep hourly granularity from sourceCASE TRIM(metamorpho_name)WHEN 'Usual Boosted USDC' THEN 'MEV Usual USDC'ELSE metamorpho_nameEND as vault,apy as hourly_apy -- Renamed to clarify this is hourly dataFROM query_4583594WHERE address in (0xB3f4D94A209045Ef35661e657Db9ADAc584141f1)and cast(hour AS date) >= DATE('2025-12-20')),-- Calculate daily averages from hourly datadaily_avg AS (SELECTDATE_TRUNC('day', timestamp) as day,vault,AVG(hourly_apy) as daily_apy -- Average of hourly APYs for each dayFROM vault_dataGROUP BY DATE_TRUNC('day', timestamp), vault),-- Calculate various time-window APY averagesapy_calculations AS (SELECTday,vault,daily_apy as apy_1d, -- Daily average from hourly data-- 7-day moving average of daily APYsAVG(daily_apy) OVER (PARTITION BY vaultORDER BY dayRANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW -- Include current day plus 6 preceding days) as apy_7d,-- 30-day moving average of daily APYsAVG(daily_apy) OVER (PARTITION BY vaultORDER BY dayRANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW -- Include current day plus 29 preceding days) as apy_30d,-- 90-day moving average of daily APYsAVG(daily_apy) OVER (PARTITION BY vaultORDER BY dayRANGE BETWEEN INTERVAL '89' DAY PRECEDING AND CURRENT ROW -- Include current day plus 89 preceding days) as apy_90dFROM daily_avg)-- Final output with rounded values for readabilitySELECTday as date,vault,ROUND(apy_1d, 4) as apy_1d,ROUND(apy_7d, 4) as apy_7d,ROUND(apy_7d, 4)*100 as apy_7d_pct,ROUND(apy_30d, 4) as apy_30d,ROUND(apy_30d, 4)*100 as apy_30d_pct,