Query editor
WITH product_meta AS (SELECT *FROM (VALUES(0, 'USDT0', 0x0200C29006150606B650577BBE7B6248F58470c1, 6, 1.0),(1, 'KBTC', 0x73E0C0d45E048D25Fc26Fa3159b0aA04BfA4Db98, 8, 100000.0),(3, 'WETH', 0x4200000000000000000000000000000000000006, 18, 4000.0),(5, 'USDC', 0x2D270e6886d130D724215A266106e6832161EAEd, 6, 1.0),(11, 'NLP', 0xf9E0aF5E093c6fF7D75983414826462fC4aBE430, 18, 1.0),(115, 'wQQQx', 0x4c1ae29c159838fc1b224636e28e086eb69101f7, 18, 660.4),(117, 'wSPYx', 0xe7e553cd128f0011777323a0b44a7b96ea1cb540, 18, 660.4)) AS t(product_id, symbol, token_address, decimals, fallback_price_usd)),latest_prices AS (SELECTpm.product_id,COALESCE(MAX(p.price), pm.fallback_price_usd) AS price_usdFROM product_meta pmLEFT JOIN prices.usd_latest pON p.blockchain = 'ink'AND p.contract_address = pm.token_addressGROUP BY pm.product_id, pm.fallback_price_usd),collateral_events AS (SELECTevt_block_time,evt_block_date,evt_tx_hash,evt_index,CAST(productId AS INTEGER) AS product_id,CASE WHEN amount > 0 THEN 'deposit' ELSE 'withdrawal' END AS action,CONCAT('0x', LOWER(SUBSTRING(CAST(subaccount AS VARCHAR), 3, 40))) AS user_address,subaccount,ABS(CAST(amount AS DOUBLE)) / 1e18 AS amount_tokenFROM nado_ink.clearinghouse_evt_modifycollateralWHERE contract_address = 0xD218103918C19D0A10cf35300E4CfAfbD444c5fEAND evt_block_number >= 29851015AND evt_block_date >= CURRENT_DATE - INTERVAL '{{Timeframe - days}}' DAYAND amount <> 0),events_with_prices AS (SELECTe.*,pm.symbol,lp.price_usd,e.amount_token * lp.price_usd AS amount_usdFROM collateral_events eJOIN product_meta pm ON pm.product_id = e.product_idJOIN latest_prices lp ON lp.product_id = e.product_id),event_rollup AS (SELECT'by_product' AS row_type,action,product_id,symbol,COUNT(*) AS event_count,COUNT(DISTINCT user_address) AS user_count,SUM(amount_token) AS total_size_token,SUM(amount_usd) AS total_size_usd,