Query editor
WITH 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 json_data),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)),final_data AS (SELECTday,CAST(daily_fees AS double) AS fees,LAG(CAST(daily_fees AS double)) OVER (ORDER BY day) AS prev_day_fees,AVG(CAST(daily_fees AS double)) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS seven_day_maFROM unnested_data)SELECTday,fees as revenue,ROUND(seven_day_ma, 2)*365 as annualized_revenueFROM final_dataWHERE day >= date '2023-01-01'ORDER BY day DESC;