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)),aggregated_data AS (SELECTweek_start_date,product_name,SUM(volume) AS volumeFROM expanded_dataGROUP BY week_start_date, product_name),ranked_week AS (SELECTproduct_name,ROW_NUMBER() OVER (ORDER BY volume DESC) AS rankFROM aggregated_dataWHERE week_start_date = date_trunc('week', CURRENT_DATE)),final_data AS (SELECTa.week_start_date,CASEWHEN r.rank IS NOT NULL AND r.rank <= 10 THEN a.product_nameELSE 'Other'END AS product_name,SUM(a.volume) AS volumeFROM aggregated_data aLEFT JOIN ranked_week rON a.product_name = r.product_nameGROUP BY a.week_start_date,CASEWHEN r.rank IS NOT NULL AND r.rank <= 10 THEN a.product_nameELSE 'Other'END)SELECTweek_start_date AS week,product_name,