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),labeled_data AS (SELECTweek_start_date,CASEWHEN volume >= 100000000 THEN product_nameELSE 'Other'END AS product_name,SUM(volume) AS volumeFROM aggregated_dataGROUP BY week_start_date,CASEWHEN volume >= 100000000 THEN product_nameELSE 'Other'END),filtered_data AS (SELECTweek_start_date AS week,product_name,volumeFROM labeled_dataWHERE product_name != 'Other')SELECTweek,product_name,volume