Query editor
WITH raw_data AS (SELECTTRY_CAST(json_extract_scalar(item, '$.timestamp') AS date) as date,json_extract_scalar(item, '$.protocol') as protocol,TRY_CAST(json_extract_scalar(item, '$.volume') AS double) AS volumeFROM UNNEST(CAST(TRY(json_parse(HTTP_GET('https://api.woofi.com/defillama_dexs?filter_days=40'))) AS ARRAY(JSON))) AS t(item)), parsed_data AS (SELECTdate,CASEWHEN protocol = 'pump.fun' THEN 'Pump'WHEN protocol = 'PumpSwap' THEN 'Pump'ELSE split_part(protocol, ' ', 1)END AS protocol,SUM(volume) AS volumeFROM raw_dataGROUP BY1, 2), protocol_ranks AS (SELECTdate,protocol,volume,DENSE_RANK() OVER (PARTITION BY date ORDER BY volume DESC) * -1 as volume_rankFROM parsed_data)SELECTdate,protocol,volume,volume_rankFROM protocol_ranksWHERE protocol = 'WOOFi'ORDER BY date DESC;