Query editor
WITH builder_onboard AS (SELECTbuilder,min(evt_block_time) AS onboard_timeFROM nado_ink.offchainexchange_evt_builderfeepaymentGROUP BY builder),daily_vol AS (SELECTbuilder,date_trunc('day', evt_block_time) AS day,sum(builderFee / 1e18) AS daily_builder_fee,sum(fee / 1e18) AS daily_fee,sum(abs(quoteDelta)) / 1e18 / 1e6 AS daily_vol_m_usdFROM nado_ink.offchainexchange_evt_builderfeepaymentGROUP BY builder, date_trunc('day', evt_block_time)),daily_with_ma7 AS (SELECTbuilder,day,daily_vol_m_usd,SUM(daily_vol_m_usd) OVER (PARTITION BY builderORDER BY dayROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7 AS last_7d_avg_daily_volFROM daily_vol),latest_ma7 AS (SELECTbuilder,last_7d_avg_daily_vol,row_number() OVER (PARTITION BY builder ORDER BY day DESC) AS rnFROM daily_with_ma7)SELECTo.builder,o.onboard_time,round(sum(b.daily_builder_fee), 2) AS builder_fee,round(sum(b.daily_fee), 2) AS fee,round(sum(b.daily_vol_m_usd), 2) AS "vol(M Usd)",round(sum(b.daily_vol_m_usd) / greatest(1, date_diff('day', cast(o.onboard_time AS date), current_date)), 4) AS avg_day_vol,round(max(m.last_7d_avg_daily_vol), 4) AS last_7d_avg_daily_volFROM builder_onboard oJOIN daily_vol b ON b.builder = o.builderLEFT JOIN latest_ma7 m ON m.builder = o.builder AND m.rn = 1GROUP BY o.builder, o.onboard_timeORDER BY o.builder ASC;