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=90'))) AS ARRAY(JSON))) AS t(item)), parsed_data AS (SELECTdate,split_part(protocol, ' ', 1) AS protocol,SUM(volume) AS volumeFROM raw_dataWHEREsplit_part(protocol, ' ', 1) NOT IN ('Raydium', 'Orca', 'Meteora', 'PumpSwap', 'pump.fun', 'Lifinity', 'stabble', 'Hashflow', 'Phoenix', 'HumidiFi')GROUP BYdate,split_part(protocol, ' ', 1)), protocol_ranks AS (SELECTdate,protocol,volume,DENSE_RANK() OVER (PARTITION BY date ORDER BY volume DESC) * -1 as volume_rankFROM parsed_data)SELECTvolume_rank * -1FROM protocol_ranksWHERE protocol = 'WOOFi'ORDER BY date DESClimit 1;