Query editor
WITH logs AS (SELECTblock_time,varbinary_to_uint256(varbinary_substring(data, 1, 32)) AS assets,varbinary_to_uint256(varbinary_substring(data, 33, 32)) AS shares,ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('hour', block_time) ORDER BY block_time) AS rnFROM ethereum.logsWHERE contract_address = 0xa3931d71877C0E7a3148CB7Eb4463524FEc27fbDAND block_time >= TIMESTAMP '2024-09-15'AND topic0 IN (0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7,0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db)),hourly AS (SELECT block_time, ROUND(assets * 1e10 / shares) AS ratioFROM logsWHERE rn = 1),calc AS (SELECTblock_time,(ratio - LAG(ratio) OVER (ORDER BY block_time))/ date_diff('millisecond', LAG(block_time) OVER (ORDER BY block_time), block_time)* 1000 * 60 * 60 * 24 * 365 / 1e10 AS aprFROM hourly),daily AS (SELECTDATE(block_time) AS date,AVG(apr) AS sUSDS_apr_pct,(POWER(1 + AVG(apr) / 365, 365) - 1) AS sUSDS_apy_pctFROM calcWHERE apr BETWEEN 0 AND 0.2GROUP BY DATE(block_time))SELECT *FROM dailyORDER BY date DESC;