flipside

    increased-blush
    HitmonleeCryptoincreased-blush
    Updated 2025-02-01
    WITH agg AS (
    SELECT
    liquidity_pool_address,
    mint,
    SUM(CASE WHEN action = 'addLiquidityByStrategy' THEN amount ELSE 0 END) AS total_added,
    SUM(CASE WHEN action = 'removeLiquidityByRange' THEN amount ELSE 0 END) AS total_removed,
    SUM(CASE WHEN action = 'addLiquidityByStrategy' THEN amount ELSE 0 END)
    - SUM(CASE WHEN action = 'removeLiquidityByRange' THEN amount ELSE 0 END) AS tvl
    FROM solana.defi.fact_liquidity_pool_actions
    WHERE -- liquidity_pool_address = 'Gm9RvdvdkPHA36UqfgeBQdS3XFJgfmkhbuZLJpSrg7GN'
    program_id IN (
    '9W959DqEETiGZocYWCQPaJ6sBmUzgfxXfqGeTEdp3aQP', -- Orca v2 Program ID
    'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc', -- Orca Whirlpool Program ID
    '675kPX9MHTjS2zt1qfr1NYHuzeLXfQM9H24wFSUt1Mp8', -- Raydium V4 Program ID
    'CAMMCzo5YL8w4VFF8KVHrK22GGUsp5VTaW7grrKgrWqK', -- Raydium CLMM Program ID
    'CPMMoo8L3F4NbTegBCKVNunggL7H1ZpdTHKxQB5qKP1C', -- Raydium CPMM Program ID
    'LBUZKhRxPF3XUpBCjp4YzTKgLccjZhTSDM9YuVaPwxo', -- Meteora DLMM Program
    'Eo7WjKq67rjJQSZxS6z3YkapzY3eMj6Xy8X5EQVn5UaB', -- Dynamic AMM Pools Program
    'FEESngU3neckdwib9X3KWqdL7Mjmqk9XNp3uh5JbP4KP', -- M3M3 Stake-for-Fee Program
    '24Uqj9JCLxUeoC3hGfh5W3s9FM9uCHDS2SG3LYwBpyTi', -- Vault Program
    'FarmuwXPWXvefWUeqFAa5w6rifLkq5X6E8bimYvrhCB1', -- Farm Program
    'vaU6kP7iNEGkbmPkLmZfGwiGxd4Mob24QQCie5R9kd2' -- Meteora DLMM Vault Program
    )
    GROUP BY liquidity_pool_address, mint
    ),
    latest_prices AS (
    SELECT
    token_address,
    symbol,
    name,
    price,
    ROW_NUMBER() OVER (PARTITION BY token_address ORDER BY hour DESC) AS rn
    FROM solana.price.ez_prices_hourly
    ),
    agg_with_price AS (
    SELECT
    Last run: 2 months ago
    LIQUIDITY_POOL_ADDRESS
    TOKEN_PAIR
    TOTAL_TVL
    TOTAL_TVL_USD
    1
    		28PzKdQkhhXtCW3umyom8C9exug1AVM6UPu9fodPzSRoBOBAOPPA BSOL-3263869021.35142-128970232.604824
    2
    		ARwi1S4DaiTG5DX7S4M4ZsrXqpMD1MrTmbu9ue2tpmEqUSDC USDT-79344499.335393-79347806.4822684
    3
    		8iibPkw7zbNHMPcihHeBgcL7rDnHteZb2NfvTy8bNm7dINF SOL-243034.908242651-47424178.3922057
    4
    		5BKxfWMbmYBAEWvyPZS9esPducUba9GqyMjtLCfbaqyFSOL USDC-19885263.2505679-46858813.058023
    5
    		CR2kQQcALb9FpA5y5KA7oa3Kf1kZ63Tz6uSQyG2yzPc6SOL USDC-18838781.8346444-45837465.3392125
    6
    		5cuy7pMhTPhVZN9xuhgSbykRb986siGJb6vnEtkuBrSUJLP USDC-44368863.855661-40921852.7778091
    7
    		DbTk2SNKWxu9TJbPzmK9HcQCAmraBCFb5VMo8Svwh34zJLP USDC-25582084.574106-40462537.3585863
    8
    		D2ze4v7YYmBPfrDzA3XrFm4rvsmtDqoVJ4twdHS7QB98USDC USDT-35035391.767831-35036559.5353778
    9
    		BVRbyLjjfSBcoyiYFuxbgKYnWuiFaF9CSXEa5vdSZ9HhSOL USDC-58599337.4905604-29179272.3452438
    10
    		BCJtr7zCmbYqEovXcGZvyJRQcw8RfQFSWCaG3PxrMypHDRIFT USDC-23753306.402795-21392333.1417133
    11
    		HTvjzsfX3yU6BUodCjZ5vZkUrAxMDTrBs3CJaq43ashRSOL USDC-11918676.4596995-17390151.8485383
    12
    		7LqWsmXDVyPd5xn4RZD61NRufcegy1ZN7PdM1c9TdaNfIO USDC-9882844.11361723-17286640.9595097
    13
    		3v94LDobJYJGu4knmPKpWvB9pmtqwJwN9GtSr4UULtPVCLOUD USDC-55631919.6918036-15641522.1679402
    14
    		HQ46iujL1fCriHva2YWbJrPu7WJHgtvaeV737F6fB1teSOL46493584.0867505-13784581.421939
    15
    		1jw5fDodwGEGBVqNXsx2eqiLgNmgMDEeXWSbrTreLCMSOL USDC-15996477.1970423-13423772.1946449
    16
    		DUB4Wd6ZA2f6ePwDghiPpfWf7Z2eQtqnuaPpPdD6Ebw1MELANIA SOL2935244.02414263-12417991.924
    17
    		EqNuKuQFAWKEsHF7sHKjL7zf4n9U7DQmZUrB2FK5GP5CMOODENG SOL52583302.8760294-11665261.2060424
    18
    		95EzJXxAyUUQAbjzDm1rgW8PHSqz1DwgZ9bsMncPSfGHJLP USDC-9106196.24633701-11462002.5978445
    19
    		5nRheYVXMTHEJXyAYG9KsUsXDTzvj9Las8M6NfNojaRJLP USDC-8338264.32078201-10774406.3915088
    20
    		bNcdL9Hy85c9qb4hRavAUFtJUiyRPh3u96jerFqZQq6JUPSOL SOL-49137.760069677-10765290.1106493
    20
    2KB
    17s