Query editor
WITH daily_trades AS (SELECTblock_date,token_bought_mint_address,token_sold_mint_address,token_bought_amount,token_sold_amount,amount_usdFROM dex_solana.tradesWHERE blockchain = 'solana'AND block_time >= TIMESTAMP '2023-01-01'AND (token_bought_mint_address = 'Ex5DaKYMCN6QWFA4n67TmMwsH8MJV68RX6YXTmVM532C'OR token_sold_mint_address = 'Ex5DaKYMCN6QWFA4n67TmMwsH8MJV68RX6YXTmVM532C')),daily_metrics AS (SELECTblock_date,-- Calculate implied USDv price from each tradeCASEWHEN token_bought_mint_address = 'Ex5DaKYMCN6QWFA4n67TmMwsH8MJV68RX6YXTmVM532C'THEN amount_usd / NULLIF(token_bought_amount, 0)ELSE amount_usd / NULLIF(token_sold_amount, 0)END as trade_price,CASEWHEN token_bought_mint_address = 'Ex5DaKYMCN6QWFA4n67TmMwsH8MJV68RX6YXTmVM532C'THEN token_bought_amountELSE token_sold_amountEND as usdv_volumeFROM daily_trades),daily_aggregates AS (SELECTblock_date,SUM(trade_price * usdv_volume) / NULLIF(SUM(usdv_volume), 0) as daily_vwap,MAX(ABS(trade_price - 1.0)) as max_deviation_from_1FROM daily_metricsWHERE trade_price IS NOT NULLAND trade_price > 0AND trade_price < 10GROUP BY block_date)SELECTblock_date,daily_vwap,max_deviation_from_1,AVG(max_deviation_from_1) OVER (ORDER BY block_dateROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as rolling_7day_avg_deviationFROM daily_aggregatesORDER BY block_date DESC