Query editor
-- v5 — adopted from Strata team's official query (id 6617138).---- Methodology change vs prior versions:-- * pps source switched to strata_ethereum.srusde_call_converttoassets-- (function-call traces of the ERC-4626 view convertToAssets(shares)).-- This is the Strata team's own canonical signal: a pure view that-- returns the contract's current totalAssets/totalSupply, with no-- fee discount, no event sparsity, no rounding bias.-- * pps per bucket aggregated as SUM(output_0) / SUM(shares) over all-- calls in the chosen block, exactly as the team's query does.-- * APY annualization is COMPOUNDED, matching the team's formula:-- POWER(rate_t / rate_{t-N}, periods_per_year/N) - 1-- instead of the linear (rate-1)*365/N we used before.---- Adaptations for the incident zoom:-- * Granularity is hourly (team uses daily) so the Apr 19 spike is-- visible intraday. Within each hour we still take the LATEST block-- observed and SUM(output_0)/SUM(shares) within it.-- * Realized APR window is 24h trailing (compounded) so the spike is-- captured but discrete pps step-jumps are smoothed.-- * Time range is restricted to >= 2026-04-18 for the zoom view.WITH hourly_blocks AS (-- Latest block in each hour where convertToAssets was calledSELECTdate_trunc('hour', call_block_time) AS hour,max(call_block_number) AS max_blockFROM strata_ethereum.srusde_call_converttoassetsWHERE call_block_time >= TIMESTAMP '2026-04-15'GROUP BY 1),hourly_pps AS (SELECThb.hour,-- SUM-then-divide aggregation, matching Strata team's query(sum(c.output_0) * 1e18) / NULLIF(sum(c.shares), 0) AS pps_scaledFROM hourly_blocks hbJOIN strata_ethereum.srusde_call_converttoassets cON c.call_block_number = hb.max_blockWHERE c.call_block_time >= TIMESTAMP '2026-04-15'GROUP BY 1),hour_grid AS (-- Generate every hour from Apr 17 onward; we'll forward-fill missing-- hours so the 24h LAG always finds a value.SELECT date_add('hour', CAST(n AS integer), TIMESTAMP '2026-04-15 00:00:00') AS hourFROM unnest(sequence(0, 24 * 12)) AS s(n)),filled_pps AS (SELECTg.hour,last_value(p.pps_scaled) IGNORE NULLS OVER (ORDER BY g.hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pps_scaledFROM hour_grid gLEFT JOIN hourly_pps p ON p.hour = g.hour),realized AS (SELECThour,