inevitable-scarlet copy

Pine Analyticsinevitable-scarlet copy

Updated 2025-03-08

WITH liquidity_data AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS week,

pool_name,

AVG(CACAO_AMOUNT_USD + ASSET_AMOUNT_USD + SYNTH_AMOUNT_USD) AS liquidity_usd

FROM maya.defi.fact_pool_block_balances

GROUP BY 1, 2

), ranked_assets AS (

SELECT

pool_name,

liquidity_usd,

RANK() OVER (ORDER BY liquidity_usd DESC) AS rank

FROM liquidity_data

WHERE week = (SELECT MAX(DATE_TRUNC('week', block_timestamp)) FROM maya.defi.fact_pool_block_balances)

)

SELECT

CASE

WHEN rank <= 5 THEN pool_name

ELSE 'Other'

END AS asset_category,

SUM(liquidity_usd) AS total_liquidity_usd

FROM ranked_assets

GROUP BY 1

ORDER BY total_liquidity_usd DESC;

Last run: 14 days ago

263B

flipside