Query editor
-- Binance Volume CalculationWITH binance_tickers AS (SELECT * FROM (VALUES('BTCUSDT'), ('ETHUSDT'), ('BNBUSDT'), ('XRPUSDT'), ('SOLUSDT'),('ADAUSDT'), ('DOGEUSDT'), ('AVAXUSDT'), ('TRXUSDT'), ('DOTUSDT')) AS t(symbol)),binance_api_responses AS (SELECTsymbol,json_parse(http_get(concat('https://api.binance.com/api/v3/klines?symbol=', symbol, '&interval=1d&limit=1000'))) AS klines_dataFROM binance_tickers),binance_data AS (SELECTDATE_TRUNC('week', from_unixtime(CAST(json_extract_scalar(kline, '$[0]') AS bigint) / 1000)) AS week_start,SUM(CAST(json_extract_scalar(kline, '$[5]') AS double) * CAST(json_extract_scalar(kline, '$[1]') AS double)) AS weekly_volumeFROM binance_api_responsesCROSS JOIN UNNEST(CAST(klines_data AS array(json))) AS t(kline)GROUP BY DATE_TRUNC('week', from_unixtime(CAST(json_extract_scalar(kline, '$[0]') AS bigint) / 1000))),-- Hyperliquid Spot Market Volume Calculationjson_data AS (SELECT json_parse(http_get('https://api.llama.fi/summary/dexs/hyperliquid-spot-orderbook?')) AS parsed_data),unnested_data AS (SELECTfrom_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint)) AS day,CAST(json_extract_scalar(item, '$[1]') AS double) AS volumeFROM json_dataCROSS JOIN UNNEST(CAST(json_extract(parsed_data, '$.totalDataChart') AS array(json))) AS t(item)),hyperliquid_spot_weekly_data AS (SELECTDATE_TRUNC('week', day) AS week_start,SUM(volume) AS weekly_volumeFROM unnested_dataGROUP BY DATE_TRUNC('week', day)),-- Combine Binance and Hyperliquid Spot Volumes (Only Common Weeks)combined_weekly_volumes AS (SELECTb.week_start AS week,b.weekly_volume AS binance_volume,h.weekly_volume AS hyperliquid_volumeFROM binance_data bJOIN hyperliquid_spot_weekly_data h ON b.week_start = h.week_start)SELECTweek,binance_volume,hyperliquid_volumeFROM combined_weekly_volumesORDER BY week;