Query editor
-- Daily inflow/outflow per collateral product (quote + spots)WITH product_meta AS (SELECT * FROM (VALUES(0, 'USDT0'),(1, 'KBTC'),(3, 'WETH'),(5, 'USDC')) AS t(product_id, symbol)),logs_raw AS (SELECTblock_time,CAST(BYTEARRAY_TO_INT256(BYTEARRAY_SUBSTRING(data, 1, 32)) AS DOUBLE) / 1e18 AS amount,BYTEARRAY_TO_UINT256(BYTEARRAY_SUBSTRING(data, 1 + 32, 32)) AS product_idFROM ink.logsWHERE contract_address = 0xD218103918C19D0A10cf35300E4CfAfbD444c5fEAND topic0 = 0xfe53084a731040f869d38b1dcd00fbbdbc14e10d7d739160559d77f5bc80cf05),modify_collateral AS (SELECT r.*FROM logs_raw rJOIN product_meta pm ON pm.product_id = r.product_id),daily AS (SELECT DATE_TRUNC('day', block_time) AS block_date, * FROM modify_collateral),agg AS (SELECTblock_date,product_id,SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS inflow_amount,SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS outflow_amountFROM dailyGROUP BY block_date, product_id)SELECTblock_date AS time,COALESCE(SUM(CASE WHEN product_id = 0 THEN inflow_amount END), 0) AS inflow_usdt0,COALESCE(SUM(CASE WHEN product_id = 0 THEN outflow_amount END), 0) AS outflow_usdt0,COALESCE(SUM(CASE WHEN product_id = 1 THEN inflow_amount END), 0) AS inflow_kbtc,COALESCE(SUM(CASE WHEN product_id = 1 THEN outflow_amount END), 0) AS outflow_kbtc,COALESCE(SUM(CASE WHEN product_id = 3 THEN inflow_amount END), 0) AS inflow_weth,COALESCE(SUM(CASE WHEN product_id = 3 THEN outflow_amount END), 0) AS outflow_weth,COALESCE(SUM(CASE WHEN product_id = 5 THEN inflow_amount END), 0) AS inflow_usdc,COALESCE(SUM(CASE WHEN product_id = 5 THEN outflow_amount END), 0) AS outflow_usdcFROM aggGROUP BY block_dateORDER BY block_date DESC;