Query editor
WITH oracle_price as (SELECT date_trunc('hour', period) as period, max_by(price, period) as priceFROM (-- Oracle from CL Aggregator EUR/USD 0xb49f677943BC038e9857d61E7d053CaA2C1734C1SELECT evt_block_time as period, current / 1e8 as priceFROM chainlink_ethereum.accesscontrolledocr2aggregator_evt_answerupdatedWHERE contract_address = 0x966Dad3B93C207A9EE3a79C336145e013C5cD3fcand evt_block_date >= DATE'2025-09-29'UNION ALLSELECT block_time, oracle_price as priceFROM chainlink.price_feedsWHERE proxy_address = 0xa14d53bc1f1c0f31b4aa3bd109344e5009051a84and aggregator_address = 0x7aaee6ad40a947a162deab5afd0a1e12be6ff871and block_time >= DATE'2025-09-29'and feed_name = 'EUR / USD')group by 1), trades as (select date_trunc('hour', block_time) as period,approx_percentile(case when token_bought_symbol ='EURCV' then token_sold_amount/token_bought_amount else token_bought_amount / token_sold_amount end, 0.5) as price_50,approx_percentile(case when token_bought_symbol ='EURCV' then token_sold_amount/token_bought_amount else token_bought_amount / token_sold_amount end, 0.05) as price_lower,approx_percentile(case when token_bought_symbol ='EURCV' then token_sold_amount/token_bought_amount else token_bought_amount / token_sold_amount end, 0.95) as price_upper,count(*) as transactions,count(case when project = 'balance' then 1 else 0 end) as transactions_balancer,sum(case when token_bought_symbol ='EURCV' then token_bought_amount else token_sold_amount end) as volume_eurofrom dex.tradeswhere blockchain = 'ethereum'and block_date >= DATE'2025-09-29'-- EURCV / USDCand project_contract_address = 0x1F195908f2EE7A6Fc15D33B30c82298F568E805C-- filter out 0 trades hash 0xe2823a8967b2dbe9c02443205cdf6f6f3052d156c6f715b3373b615454f2e692and 0 not in (token_bought_amount, token_sold_amount)group by 1), trades_deviation as (select period, price_50, price as chainlink_price, price_50/price-1.0 as deviation_chainlink,abs(price_50/price-1.0) as deviation_chainlink_abs, volume_eurofrom trades join oracle_price using (period)), daily_trades_deviation as (select date_trunc('day', period) as period,max_by(price_50, period) as price,max_by(chainlink_price, period) as chainlink_price,approx_percentile(deviation_chainlink_abs, 0.5) as abs_error,sum(volume_euro) as volume_eurofrom trades_deviationgroup by 1)select *,avg(abs_error) over (order by period ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as abs_error_7_days,avg(abs_error) over (order by period ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) * 100 as abs_error_7_days_fmt,avg(volume_euro) over (order by period ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as trading_volume_trendfrom daily_trades_deviationorder by 1 desc