Query editor
WITH bounds AS (SELECT CAST('{{week_end}}' AS timestamp) AS week_end),fills AS (SELECTvarbinary_substring(f.subaccount, 1, 20) AS wallet,f.isTaker,abs(CAST(f.quoteDelta AS double)) / 1e18 AS quote_abs,CAST(floor(date_diff('second', f.evt_block_time, b.week_end) / 604800.0) AS integer) AS weeks_agoFROM nado_ink.offchainexchange_evt_fillorder fCROSS JOIN bounds bWHERE f.evt_block_date >= CAST(b.week_end - INTERVAL '36' DAY AS date)AND f.evt_block_time >= b.week_end - INTERVAL '35' DAYAND f.evt_block_time < b.week_end),weekly AS (SELECTweeks_ago,sum(CASE WHEN isTaker THEN quote_abs ELSE 0 END) / 7 AS adv,count(DISTINCT wallet) AS wauFROM fillsGROUP BY 1)SELECT(SELECT week_end FROM bounds) - INTERVAL '7' DAY * (w.weeks_ago + 1) AS window_start,w.adv,w.wau,w.adv / lead(w.adv) OVER (ORDER BY w.weeks_ago) - 1 AS adv_wow,w.adv / avg(w.adv) OVER (ORDER BY w.weeks_ago ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING) - 1 AS adv_trailing_4w,CAST(w.wau AS double) / lead(w.wau) OVER (ORDER BY w.weeks_ago) - 1 AS wau_wow,w.wau / avg(CAST(w.wau AS double)) OVER (ORDER BY w.weeks_ago ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING) - 1 AS wau_trailing_4wFROM weekly wORDER BY w.weeks_ago