WITH keyrock_fee as (

WITH raw_data AS (

SELECT

livequery.live.udf_api(

'https://dlmm-api.meteora.ag/wallet/GcsbRotLHuHGc3tzE7XBVQ2sNgAMJqbkeGuzjTAwxPfQ/3msVd34R5KxonDzyNSV5nT19UtUeJ2RF1NaQhvVPNLxL/earning'

) AS resp

)

SELECT

PARSE_JSON(resp) :data:total_fee_usd_claimed as keyrock_fees

FROM

raw_data

),

total_fee as (

WITH raw_data AS (

SELECT

livequery.live.udf_api(

'https://dlmm-api.meteora.ag/pair/3msVd34R5KxonDzyNSV5nT19UtUeJ2RF1NaQhvVPNLxL/analytic/pair_trade_volume?num_of_days=20'

) AS resp

)

SELECT

sum(flattened.value:fee_volume :: DECIMAL(18, 4)) AS fee_volume

FROM

raw_data,

LATERAL FLATTEN(input => PARSE_JSON(RESP) :data) AS flattened

WHERE flattened.value:day_date > '2024-02-28'

),

ms_ratio as (

SELECT *, keyrock_fees / fee_volume as keyrock_ratio

from total_fee

cross join keyrock_fee

),

daily_vol as (

WITH raw_data AS (