Query editor
-- This query used incompatible data types from Dune SQL alpha and may need to be updated.-- More details can be found on https://dune.com/docs/query/dunesql-changes/with dates as (withday_seq as(SELECT(sequence(cast('2022-09-01' as date),cast(now() as date),interval '1' day)) day)selectdays.dayfromday_seqcross join unnest(day) as days(day)),volumes as (select date_trunc('day', call_block_time) as time, CAST(output_0 as double) as steth, CAST(_wstETHAmount as DOUBLE) as wstethfrom lido_ethereum.WstETH_call_unwrap where call_success = TRUEunion allselect date_trunc('day', call_block_time) as time, CAST(_stETHAmount as double) as steth, CAST(output_0 as DOUBLE) as wstethfrom lido_ethereum.WstETH_call_wrap where call_success = TRUE),prices as (select day, sum(steth)/sum(wsteth) AS pricefrom volumesright join dates on dates.day = volumes.timegroup by 1), steth_prices as (select distinct date_trunc('day', minute) as day,last_value(price) over (partition by date_trunc('day', minute) order by minute range between unbounded preceding and unbounded following) as pricefrom prices.usdwhere contract_address = 0xae7ab96520de3a18e5e111b5eaab095312d7fe84and minute >= CAST('2022-09-01 00:00' as timestamp)),wsteth_price_with_lag as (select wsteth_price.day,case when wsteth_price.price > 1 then wsteth_price.price*steth_price.pricewhen lead(wsteth_price.price*steth_price.price) over (order by wsteth_price.day) >1 then lead(wsteth_price.price*steth_price.price) over (order by wsteth_price.day)else lag(wsteth_price.price*steth_price.price) over (order by wsteth_price.day)end as pricefrom prices wsteth_priceleft join steth_prices steth_price on wsteth_price.day = steth_price.daywhere wsteth_price.price >0order by 1), bridged_txs as (select date_trunc('day', evt_block_time) as time, sum(CAST(value as DOUBLE)) as valuefrom erc20_ethereum.evt_Transfer