SELECT 'USDt' AS token_symbol, 'https://api.data-service.burrow.finance/burrow/get_token_detail/usdt.tether-token.near?period=0' AS token_endpoint

UNION ALL

SELECT 'USDC.e', 'https://api.data-service.burrow.finance/burrow/get_token_detail/a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near?period=0'

UNION ALL

SELECT 'USDT.e', 'https://api.data-service.burrow.finance/burrow/get_token_detail/dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near?period=0'

UNION ALL

SELECT 'DAI', 'https://api.data-service.burrow.finance/burrow/get_token_detail/6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near?period=0'

UNION ALL

SELECT 'USDC', 'https://api.data-service.burrow.finance/burrow/get_token_detail/17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1?period=0'

UNION ALL

SELECT 'FRAX', 'https://api.data-service.burrow.finance/burrow/get_token_detail/853d955acef822db058eb8505911ed77f175b99e.factory.bridge.near?period=0'

UNION ALL

SELECT 'USN', 'https://api.data-service.burrow.finance/burrow/get_token_detail/usn'

),

api_data AS (

SELECT

s.token_symbol,

TRUNC(TO_TIMESTAMP(value:createdAt), 'WK') AS week_start,

AVG(value:net_liquidity_apr::FLOAT) AS avg_net_liquidity_apr,

AVG(value:reserve_ratio::FLOAT * 100) AS avg_reserve_ratio,

AVG(value:volatility_ratio::FLOAT) AS avg_volatility_ratio,

AVG(value:token_supply_apr::FLOAT * 100) AS avg_token_supply_apr,

AVG(value:token_borrow_apr::FLOAT * 100) AS avg_token_borrow_apr,

AVG(value:token_borrowed_value::FLOAT / POW(10, 6)) AS avg_token_borrowed_value,

AVG(value:token_borrow_fee::FLOAT / POW(10, 6)) AS avg_token_borrow_fee,

AVG(value:token_supply_fee::FLOAT / POW(10, 6)) AS avg_token_supply_fee,

AVG(value:token_utilization_rate::FLOAT * 100) AS avg_token_utilization_rate

FROM

stablecoins AS s,

LATERAL FLATTEN(input => livequery.live.udf_api(s.token_endpoint)::VARIANT:data)

WHERE

TO_TIMESTAMP(value:createdAt) >= '2023-10-24 00:00:00.00'

GROUP BY

s.token_symbol, week_start