Query editor
-- nado_ink_competition_collateral_period_v01 (parameterized)-- Grain: one row per period x subaccount x wallet x collateral asset.-- Periods: 30d baseline and exact competition window.-- Purpose: deposit/withdrawal facts for comp-associated wallets after joining Archive registrations downstream.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,CAST(DATE_ADD('day', -CAST('{{baseline_days}}' AS INTEGER), TIMESTAMP '{{competition_start_time}}') AS DATE) AS scan_start_date,CAST(TIMESTAMP '{{competition_end_time}}' AS DATE) AS scan_end_date),asset_meta AS (SELECT *FROM (VALUES(0, 0x0200c29006150606b650577bbe7b6248f58470c1, 'USDT0'),(1, 0x73e0c0d45e048d25fc26fa3159b0aa04bfa4db98, 'kBTC'),(3, 0x4200000000000000000000000000000000000006, 'WETH'),(5, 0x2d270e6886d130d724215a266106e6832161eaed, 'USDC'),(11, 0xf9e0af5e093c6ff7d75983414826462fc4abe430, 'UNKNOWN_11')) AS t(asset_product_id, token_address, asset_symbol)),collateral_events AS (SELECTp.competition_start_time,p.competition_end_time,p.baseline_start_time,CASEWHEN mc.evt_block_time >= p.baseline_start_timeAND mc.evt_block_time < p.competition_start_time THEN 'baseline_30d'WHEN mc.evt_block_time >= p.competition_start_timeAND mc.evt_block_time < p.competition_end_time THEN 'competition'END AS period_label,mc.evt_block_time,mc.evt_block_date AS utc_date,concat('0x', lower(to_hex(bytearray_substring(mc.subaccount, 1, 20)))) AS wallet_address,concat('0x', lower(to_hex(mc.subaccount))) AS subaccount,CAST(mc.productId AS BIGINT) AS asset_product_id,COALESCE(am.asset_symbol, concat('asset_', CAST(mc.productId AS VARCHAR))) AS asset_symbol,am.token_address,CAST(mc.amount AS DOUBLE) / 1e18 AS asset_amount,CASEWHEN am.asset_symbol IN ('USDT0', 'USDC') THEN 1.0ELSE pd.priceEND AS asset_price_usdFROM nado_ink.clearinghouse_evt_modifycollateral AS mcCROSS JOIN params AS pLEFT JOIN asset_meta AS amON CAST(mc.productId AS BIGINT) = am.asset_product_idLEFT JOIN prices.day AS pdON pd.blockchain = 'ink'AND DATE(pd.timestamp) = mc.evt_block_dateAND lower(CAST(pd.contract_address AS VARCHAR)) = lower(CAST(am.token_address AS VARCHAR))WHERE mc.evt_block_date BETWEEN p.scan_start_date AND p.scan_end_dateAND mc.evt_block_time >= p.baseline_start_timeAND mc.evt_block_time < p.competition_end_timeAND concat('0x', lower(to_hex(bytearray_substring(mc.subaccount, 1, 20)))) <> '0x0000000000000000000000000000000000000000')