Query editor
WITH json_data AS (SELECT json_parse(http_get('https://api.llama.fi/summary/derivatives/hyperliquid')) AS parsed_data),total_data_chart_breakdown AS (SELECT json_extract(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM json_data),hyperliquid_data AS (SELECTfrom_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint)) AS day,CASEWHEN DATE(from_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint))) = CURRENT_DATETHEN CAST(json_extract_scalar(json_extract(item, '$[1]'), '$["Hyperliquid L1"]["Hyperliquid Perps"]') AS double) * (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) / 24.0)ELSE CAST(json_extract_scalar(json_extract(item, '$[1]'), '$["Hyperliquid L1"]["Hyperliquid Perps"]') AS double)END AS hyperliquid_volumeFROM total_data_chart_breakdownCROSS JOIN UNNEST(CAST(json_extract(breakdown_array, '$') AS array(json))) AS t(item)WHERE EXTRACT(YEAR FROM from_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint))) >= 2023),binance_tickers AS (SELECT * FROM (VALUES('ETHUSDT'), ('DOGEUSDT'), ('SOLUSDT'), ('1000PEPEUSDT'), ('BTCUSDC'),('1000SHIBUSDT'), ('XRPUSDT'), ('NEIROUSDT'), ('SUIUSDT'), ('BNBUSDT'),('WIFUSDT'), ('ACTUSDT'), ('WLDUSDT'), ('ADAUSDT'), ('APTUSDT'),('ETHUSDC'), ('1000BONKUSDT'), ('AVAXUSDT'), ('DOGEUSDC'), ('TRUMPUSDT'),('MOODENGUSDT'), ('ORDIUSDT'), ('1000FLOKIUSDT'), ('NEARUSDT'), ('GOATUSDT'),('BTCUSDT'), ('PNUTUSDT'), ('UNIUSDT'), ('POPCATUSDT'), ('BCHUSDT')) AS t(symbol)),binance_api_responses AS (SELECTsymbol,json_parse(http_get(concat('https://fapi.binance.com/fapi/v1/klines?symbol=', symbol, '&interval=1d&limit=1000'))) AS klines_dataFROM binance_tickers),binance_data AS (SELECTfrom_unixtime(CAST(json_extract_scalar(kline, '$[0]') AS bigint) / 1000) AS day,SUM(CAST(json_extract_scalar(kline, '$[5]') AS double) * CAST(json_extract_scalar(kline, '$[1]') AS double)) AS binance_volumeFROM binance_api_responsesCROSS JOIN UNNEST(CAST(klines_data AS array(json))) AS t(kline)WHERE EXTRACT(YEAR FROM from_unixtime(CAST(json_extract_scalar(kline, '$[0]') AS bigint) / 1000)) >= 2023GROUP BY from_unixtime(CAST(json_extract_scalar(kline, '$[0]') AS bigint) / 1000)),combined_daily_data AS (SELECTCOALESCE(h.day, b.day) AS day,COALESCE(h.hyperliquid_volume, 0) AS hyperliquid_volume,COALESCE(b.binance_volume, 0)*1.3 AS binance_volumeFROM hyperliquid_data hFULL OUTER JOIN binance_data b ON DATE(h.day) = DATE(b.day)),weekly_aggregated_data AS (SELECTDATE_TRUNC('week', day) AS week,SUM(hyperliquid_volume) AS weekly_hyperliquid_volume,SUM(binance_volume) AS weekly_binance_volume,SUM(hyperliquid_volume) / NULLIF(SUM(binance_volume), 0) AS market_shareFROM combined_daily_dataGROUP BY DATE_TRUNC('week', day)