Query editor
-- nado_ink_competition_daily_baseline_v01 (parameterized)-- Grain: one row per baseline/competition 24h contest-aligned day x wallet cohort candidate.-- Purpose: DAU, signup, daily volume/revenue, and baseline overlay source for the dashboard spec.WITH params AS (SELECTTIMESTAMP '{{competition_start_time}}' AS competition_start_time,TIMESTAMP '{{competition_end_time}}' AS competition_end_time,DATE_ADD('day', -CAST('{{baseline_days}}' AS INTEGER), TIMESTAMP '{{competition_start_time}}') AS baseline_start_time,DATE '2025-11-17' AS history_start_date,CAST(TIMESTAMP '{{competition_end_time}}' AS DATE) AS competition_end_date),period_days AS (SELECT'baseline_30d' AS period_label,day_index AS period_day_index,DATE_ADD('day', day_index - (CAST('{{baseline_days}}' AS INTEGER) + 1), competition_start_time) AS period_day_start_time,DATE_ADD('day', day_index - CAST('{{baseline_days}}' AS INTEGER), competition_start_time) AS period_day_end_time,CAST(NULL AS INTEGER) AS competition_day_indexFROM paramsCROSS JOIN UNNEST(SEQUENCE(1, CAST('{{baseline_days}}' AS INTEGER))) AS t(day_index)UNION ALLSELECT'competition' AS period_label,day_index AS period_day_index,DATE_ADD('day', day_index - 1, competition_start_time) AS period_day_start_time,DATE_ADD('day', day_index, competition_start_time) AS period_day_end_time,day_index AS competition_day_indexFROM paramsCROSS JOIN UNNEST(SEQUENCE(1, DATE_DIFF('day', CAST(TIMESTAMP '{{competition_start_time}}' AS DATE), CAST(TIMESTAMP '{{competition_end_time}}' AS DATE)))) AS t(day_index)),fills AS (SELECTf.evt_block_time,f.evt_block_date,concat('0x', lower(to_hex(bytearray_substring(f.subaccount, 1, 20)))) AS wallet_address,concat('0x', lower(to_hex(f.subaccount))) AS subaccount,f.isTaker AS is_taker,ABS(CAST(f.quoteDelta AS DOUBLE)) / 1e18 AS fill_volume_usd,CAST(f.feeAmount AS DOUBLE) / 1e18 AS fee_usdFROM nado_ink.offchainexchange_evt_fillorder AS fCROSS JOIN params AS pWHERE f.evt_block_date BETWEEN p.history_start_date AND p.competition_end_dateAND f.evt_block_time < p.competition_end_timeAND concat('0x', lower(to_hex(bytearray_substring(f.subaccount, 1, 20)))) <> '0x0000000000000000000000000000000000000000'),wallet_history AS (SELECTf.wallet_address,MIN(f.evt_block_time) AS wallet_first_ever_trade_time,MAX(CASE WHEN f.evt_block_time < p.competition_start_time THEN f.evt_block_time END) AS wallet_last_trade_before_competition_time,COUNT_IF(f.evt_block_time >= p.baseline_start_time AND f.evt_block_time < p.competition_start_time) AS wallet_baseline_fill_events,COUNT_IF(f.evt_block_time >= p.competition_start_time AND f.evt_block_time < p.competition_end_time) AS wallet_competition_fill_events,MAX(p.competition_start_time) AS competition_start_time,MAX(p.competition_end_time) AS competition_end_time,MAX(p.baseline_start_time) AS baseline_start_timeFROM fills AS fCROSS JOIN params AS pGROUP BY 1),