Query editor
/*This query calculates the liquidity of (w)stETH pools in different blockchains over the past three mounths*/-- to create a calendar for the past full three months with a daily intervalwithdates AS (withday_seq AS (SELECT(sequence(cast(date_trunc('year', NOW()) - interval '3' year AS date),current_date,interval '1' day)) AS day)SELECTdays.dayFROMday_seqCROSS JOIN unnest (day) AS days (day))-- to retrieve daily amounts of (w)stETH involved in unwrapping and wrapping transactions-- for the further calculation of wstETH:stETH, volumes AS (SELECTu.call_block_time AS time,"output_0" AS steth,"_wstETHAmount" AS wstethFROMlido_ethereum.WstETH_call_unwrap uWHERE"call_success" = TRUEUNION allSELECTu."call_block_time","_stETHAmount" AS steth,"output_0" AS wstethFROMlido_ethereum.WstETH_call_wrap uWHERE"call_success" = TRUE)-- to calculate the daily rate of wstETH:stETH from CTE 'volumes' combined with CTE 'dates', wsteth_rate AS (SELECTdate_trunc('day', d.day) AS day,SUM(cast(steth AS DOUBLE)) / SUM(cast(wsteth AS DOUBLE)) AS rateFROMdates dLEFT JOIN volumes v ON date_trunc('day', v.time) = date_trunc('day', d.day)GROUP BY1