flipside

    Dately BLUB Pools' TVL on Avalanche DEXs Over Time
    Ali3NDately BLUB Pools' TVL on Avalanche DEXs Over Time
    Updated 1 day ago
    with top_lp as (
    select
    sum(case when amount_in_usd is null then 0 else amount_in_usd end) as total,
    sum(case when token_in='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then amount_in
    when token_out='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then amount_out else 0
    end
    ) as total_eth,
    contract_address as pool_address,
    pool_name,
    platform
    from avalanche.defi.ez_dex_swaps
    where
    1=1--date(block_timestamp)>= current_date() - 365
    and contract_address in (
    select pool_address from avalanche.defi.dim_dex_liquidity_pools
    where tokens:token0 = '0x0f669808d88b2b0b3d23214dcd2a1cc6a8b1b5cd'
    or tokens:token1 = '0x0f669808d88b2b0b3d23214dcd2a1cc6a8b1b5cd')
    --and date(creation_time)>='2023-01-01'
    and (pool_name not like ('0x%') or pool_name not like ('%-0x%'))

    group by contract_address, pool_name,platform
    order by total desc, total_eth desc
    limit 1000
    ),

    lps as (
    select
    pool_name, pool_address, tokens:token0 as token0, tokens:token1 as token1,platform
    from
    avalanche.defi.dim_dex_liquidity_pools
    where
    1=1
    and pool_address in (select pool_address from top_lp where pool_name not like ('0x%'))
    ),

    Last run: 1 day agoAuto-refreshes every 12 hours
    DATE_DAY
    TVL
    INITCAP(PLATFORM) || ' | ' || POOL_NAME
    POOL_NAME
    DAY
    AVG_PRICE
    TVL_AVAX
    1
    		2025-04-27 00:00:00.000636564.190983363Trader-Joe-V1 | BLUB-WAVAXBLUB-WAVAX2025-04-27 00:00:00.00022.18541666728692.911228473
    2
    		2025-04-27 00:00:00.0005303.453808284Pharaoh-V2 | BLUB-WAVAX 10000 200BLUB-WAVAX 10000 2002025-04-27 00:00:00.00022.185416667239.051350171
    3
    		2025-04-27 00:00:00.0005269.32481991Trader-Joe-V2 | BLUB-WAVAXBLUB-WAVAX2025-04-27 00:00:00.00022.185416667237.512997799
    4
    		2025-04-27 00:00:00.0001100.320980725Pharaoh-V2 | BLUB-avUSD 10000 200BLUB-avUSD 10000 2002025-04-27 00:00:00.00022.18541666749.596588482
    5
    		2025-04-27 00:00:00.000613.120928503Trader-Joe-V2 | BLUB-USDCBLUB-USDC2025-04-27 00:00:00.00022.18541666727.636214262
    6
    		2025-04-27 00:00:00.000100.705319915Pharaoh-V2 | BLUB-USDC 10000 200BLUB-USDC 10000 2002025-04-27 00:00:00.00022.1854166674.539257541
    7
    		2025-04-27 00:00:00.0007.559073793Pangolin | BLUB-WAVAXBLUB-WAVAX2025-04-27 00:00:00.00022.1854166670.3407226426
    8
    		2025-04-27 00:00:00.0003.350418318Trader-Joe-V1 | BLUB-MUBLUB-MU2025-04-27 00:00:00.00022.1854166670.1510189495
    9
    		2025-04-27 00:00:00.0000.3005769571Trader-Joe-V2 | BLUB-WAVAXBLUB-WAVAX2025-04-27 00:00:00.00022.1854166670.01354840261
    10
    		2025-04-27 00:00:00.0000.2598210524Pharaoh-V2 | BLUB-KET 3000 60BLUB-KET 3000 602025-04-27 00:00:00.00022.1854166670.01171134427
    11
    		2025-04-27 00:00:00.0000.2494765331Pharaoh-V2 | BLUB-LIL 10000 200BLUB-LIL 10000 2002025-04-27 00:00:00.00022.1854166670.01124506864
    12
    		2025-04-27 00:00:00.0000.07653823558Trader-Joe-V2 | BLUB-USDCBLUB-USDC2025-04-27 00:00:00.00022.1854166670.003449934555
    13
    		2025-04-27 00:00:00.0000.01418093182Trader-Joe-V1 | BLUB-AMIBLUB-AMI2025-04-27 00:00:00.00022.1854166670.000639200608
    14
    		2025-04-27 00:00:00.0001.652182577e-13Pharaoh-V2 | BLUB-WINK 100 1BLUB-WINK 100 12025-04-27 00:00:00.00022.1854166677.44715595e-15
    15
    		2025-04-27 00:00:00.000-4.490651597e-15Pharaoh_V1 | BLUB-WAVAXBLUB-WAVAX2025-04-27 00:00:00.00022.185416667-2.02414571e-16
    16
    		2025-04-27 00:00:00.000-4.492469695e-14Pharaoh-V2 | BLUB-WAVAX 3000 60BLUB-WAVAX 3000 602025-04-27 00:00:00.00022.185416667-2.024965211e-15
    17
    		2025-04-27 00:00:00.000-8.699735332e-14Uniswap-V3 | BLUB-WAVAX 10000 200 UNI-V3 LPBLUB-WAVAX 10000 200 UNI-V3 LP2025-04-27 00:00:00.00022.185416667-3.921375678e-15
    18
    		2025-04-27 00:00:00.000-8.984939389e-14Pharaoh-V2 | BLUB-WINK 250 5BLUB-WINK 250 52025-04-27 00:00:00.00022.185416667-4.049930422e-15
    19
    		2025-04-27 00:00:00.000-2.547811912e-13Pharaoh-V2 | BLUB-WINK 10000 200BLUB-WINK 10000 2002025-04-27 00:00:00.00022.185416667-1.148417427e-14
    20
    		2025-04-26 00:00:00.000641318.564198361Trader-Joe-V1 | BLUB-WAVAXBLUB-WAVAX2025-04-26 00:00:00.00022.24791666728826.005320275
    ...
    1045
    145KB
    32s