Query editor
-- SELECT productId,-- SUM(CASE WHEN productId = 0 THEN amount/1e6 -- USDC-- WHEN productId = 1 THEN amount/1e8 -- wBTC-- WHEN productId = 3 THEN amount/1e18 -- wETH-- ELSE CAST(amount AS DOUBLE)-- END) AS amount-- FROM vertex_endpoint_arbitrum.Endpoint_call_depositCollateral-- WHERE call_success = true-- GROUP BY 1-- SELECT productId, SUM(amount) AS amount FROM (-- SELECT CAST(JSON_VALUE(txn, 'strict $.productId') AS BIGINT) AS productId,-- SUM(CASE WHEN JSON_VALUE(txn, 'strict $.productId') = '0' THEN CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e6 -- USDC-- WHEN JSON_VALUE(txn, 'strict $.productId') = '1' THEN CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e8 -- wBTC-- WHEN JSON_VALUE(txn, 'strict $.productId') = '3' THEN CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e18 -- wETH-- ELSE CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)-- END) AS amount-- FROM vertex_arbitrum.Clearinghouse_call_depositCollateral-- WHERE call_success = true-- GROUP BY 1-- UNION ALL-- SELECT CAST(JSON_VALUE(txn, 'strict $.productId') AS BIGINT) AS productId,-- SUM(CASE WHEN JSON_VALUE(txn, 'strict $.productId') = '0' THEN -CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e6 -- USDC-- WHEN JSON_VALUE(txn, 'strict $.productId') = '1' THEN -CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e8 -- wBTC-- WHEN JSON_VALUE(txn, 'strict $.productId') = '3' THEN -CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)/1e18 -- wETH-- ELSE -CAST(JSON_VALUE(txn, 'strict $.amount') AS DOUBLE)-- END) AS amount-- FROM vertex_arbitrum.Clearinghouse_call_withdrawCollateral-- WHERE call_success = true-- GROUP BY 1-- ) GROUP BY 1WITH token_prices AS(SELECT minute, symbol, priceFROM prices.usd_latestWHERE blockchain = 'arbitrum'AND symbol IN ('USDC.e', 'WBTC', 'WETH', 'ARB', 'USDT')UNION ALL(SELECT block_time AS minute, 'VRTX' AS symbol, CAST(BYTEARRAY_TO_INT256(BYTEARRAY_SUBSTRING(data, 1, 32)) AS DOUBLE)/1e18 AS priceX18FROM arbitrum.logs WHERE topic0 = 0x7c57459d6f4f0fb2fc5b1e298c8c0eb238422944964aa1e249eaa78747f0cca9 -- OffchainExchangeAND BYTEARRAY_TO_UINT256(topic1) = 41 ORDER BY block_time DESC LIMIT 1)),daily_token_inflows AS(SELECT "date",token,SUM(amount) AS amountFROM (SELECT date_trunc('day', evt_block_time) AS "date",CASE WHEN productId = 0 THEN 'USDC.e'WHEN productId = 1 THEN 'WBTC'WHEN productId = 3 THEN 'WETH'WHEN productId = 5 THEN 'ARB'WHEN productId = 31 THEN 'USDT'WHEN productId = 41 THEN 'VRTX'ELSE CAST(productId AS VARCHAR)END AS token,amount/1e18 AS amount