Query editor
WITH json_data AS (SELECT JSON_PARSE(HTTP_GET('https://api.llama.fi/overview/derivatives/base')) AS parsed_data),total_data_chart_breakdown AS (SELECTJSON_EXTRACT(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM json_data),unnested_data AS (SELECTFROM_UNIXTIME(TRY_CAST(JSON_EXTRACT_SCALAR(item, '$[0]') AS BIGINT)) AS day,JSON_EXTRACT(item, '$[1]') AS product_volumes_jsonFROM total_data_chart_breakdownCROSS JOIN UNNEST(TRY_CAST(JSON_EXTRACT(breakdown_array, '$') AS ARRAY(JSON))) AS t(item)),expanded_data AS (SELECTDATE_TRUNC('week', day) AS week_start_date,CASE WHEN key = 'SynFutures V3' THEN 'SynFutures V3' ELSE key END AS product_name,TRY_CAST(value AS DOUBLE) AS volumeFROM unnested_dataCROSS JOIN UNNEST(TRY_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),other_perp_dexes AS (SELECTweek_start_date,SUM(volume) AS total_other_volume,COUNT(DISTINCT product_name) AS num_protocolsFROM weekly_aggregated_dataWHERE product_name != 'SynFutures V3'GROUP BY week_start_date),synfutures_data AS (SELECTweek_start_date,volume AS synfutures_volumeFROM weekly_aggregated_dataWHERE product_name = 'SynFutures V3'),final_data AS (SELECTs.week_start_date AS week,'SynFutures V3' AS product_name,COALESCE(s.synfutures_volume, 0) AS volume,CASEWHEN COALESCE(s.synfutures_volume, 0) + COALESCE(o.total_other_volume, 0) > 0THEN COALESCE(s.synfutures_volume, 0) / (COALESCE(s.synfutures_volume, 0) + COALESCE(o.total_other_volume, 0)) * 100ELSE NULLEND AS dominance_percentage,COALESCE(o.num_protocols, 0) AS num_other_protocolsFROM synfutures_data sFULL JOIN other_perp_dexes o ON s.week_start_date = o.week_start_date