Query editor
WITH usd1_trades AS (SELECTDATE_TRUNC('day', block_time) AS date,blockchain AS chain,block_time,amount_usd,CASEWHEN token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d THEN token_bought_amountWHEN token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d THEN token_sold_amountELSE NULLEND AS usd1_amount,CASEWHEN token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d THEN 'buy'WHEN token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d THEN 'sell'ELSE NULLEND AS sideFROM dex.tradesWHERE ((blockchain = 'ethereum' AND (token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d OR token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d))OR (blockchain = 'bnb' AND (token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d OR token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d))OR (blockchain = 'solana' AND (token_bought_symbol = 'USD1' OR token_sold_symbol = 'USD1')))AND amount_usd >= 1000AND block_time >= TIMESTAMP '2024-01-01'),intraday_prices AS (SELECTdate,chain,block_time,amount_usd / usd1_amount AS trade_priceFROM usd1_tradesWHERE usd1_amount IS NOT NULL AND usd1_amount > 0),daily_vwap AS (SELECTdate,chain,SUM(amount_usd) / SUM(usd1_amount) AS vwap_priceFROM usd1_tradesWHERE usd1_amount IS NOT NULL AND usd1_amount > 0GROUP BY date, chain),intraday_deviations AS (SELECTdate,chain,MAX(ABS(trade_price - 1.0) * 10000) AS max_intraday_deviation_bpsFROM intraday_pricesGROUP BY date, chain)SELECTdv.date,dv.chain,dv.vwap_price,ABS(dv.vwap_price - 1.0) * 10000 AS deviation_from_1_bps,id.max_intraday_deviation_bps,SUM(CASE WHEN ABS(dv.vwap_price - 1.0) * 10000 > 25 THEN 1 ELSE 0 END) OVER (PARTITION BY dv.chain) AS days_exceeding_25bpsFROM daily_vwap dvLEFT JOIN intraday_deviations id ON dv.date = id.date AND dv.chain = id.chain