Query editor
/* APY comparison, one column per product, newest day first.ERC-4626: 7-day trailing growth of assets/shares, annualised (decimal-invariant).Aave: supply APY from liquidityRate (Ray), per-second compounding. */WITH vaults (name, vault) AS (VALUES('syrupUSDC', 0x80ac24aA929eaF5013f6436cdA2a7ba190f5Cc0b),('syrupUSDT', 0x356B8d89c1e1239Cbbb9dE4815c39A1474d5BA7D),('sUSDe', 0x9D39A5DE30e57443BfF2A8307A4256c8797A3497),('spUSDT', 0xe7dF13b8e3d6740fe17CBE928C7334243d86c92f)),erc4626_logs AS (SELECTv.name,l.block_time,l.block_number,l.index AS log_index,CAST(bytearray_to_uint256(bytearray_substring(l.data, 1, 32)) AS double) AS assets_d,CAST(bytearray_to_uint256(bytearray_substring(l.data, 33, 32)) AS double) AS shares_dFROM ethereum.logs lJOIN vaults v ON l.contract_address = v.vaultWHERE l.topic0 IN (0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7, -- Deposit(address,address,uint256,uint256)0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db -- Withdraw(address,address,address,uint256,uint256))AND l.block_time >= DATE '2024-01-01'),erc4626_ranked AS (SELECTname,CAST(date_trunc('day', block_time) AS date) AS d,assets_d / shares_d AS xrate,row_number() OVER (PARTITION BY name, date_trunc('day', block_time)ORDER BY block_number DESC, log_index DESC) AS rnFROM erc4626_logsWHERE shares_d > 0 AND assets_d > 0),erc4626_daily AS ( -- last exchange rate per vault per daySELECT name, d, xrate FROM erc4626_ranked WHERE rn = 1),spine AS ( -- continuous daily calendar per vaultSELECT v.name, s.dFROM vaults vCROSS JOIN UNNEST(sequence(DATE '2024-01-01', current_date, INTERVAL '1' DAY)) AS s(d)),erc4626_ffill AS ( -- forward-fill over gap daysSELECTsp.name,sp.d,LAST_VALUE(dd.xrate) IGNORE NULLS OVER (PARTITION BY sp.name ORDER BY sp.dROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS xrate