Query editor
WITH symbols AS (SELECTCAST(json_extract_scalar(s, '$.product_id') AS bigint) AS product_id,json_extract_scalar(s, '$.symbol') AS symbol,TRY_CAST(json_extract_scalar(s, '$.delisted') AS boolean) AS delistedFROM UNNEST(CAST(json_parse(http_get('https://gateway.prod.nado.xyz/v1/symbols'))AS array(json))) AS t(s)),funding_payments AS (SELECTblock_time,DATE_TRUNC('day', block_time) AS day,BYTEARRAY_TO_UINT256(BYTEARRAY_SUBSTRING(data, 1, 32)) AS product_id,CAST(BYTEARRAY_TO_INT256(BYTEARRAY_SUBSTRING(data, 1 + 32 * 2, 32)) AS double) / 1e18 AS open_interestFROM ink.logsWHERE contract_address = 0xF8599D58d1137fC56EcDd9C16ee139C8BDf96da1AND topic0 = 0x52647619f5161a81ba52d76a53fbeae1142f4cd7e3574d9a810df811f760491a),daily_oi AS (SELECTday,product_id,MAX_BY(open_interest, block_time) AS open_interest_eodFROM funding_paymentsGROUP BY 1, 2),oracle_prices AS (SELECTCAST(json_extract_scalar(p, '$.product_id') AS bigint) AS product_id,json_extract_scalar(p, '$.oracle_price_x18') AS oracle_price_x18FROM UNNEST(CAST(json_extract(json_parse(http_get('https://gateway.prod.nado.xyz/query?type=all_products')),'$.data.perp_products') AS array(json))) AS t(p))SELECTd.day,REGEXP_REPLACE(s.symbol, '-PERP$', '') AS asset,d.open_interest_eod AS open_interest,d.open_interest_eod * (TRY_CAST(op.oracle_price_x18 AS double) / 1e18) AS open_interest_usdFROM daily_oi dJOIN symbols sON s.product_id = d.product_idLEFT JOIN oracle_prices opON op.product_id = d.product_idWHERE s.symbol LIKE '%-PERP'AND COALESCE(s.delisted, false) = falseORDER BY d.day DESC, asset;