Query editor
WITH protocols AS (SELECTJSON_QUERY(HTTP_GET('https://api.llama.fi/overview/fees/base?excludeTotalDataChart=true&excludeTotalDataChartBreakdown=true&dataType=dailyRevenue'),'lax $.protocols') AS revenues), protocols_json AS (SELECTTRY_CAST(JSON_PARSE(revenues) AS ARRAY(JSON)) AS dataFROM protocols), magic_json AS (SELECTelementFROM protocols_jsonCROSS JOIN UNNEST(data) AS t(element)), raw_data AS (SELECTJSON_EXTRACT_SCALAR(element, '$.displayName') AS name,CAST(JSON_EXTRACT_SCALAR(element, '$.total24h') AS BIGINT) AS total24h,CAST(JSON_EXTRACT_SCALAR(element, '$.total7d') AS BIGINT) AS total7d,CAST(JSON_EXTRACT_SCALAR(element, '$.total30d') AS BIGINT) AS total30d,CAST(JSON_EXTRACT_SCALAR(element, '$.totalAllTime') AS BIGINT) AS totalAllTimeFROM magic_json), formatted_data AS (SELECTname,CASEWHEN total24h >= 1000000 THEN FORMAT('$%.2fm', total24h / 1000000.0)ELSE FORMAT('$%.2fk', total24h / 1000.0)END AS "Fees(24h)",CASEWHEN total7d >= 1000000 THEN FORMAT('$%.2fm', total7d / 1000000.0)ELSE FORMAT('$%.2fk', total7d / 1000.0)END AS "Fees(7d)",CASEWHEN total30d >= 1000000 THEN FORMAT('$%.2fm', total30d / 1000000.0)ELSE FORMAT('$%.2fk', total30d / 1000.0)END AS "Fees(30d)",CASEWHEN totalAllTime >= 1000000 THEN FORMAT('$%.2fm', totalAllTime / 1000000.0)ELSE FORMAT('$%.2fk', totalAllTime / 1000.0)END AS "Fees(all time)",total30dFROM raw_data)SELECTROW_NUMBER() OVER (ORDER BY total30d DESC) AS Rank,name AS Name,"Fees(24h)","Fees(7d)","Fees(30d)","Fees(all time)"FROM formatted_dataORDER BY total30d DESCLIMIT 25