Query editor
WITH volume_json_data AS (SELECT json_parse(http_get('https://api.llama.fi/overview/derivatives')) AS parsed_data),volume_data_chart AS (SELECT json_extract(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM volume_json_data),volume_unnested_data AS (SELECTfrom_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint)) AS day,json_extract(item, '$[1]') AS product_volumes_jsonFROM volume_data_chartCROSS JOIN UNNEST(CAST(json_extract(breakdown_array, '$') AS array(json))) AS t(item)),volume_daily_data AS (SELECTday,CAST(json_extract_scalar(product_volumes_json, '$["Hyperliquid Perps"]') AS double) AS hyperliquid_volumeFROM volume_unnested_data),-- Now get the fees datafees_json_data AS (SELECT json_parse(http_get('https://api.llama.fi/summary/fees/Hyperliquid')) AS parsed_data),fees_data_chart AS (SELECT json_extract(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM fees_json_data),fees_unnested_data AS (SELECTfrom_unixtime(CAST(json_extract_scalar(item, '$[0]') AS bigint)) AS day,json_extract(item, '$[1]["Hyperliquid L1"]["Hyperliquid Spot Orderbook"]') AS daily_feesFROM fees_data_chartCROSS JOIN UNNEST(CAST(json_extract(breakdown_array, '$') AS array(json))) AS t(item)),daily_fees_data AS (SELECTday,CAST(daily_fees AS double) AS feesFROM fees_unnested_data),-- Combine fees and volume datacombined_data AS (SELECTCOALESCE(v.day, f.day) AS day,f.fees,v.hyperliquid_volume,-- Calculate fees/volume in basis points (1 bp = 0.01%)CASEWHEN v.hyperliquid_volume > 0 AND f.fees IS NOT NULLTHEN (f.fees / v.hyperliquid_volume) * 10000ELSE NULLEND AS fees_to_volume_bpsFROM volume_daily_data vFULL OUTER JOIN daily_fees_data f ON v.day = f.day),-- Add 7-day moving average calculationmoving_avg_data AS (SELECTday,