with pools_details as ( select value[0]::string::date as date, value[1]::string as usdt_tvl, value[2]::string as usdc_tvl, value[3]::string as frax_tvl, value[4]::string as usdt_brr, value[5]::string as usdc_brr, value[6]::string as frax_brr, value[7]::string as usdt_sup_cap, value[8]::string as usdc_sup_cap, value[9]::string as frax_sup_cap, rank() over (order by date desc) as rank from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/75dd15af-e995-4752-9ffc-a8ee48c08e2c/latest-run') as response), lateral FLATTEN (input => response:data:result:rows)), depositors as ( select value[0]::string as pool, value[1]::string as "Suppliers" from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/1dca5fab-dc6b-4517-9414-ba1fdd5ad699/latest-run') as response), lateral FLATTEN (input => response:data:result:rows)), burrowers as ( select value[0]::string as pool, value[1]::string as "Burrowers" from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/1567a27b-0b6c-4fa1-bb2d-fefc56a527b4/latest-run') as response), lateral FLATTEN (input => response:data:result:rows)) select 'USDt' as "Pool", round(a.USDT_SUP_CAP,3) as "Supply Cap", case when round((a.USDT_SUP_CAP-b.USDT_SUP_CAP)/b.USDT_SUP_CAP,2)>0 then '🟩 '||round((a.USDT_SUP_CAP-b.USDT_SUP_CAP)/b.USDT_SUP_CAP,2)||'%' when round((a.USDT_SUP_CAP-b.USDT_SUP_CAP)/b.USDT_SUP_CAP,2)<0 then '🟥 '||round((a.USDT_SUP_CAP-b.USDT_SUP_CAP)/b.USDT_SUP_CAP,2)||'%' else ''||round((a.USDT_SUP_CAP-b.USDT_SUP_CAP)/b.USDT_SUP_CAP,2)||'%' end as "7D %📉", case when round((a.USDT_SUP_CAP-c.USDT_SUP_CAP)/c.USDT_SUP_CAP,2)>0 then '🟩 '||round((a.USDT_SUP_CAP-c.USDT_SUP_CAP)/c.USDT_SUP_CAP,2)||'%' when round((a.USDT_SUP_CAP-c.USDT_SUP_CAP)/c.USDT_SUP_CAP,2)<0 then '🟥 '||round((a.USDT_SUP_CAP-c.USDT_SUP_CAP)/c.USDT_SUP_CAP,2)||'%' else ''||round((a.USDT_SUP_CAP-c.USDT_SUP_CAP)/c.USDT_SUP_CAP,2)||'%' end as "30D %📉", round(a.USDT_TVL,3) as "Available Liquidity", round(a.USDT_BRR,3) as "Borrow Cap", case when round((a.USDT_BRR-b.USDT_BRR)/b.USDT_BRR,2)>0 then '🟩 '||round((a.USDT_BRR-b.USDT_BRR)/b.USDT_BRR,2)||'%' when round((a.USDT_BRR-b.USDT_BRR)/b.USDT_BRR,2)<0 then '🟥 '||round((a.USDT_BRR-b.USDT_BRR)/b.USDT_BRR,2)||'%' else ''||round((a.USDT_BRR-b.USDT_BRR)/b.USDT_BRR,2)||'%' end as "7D %📈", case when round((a.USDT_BRR-c.USDT_BRR)/c.USDT_BRR,2)>0 then '🟩 '||round((a.USDT_BRR-c.USDT_BRR)/c.USDT_BRR,2)||'%' when round((a.USDT_BRR-c.USDT_BRR)/c.USDT_BRR,2)<0 then '🟥 '||round((a.USDT_BRR-c.USDT_BRR)/c.USDT_BRR,2)||'%' else ''||round((a.USDT_BRR-c.USDT_BRR)/c.USDT_BRR,2)||'%' end as "30D %📈", "Suppliers", "Burrowers" from pools_details a join pools_details b on b.rank=8 join pools_details c on c.rank=31 join depositors x on x.pool='USDt' join burrowers y on y.pool='USDt' where a.rank=1 union all select 'USDC' as "Pool", round(a.USDC_SUP_CAP,3) as "Supply Cap", case when round((a.USDC_SUP_CAP-b.USDC_SUP_CAP)/b.USDC_SUP_CAP,2)>0 then '🟩 '||round((a.USDC_SUP_CAP-b.USDC_SUP_CAP)/b.USDC_SUP_CAP,2)||'%' when round((a.USDC_SUP_CAP-b.USDC_SUP_CAP)/b.USDC_SUP_CAP,2)<0 then '🟥 '||round((a.USDC_SUP_CAP-b.USDC_SUP_CAP)/b.USDC_SUP_CAP,2)||'%' else ''||round((a.USDC_SUP_CAP-b.USDC_SUP_CAP)/b.USDC_SUP_CAP,2)||'%' end as "7D %📉", case when round((a.USDC_SUP_CAP-c.USDC_SUP_CAP)/c.USDC_SUP_CAP,2)>0 then '🟩 '||round((a.USDC_SUP_CAP-c.USDC_SUP_CAP)/c.USDC_SUP_CAP,2)||'%' when round((a.USDC_SUP_CAP-c.USDC_SUP_CAP)/c.USDC_SUP_CAP,2)<0 then '🟥 '||round((a.USDC_SUP_CAP-c.USDC_SUP_CAP)/c.USDC_SUP_CAP,2)||'%' else ''||round((a.USDC_SUP_CAP-c.USDC_SUP_CAP)/c.USDC_SUP_CAP,2)||'%' end as "30D %📉", round(a.USDC_TVL,3) as "Available Liquidity", round(a.USDC_BRR,3) as "Borrow Cap", case when round((a.USDC_BRR-b.USDC_BRR)/b.USDC_BRR,2)>0 then '🟩 '||round((a.USDC_BRR-b.USDC_BRR)/b.USDC_BRR,2)||'%' when round((a.USDC_BRR-b.USDC_BRR)/b.USDC_BRR,2)<0 then '🟥 '||round((a.USDC_BRR-b.USDC_BRR)/b.USDC_BRR,2)||'%' else ''||round((a.USDC_BRR-b.USDC_BRR)/b.USDC_BRR,2)||'%' end as "7D %📈", case when round((a.USDC_BRR-c.USDC_BRR)/c.USDC_BRR,2)>0 then '🟩 '||round((a.USDC_BRR-c.USDC_BRR)/c.USDC_BRR,2)||'%' when round((a.USDC_BRR-c.USDC_BRR)/c.USDC_BRR,2)<0 then '🟥 '||round((a.USDC_BRR-c.USDC_BRR)/c.USDC_BRR,2)||'%' else ''||round((a.USDC_BRR-c.USDC_BRR)/c.USDC_BRR,2)||'%' end as "30D %📈", "Suppliers", "Burrowers" from pools_details a join pools_details b on b.rank=8 join pools_details c on c.rank=31 join depositors x on x.pool='USDC' join burrowers y on y.pool='USDC' where a.rank=1 union all select 'FRAX' as "Pool", round(a.FRAX_SUP_CAP,3) as "Supply Cap", case when round((a.FRAX_SUP_CAP-b.FRAX_SUP_CAP)/b.FRAX_SUP_CAP,2)>0 then '🟩 '||round((a.FRAX_SUP_CAP-b.FRAX_SUP_CAP)/b.FRAX_SUP_CAP,2)||'%' when round((a.FRAX_SUP_CAP-b.FRAX_SUP_CAP)/b.FRAX_SUP_CAP,2)<0 then '🟥 '||round((a.FRAX_SUP_CAP-b.FRAX_SUP_CAP)/b.FRAX_SUP_CAP,2)||'%' else ''||round((a.FRAX_SUP_CAP-b.FRAX_SUP_CAP)/b.FRAX_SUP_CAP,2)||'%' end as "7D %📉", case when round((a.FRAX_SUP_CAP-c.FRAX_SUP_CAP)/c.FRAX_SUP_CAP,2)>0 then '🟩 '||round((a.FRAX_SUP_CAP-c.FRAX_SUP_CAP)/c.FRAX_SUP_CAP,2)||'%' when round((a.FRAX_SUP_CAP-c.FRAX_SUP_CAP)/c.FRAX_SUP_CAP,2)<0 then '🟥 '||round((a.FRAX_SUP_CAP-c.FRAX_SUP_CAP)/c.FRAX_SUP_CAP,2)||'%' else ''||round((a.FRAX_SUP_CAP-c.FRAX_SUP_CAP)/c.FRAX_SUP_CAP,2)||'%' end as "30D %📉", round(a.FRAX_TVL,3) as "Available Liquidity", round(a.FRAX_BRR,3) as "Borrow Cap", '0' as "7D %📈", '0' as "30D %📈", "Suppliers", "Burrowers" from pools_details a left join pools_details b on b.rank=8 left join pools_details c on c.rank=31 left join depositors x on x.pool='FRAX' left join burrowers y on y.pool='FRAX' where a.rank=1

Near Pools Analysis - Ref & Burrow