Query editor
WITH json_data AS (SELECT json_parse(http_get('https://api.llama.fi/overview/derivatives')) AS parsed_data),total_data_chart_breakdown 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]') AS product_volumes_jsonFROM total_data_chart_breakdownCROSS JOIN UNNEST(CAST(json_extract(breakdown_array, '$') AS array(json))) AS t(item)),expanded_data AS (SELECTdate_trunc('week', day) AS week_start_date,CASEWHEN key IN ('dYdX V3', 'dYdX V4') THEN 'dYdX (V3+V4)'ELSE keyEND AS product_name,CAST(value AS double) AS volumeFROM unnested_dataCROSS JOIN UNNEST(CAST(product_volumes_json AS map(varchar, double))) AS t(key, value)),weekly_aggregated_data AS (SELECTweek_start_date,product_name,SUM(volume) AS volumeFROM expanded_dataGROUP BY week_start_date, product_name),top_protocols AS (SELECTproduct_name,SUM(volume) AS total_volume,DENSE_RANK() OVER (ORDER BY SUM(volume) DESC) AS overall_rankFROM weekly_aggregated_dataWHERE week_start_date >= DATE_ADD('month', -3, CURRENT_DATE)GROUP BY product_name),final_data AS (SELECTw.week_start_date AS week,t.product_name,a.volume, -- Use the actual volumet.overall_rankFROM top_protocols tCROSS JOIN (SELECT DISTINCT week_start_dateFROM weekly_aggregated_dataWHERE week_start_date >= DATE_ADD('month', -3, CURRENT_DATE)) wLEFT JOIN weekly_aggregated_data aON a.week_start_date = w.week_start_dateAND a.product_name = t.product_nameWHERE t.overall_rank <= 10)SELECT